Excel Essentials for Auditors: Part 1 Practical Tips from the Ground Up

Starting your career as an auditor can be daunting, with exams, lots of audit and accounting standards which keep evolving and the realisation that accountants really do use Excel for everything!

Given this reliance on Excel, there hasn’t always been focused Excel training available incorporating the latest functionality within Excel. 

This short series of articles runs through core functionality which should help you in your audit role. The skills can obviously be applied elsewhere but the example exercise, which you can download here, is very much audit focused.

In this series we are starting with navigation and basic formula tips, we move into formulae which make working with text easier then finish on our stock audit test (cost v net realization value) which brings together pivot tables and lookups. For some roles these would be considered advanced functionality, but I really see these as essential skills for any auditor as this is a pretty common audit test and similar techniques can be applied to many other areas of an audit.

While the video is the primary format, you can follow along with the article below for a summary of the video.

1. Navigating Large Workbooks: New Windows & Freeze Panes

New Windows

When working with big spreadsheets, it would often be helpful to view different parts of the same worksheet, or even different tabs, at the same time. Instead of endlessly scrolling, use the New Window feature (found under the View ribbon). This opens another view of your current workbook, letting you compare data side by side. Each window can show a different part of your data, making cross-referencing much easier.

Tip: If you use Windows + arrow keys, you can quickly snap these windows to different sides of your screen for better visibility.

Freeze Panes

Freeze Panes is another handy tool. It locks rows and/or columns, so your headers stay visible as you scroll. Just select the cell below and to the right of the rows/columns you want to freeze, then click Freeze Panes (in View ribbon).

Health Warning: If you use multiple windows on a file with Freeze Panes, make sure you don’t close the windows with freeze panes displaying otherwise you will accidentally remove the frozen panes from your file. This will cause complaints from colleagues.

2. Mastering Formula References: The Dollar Sign ($) and F4

When copying formulas across rows or columns, you’ll often want to “lock” a reference, so it doesn’t change. This is where the dollar sign ($) comes in:

• $A$1 locks both the column and row.
• A$1 locks just the row.
• $A1 locks just the column.

Shortcut: Press F4 after selecting a cell reference in your formula to quickly toggle through these options. This saves time and reduces errors when building complex formulas.

3. Dynamic Arrays: Modern Excel Magic

If you’re using a recent version of Excel, you can take advantage of dynamic array formulae. These let you perform calculations on a whole range at once, and the results “spill” into adjacent cells automatically. This is demonstrated in the video with both a simple multiplication and the ROUND function. If there’s something in the way, you’ll see a “#SPILL!” error, just clear the obstructing cell and your formula will work. Note you can’t spill within an Excel Table.

4. Rounding Numbers: Why It Matters

Excel lets you display numbers to a certain number of decimal places, but the underlying value might have more digits (accuracy). This can cause problems when totals don’t match what’s shown on paper or in a PDF.

Example: If you sum rounded numbers, the total might differ from the sum of the unrounded values, sometimes by just a few pence, but this can become significant in reports quoting in thousands or millions

Tip: Use the ROUND function to ensure your numbers are genuinely rounded, not just displayed that way. Always be conscious of whether you’re rounding for display or for calculation.

5. Summing Data: SUM, SUBTOTAL, and AGGREGATE

SUM

The basic SUM function adds up all numbers in the range whether rows/columns are hidden/filtered or not. 

SUBTOTAL

SUBTOTAL is smarter—it ignores other subtotals in your range, helping you avoid double-counting and build more robust workpapers/layouts. It can be used for a number of functions as defined in the first parameter of the formula.

AGGREGATE

AGGREGATE is even more powerful. It can ignore hidden rows, errors, and other subtotals and gives you clear control over what gets included in your totals. AGGREGATE offers more options and clearer function selection than SUBTOTAL. If you’re unsure which to use, AGGREGATE is often the safer bet.

6. Conditional Sums: SUMIF and SUMIFS

When you need to sum values based on criteria (e.g., total sales for “Part 1”), use SUMIF. For more complex conditions (e.g., sum sales for “Part 1” in a specific region), use SUMIFS.

• SUMIF(range, criteria, sum_range)
• SUMIFS(sum_range, criteria_range1, criteria1, ...)

I personally always use SUMIFS due to its increased flexibility and I find the parameter order matches the function name, so it is easier to remember what to do i.e. SUM [this] IF [criteria range] is [criteria]. Remember to use F4 to lock ranges as you build your formulas, making it easy to copy them for different criteria.

Conclusion

These Excel features, New Windows, Freeze Panes, formula referencing, dynamic arrays, rounding, AGGREGATE and SUMIFS, are foundational for any trainee accountant. Mastering them will not only make your work more efficient but also help you avoid common pitfalls in audit and financial analysis.