Practice tests ยท By job role
Excel test for bookkeepers
Running balances, matching two lists, totals by date range, categorizing from a vendor list and ageing buckets are the skills bookkeepers are commonly asked to show.
Skills commonly tested for bookkeepers
- Running balances. A balance column where each row adds the new amount to the balance above it, kept correct when rows are added.
- Reconciling two lists. Checking whether each bank line has a match in the ledger with COUNTIF or a lookup, and totalling what does not.
- Totals by date range. SUMIFS with a start and end date to total one month or one quarter of transactions.
- Categorizing transactions. Pulling a category or account from a vendor or chart-of-accounts sheet with a lookup, and counting what is still uncategorized.
- Signs and absolute values. Debits and credits arrive as positive and negative numbers, so ABS, SUMIF with a greater-than-zero condition and consistent signs matter.
- Ageing. Bucketing overdue invoices into 0 to 30, 31 to 60 and over 60 days with a nested IF.
8 sample tasks with answers
1. Fill down a running balance, starting from the opening deposit.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Amount | Balance |
| 2 | 1/2/2026 | 500 | 500 |
| 3 | 1/5/2026 | -120 | 380 |
| 4 | 1/9/2026 | -75 |
C2 holds the opening balance of 500. For row 3, the formula adds the new amount in B3 to the balance in C2: 500 minus 120 leaves 380.
2. Mark each bank reference that does not appear in the ledger.
| A | B | C | |
|---|---|---|---|
| 1 | Ledger | Bank | Status |
| 2 | C-101 | C-103 | Not in ledger |
| 3 | C-102 | ||
| 4 | C-104 |
COUNTIF counts how many times the bank reference in B2 appears in the ledger list A2:A4. C-103 is not in the ledger, so the count is 0 and the status reads Not in ledger.
3. Total the deposits only, which are the positive amounts.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Amount | ||
| 2 | 1/2/2026 | 500 | 800 | |
| 3 | 1/5/2026 | -120 | ||
| 4 | 1/9/2026 | -75 | ||
| 5 | 1/12/2026 | 300 | ||
| 6 | 1/15/2026 | -40 |
With one range and a condition, SUMIF tests and sums the same cells. Only 500 and 300 are above zero, so the total is 800.
4. Total the January transactions.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Amount | ||
| 2 | 1/15/2026 | 200 | 275 | |
| 3 | 2/3/2026 | 150 | ||
| 4 | 1/28/2026 | 75 | ||
| 5 | 2/10/2026 | 60 |
SUMIFS needs a start and an end for the date window: on or after January 1 and before February 1. The January amounts in B2 and B4 are 200 and 75, which add to 275.
5. Fill in the category for Staples from the Vendors sheet.
| A | B | |
|---|---|---|
| 1 | Vendor | Category |
| 2 | Staples | Office supplies |
| A | B | |
|---|---|---|
| 1 | Vendor | Category |
| 2 | Shell | Fuel |
| 3 | Staples | Office supplies |
VLOOKUP finds Staples in the first column of the vendor list on the other sheet and returns the second column, the category.
6. Put an invoice that is 45 days past due into its ageing bucket.
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Days past due | Bucket |
| 2 | INV-7 | 45 | 31-60 |
The nested IF checks the smallest bucket first. 45 is not within 30, so the formula moves on, and 45 is within 60, so it returns 31-60.
7. Count the transactions that have no category.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Category | ||
| 2 | 1/2/2026 | Rent | 2 | |
| 3 | 1/3/2026 | |||
| 4 | 1/4/2026 | Fuel | ||
| 5 | 1/5/2026 |
COUNTBLANK counts the empty cells in B2:B5. B3 and B5 are empty, so the answer is 2.
8. Show the bank's debit of 45 as a negative amount, whichever sign it arrives with.
| A | B | |
|---|---|---|
| 1 | Bank debit | Ledger amount |
| 2 | 45 | -45 |
ABS removes the sign, and the minus in front makes the result negative, so a debit of 45 or -45 both come out as -45.
How to prepare in a week
- Day 1. Running balances and fill down. Build a bank-register sheet and check the last balance against a hand calculation.
- Day 2. SUMIF and SUMIFS with one and two conditions, including a date window built from DATE. Check each by filtering.
- Day 3. Reconciliation: COUNTIF and VLOOKUP to find unmatched items between two lists, then total the unmatched amounts.
- Day 4. Lookups into a vendor or account list, with IFNA for missing vendors. Practice locking the table range with dollar signs.
- Day 5. Signs, ABS and ROUND. Make a column of mixed debits and credits consistent, and round amounts to the cent where asked.
- Day 6. Ageing buckets with nested IF, days past due with date subtraction, and a short pivot table of totals by category in Excel.
- Day 7. A timed mixed set, then a review of every miss. Rebuild one reconciliation from scratch without notes.
Which of our practice fits
The Everyday Excel track covers the core of this role: conditional totals, lookups, dates and rounding. The free lessons on absolute references and AutoFilter help with the reconciliation tasks.
Because bookkeeping tests can involve a ledger with real-looking dates and signs, practice with sheets that have both. After every reconciliation exercise, total the matched and unmatched items separately and confirm they add back to the starting figure, because that cross-check is the habit this work rewards.
Function guides for this role
Questions
- What Excel test do bookkeepers take?
- It depends on the employer. Some use a general Excel assessment, some a small workbook of transactions to reconcile. Ask what format to expect.
- Do I need to know accounting software formulas?
- Tests are about Excel itself. Accounting knowledge helps you read the task, but the formulas are ordinary Excel formulas.
- How should I handle debits and credits?
- Follow the sign convention given in the task. If amounts arrive mixed, ABS makes them consistent before you apply a sign.
- Why does my balance column go wrong after I insert a row?
- A formula that points at the wrong row, or a hard-typed balance, stops following the data. Each balance should refer to the one above it.