Excel for Auditors: Testing, Sampling and Reconciling

Excel for auditors comes down to a few jobs: testing a whole population for exceptions, matching records between two systems, reconciling totals, selecting samples and documenting the work so someone else can check it. The functions behind those jobs are COUNTIFS, SUMIFS, XLOOKUP and conditional formatting. This guide shows each one on a small invoice list, for internal and external audit candidates preparing for a job or an Excel test.

What auditors use Excel for#

Auditors use Excel to check that records are complete, accurate and valid, and to show the evidence. A typical file holds an export from the client's system, a few columns of tests and a summary of exceptions. The skills below cover most of that work:

All the examples use this invoice export in A1:D9. The company rule is that any invoice over 5,000 must be approved:

Exception testing with COUNTIFS and SUMIFS#

With Excel you can test 100% of a population, not just a sample. COUNTIFS counts the rows that break the rule, and SUMIFS gives their value:

=COUNTIFS(C2:C9,">5000",D2:D9,"No")
=SUMIFS(C2:C9,C2:C9,">5000",D2:D9,"No")

The results are 3 exceptions worth 20,300 (5,600 + 9,100 + 5,600). Each range and criteria pair is one condition, and a row counts only if it meets them all. The COUNTIFS function guide covers the syntax, and the COUNTIFS and SUMIFS lesson practises multi-condition totals.

To see which invoices fail, add a test column. In E2, type and fill down:

=IF(AND(C2>5000,D2="No"),"Exception","")

Then filter column E for "Exception" to list INV-1003, INV-1005 and INV-1007.

Matching records with XLOOKUP#

Matching means checking that each record in one list exists in another, such as invoices against payments. XLOOKUP finds a value and returns a matching value from another column, and its fourth argument sets what to show when nothing is found. If a second sheet called Payments lists paid invoices in column A and amounts in column C:

=XLOOKUP(A2,Payments!A:A,Payments!C:C,"Not found")

"Not found" is easier to filter than #N/A, and it doesn't hide other errors the way IFERROR can. Next to it, =C2-F2 shows any difference between the invoice and the payment. XLOOKUP needs Excel 2021 or Microsoft 365; in older versions, use INDEX MATCH. The XLOOKUP function guide explains each argument.

Lookups fail silently when IDs have extra spaces or numbers are stored as text. The fixing lookup errors lesson shows how to diagnose #N/A and wrong answers.

Reconciling two totals#

A reconciliation compares two figures that should agree and explains the difference. Keep it simple and visible:

H1 is =SUM(C2:C9), H2 is the total from the other report, and H3 is =H1-H2. A difference of 1,250 matches INV-1004 exactly, and the XLOOKUP column confirms it with "Not found". Add a check cell such as =IF(H3=0,"Reconciled","Check") so a non-zero difference is obvious. For totals by vendor or account, use SUMIFS on each list and subtract.

Finding duplicate payments#

A duplicate test looks for rows where the same vendor and amount appear more than once. COUNTIFS checks both columns at once. In F2, type and fill down:

=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,C2)>1

Rows 4 and 8 return TRUE: two Apex invoices of 5,600. That is a possible duplicate, not proof; it may be two real orders. Add the invoice date as a third pair to make the test stricter. Highlight the TRUE rows with a conditional formatting rule, for example =COUNTIFS($B$2:$B$9,$B2,$C$2:$C$9,$C2)>1 applied to A2:D9. The conditional formatting lesson shows how a formula rule colours whole rows.

Selecting a sample in Excel#

When you can't test everything in detail, Excel can select the sample, but your firm's methodology decides how many items to pick. Two common ways:

• Random selection. Add a column with =RAND(), which returns a random number between 0 and 1. Sort the list by that column and take the first rows. In Microsoft 365, =TAKE(SORTBY(A2:A9,RANDARRAY(ROWS(A2:A9))),3) returns three random invoice numbers in one step.
• Interval (systematic) selection. Pick every nth item. =MOD(ROW()-2,3)=0 in row 2 returns TRUE for every third row starting with the first.

RAND and RANDARRAY recalculate every time the sheet changes, so the sample changes too. As soon as you have it, copy the sample, open Paste Special (Windows: Ctrl+Alt+V; Mac: ⌘Control+V) and choose Values. Then note the date, the population size and the method.

Documenting work so a reviewer can follow it#

Audit work is reviewed, so a correct answer is not enough: the reviewer must be able to see how you got it. A few habits help:

1. Never edit the source data. Keep the export on its own sheet and do the testing on another sheet or in new columns.
2. Use formulas, not typed numbers. A typed 43,300 can't be checked; =SUM(C2:C9) can.
3. Show the formulas when reviewing with Ctrl+` (the grave accent key, on Windows and Mac) or Formulas > Show Formulas.
4. Use Trace Precedents on the Formulas tab to show which cells feed a total.
5. Label every test with what it checks and the rule it uses, such as "Over 5,000 and not approved".

Similar habits matter in other analysis-heavy roles; see the Excel skills for business analysts, the Excel skills for supply chain jobs and the Excel skills for project managers.

How to prepare for an audit Excel test#

Audit and finance Excel tests usually mix lookups, conditional totals and a short data task under time pressure. Practise typing the formulas, not just reading them. Work through the finance Excel mock test for timed questions on totals and lookups, and the Excel case study test for a longer data task. If you are not sure where to start, the Excel level test shows which skills need work.

Practise it#

Try the exercise on this page: it counts the unapproved invoices over 5,000, the same exception test an auditor runs. Then practise COUNTIFS and SUMIFS in a live spreadsheet, where you type each formula and it checks it instantly, and move on to fixing lookup errors to match records between two lists without silent mistakes.