Excel test for accounting roles: what it covers and how to pass (or set) one

What makes an accounting Excel test different

A general Excel test checks whether you can total, count and look things up. An accounting test checks whether you can make two sets of numbers agree, and explain the difference when they don't. The functions are ordinary (SUMIFS, XLOOKUP, COUNTIF, ROUND). The skill being tested is control: nothing double-counted, nothing dropped, every gap explained.

The sheet above is a typical test file. Open invoices are in A:F, receipts from the bank are in H:I, the reporting date is in K2, an account and month to report on are in L2 and M2, and a shared cost to split is in K5. Like a real export, it has problems on purpose: one payment is short, one receipt has no invoice, and one invoice is long overdue.

Hiring managers: the general process (format, timing, rubric, fairness) is in how to test Excel skills in an interview, and a general task set is in Excel assessment questions for employers. This page is the accounting version.

Task 1: Reconcile invoices to receipts

"Which invoices haven't been paid, and is there any cash we can't match?" This is the most common accounting task, and it has two directions. Testers watch for candidates who only check one.

Task 2: Total by account and period

"What was billed to account 4000 in October?" Strong answers put the account and month in cells and use SUMIFS with a date range, so next month is a one-cell change. EOMONTH(M2,0) gives the last day of the month in M2, so the range works for 30-day and 31-day months alike.

Weak answers filter the list and add the visible numbers, or type the dates into the formula. Both give the right figure today and need rework every month. More on date ranges in SUMIFS between two dates.

Task 3: Age the open balances

"Age the receivables as at 31 October." First work out what's still open on each invoice: the amount minus anything received against it. Then compare the due date with the as-of date in K2 and put each open amount in a bucket. Using K2, not TODAY(), keeps the aging repeatable for a month-end date.

Task 4: Rounding pennies

"Split the 1,000.00 cost in K5 equally across three departments." Each share is 333.333..., and the cell shows 333.33 if it's formatted to 2 decimals. Formatting only changes what you see, not the value stored in the cell. Post the rounded figures and they add to 999.99, a penny short.

Strong answers round each share with ROUND, then make the last share the total minus the others, so the split always adds back exactly. Weak answers format to 2 decimals and assume it's fine, or type 333.34 into one cell by hand. The same issue explains why a match difference can look like 0.00 and still fail an =0 check: wrap differences in ROUND(...,2) before you compare them. More in how to round in Excel.

Task 5: Prove it ties out

Finishing with a check is what separates an accountant's sheet from a list of answers. Here there are two. The aging buckets should add up to total invoices minus the receipts applied to them. And total bank receipts should equal applied receipts plus anything unapplied.

Invoices total 9,083.45 and receipts applied to them total 3,068.40, which leaves 6,015.05 open. The buckets give 3,085.00 + 2,400.05 + 0 + 530.00 = 6,015.05, so the aging ties. Bank receipts total 3,468.40, and the 400.00 difference is exactly INV-109, the receipt from task 1 with no invoice. A candidate who writes that sentence has passed the part of the test that matters most.

For candidates and hiring managers

• Candidates: practise on a real ledger with a mismatch in it, check both directions of every reconciliation, put dates and accounts in cells, and say what you checked when you hand it in. Our Excel test preparation guide covers timing and the general skills.
• Candidates: if you use a PivotTable or helper columns instead of the formulas above, that's fine. Testers score the result and whether it holds up when the data changes.
• Hiring managers: build the test on invented data shaped like your own ledger, and plant two or three realistic problems (a short payment, unapplied cash, a duplicate). Score whether each one is found and explained, not just the final totals.
• Hiring managers: keep the tasks to what the role does. An AP clerk needs matching and totals; a senior accountant might add accruals or a variance analysis. Follow local hiring law and keep tests job-related.