Accounting Excel test practice

Preparing for an Excel skills test for an accounting role? Practice the spreadsheet tasks accountants do every week, in a real spreadsheet, with every answer checked instantly.

If your hiring process includes a spreadsheet exercise, it might be a timed test on a laptop, a file you take home, or a task you talk through in an interview. Each employer designs its own, so nobody can tell you exactly what will be on yours. What you can do is get fluent at the core tasks of the job, so that whatever form the test takes, the work feels familiar.

This page lists those tasks, explains what makes each one tricky, and links to a full guide and hands-on exercises for each. Every exercise runs in a spreadsheet in your browser. Your formulas run and the results are compared with a correct solution. Wrong cells are marked with a hint about the likely mistake, and New numbers gives you the same task with a different company and different amounts.

How to prepare in a week or two

You don't need to learn every function. You need to be quick and accurate with a small set of tasks and the handful of functions behind them. A sensible plan:

1. Start with the trial balance. It practices conditional sums, lookups and IF together, and those three show up inside almost every other task.
2. Then the routine tasks: bank reconciliation, loan amortization, depreciation, receivables aging and payroll. Do the free exercise of each, then the harder ones.
3. Then ratios. They are quick to calculate but easy to get backwards, which makes them a good final check of your accuracy.
4. Repeat with new numbers. A test gives you an unfamiliar company. Practicing with fresh numbers is the closest thing to that.

Throughout, practice working the way a reviewer wants to see: formulas that refer to input cells rather than typed numbers, one formula filled down rather than ten retyped, and absolute references (the dollar signs in $B$2) where a cell must not move. Those habits make a file easier to review and harder to break.

The tasks worth practicing

Bank reconciliation

A bank reconciliation explains why the bank statement and the books show different cash balances, and brings both to the same adjusted figure. On the bank side you add deposits in transit and subtract outstanding checks. On the book side you subtract bank fees and returned (NSF) checks and add interest the bank has credited.

Practice tasks such as totaling the uncleared items straight from a check register with a conditional sum (SUMIFS), so the reconciliation updates when an item clears. The classic mistakes are adding the outstanding checks instead of subtracting them, and totaling the checks that have cleared rather than the ones that haven't.

Guide and exercises: bank reconciliation in Excel.

Totals by account and lookups: the trial balance

A trial balance turns a list of ledger lines into one balance per account. That means totaling debits and credits for each account (SUMIFS), looking up each account's type in a chart of accounts (XLOOKUP, or VLOOKUP or INDEX/MATCH), and showing each net balance in the debit or the credit column, never both (IF or MAX).

This is the task that most rewards careful references. Lock the ledger ranges with dollar signs before you fill down, or the ranges drift and the totals go wrong one row at a time. When the chart of accounts isn't sorted, ask the lookup for an exact match; an approximate match on unsorted data returns a plausible but wrong answer. And remember that a trial balance shows each account's net balance, not its total debits and credits side by side.

Guide and exercises: trial balance in Excel.

Loan amortization

An amortization schedule shows how each fixed loan payment splits between interest and principal, and how the balance falls to zero. Practice calculating the payment (PMT), building the month-by-month schedule, and pulling the interest and principal parts of a single payment (IPMT and PPMT).

The usual errors: using the annual rate where the monthly rate is needed (divide by the number of payments per year), showing the payment as a negative number because of the function's sign convention, and charging interest on the original loan amount instead of each month's beginning balance.

Guide and exercises: loan amortization schedule in Excel.

Depreciation schedules

Depreciation spreads an asset's cost, less its salvage value, over its useful life. Practice the three methods side by side: straight-line (SLN), double-declining balance (DDB) and sum-of-the-years' digits (SYD). Then build a schedule that tracks accumulated depreciation and book value year by year.

Watch for forgetting the salvage value, hard-coding the year so every row shows the same amount, and calculating book value as cost minus only this year's depreciation instead of all depreciation to date.

Guide and exercises: depreciation schedule in Excel.

Accounts receivable aging

An aging report groups unpaid invoices by how long they have been outstanding, so a business can see which customers are slow and estimate how much may never be collected. Practice calculating days outstanding from an aging date, sorting invoices into 0-30, 31-60, 61-90 and over-90 buckets with IF, totaling each bucket with SUMIFS and applying a percentage to each bucket to estimate the allowance for doubtful accounts.

Boundaries are the trap here: an invoice exactly 30 days old belongs in 0-30, not 31-60. Lock the aging date with dollar signs, total amounts rather than counting invoices, and use each bucket's own percentage.

Guide and exercises: accounts receivable aging report in Excel.

Payroll calculations

Payroll in a spreadsheet goes from hours to gross pay, from gross pay to net pay, and on to the employer's own payroll taxes. Practice overtime with IF (only the hours above the threshold get the multiplier), withholding on taxable wages after a pre-tax deduction, net pay, the employer's tax cost, and flags that mark rows needing review when either of two conditions is true.

Our payroll exercises use example rates for practice, not real tax rates. They teach the structure of the calculation and are not a guide to working out anyone's actual taxes.

Guide and exercises: payroll calculations in Excel.

Financial ratios

Ratios turn a set of financial statements into a few comparable numbers. Practice the three families: profitability (gross margin, net profit margin, return on assets), liquidity (current ratio, quick ratio, working capital) and solvency (debt-to-equity, debt ratio, times interest earned).

Each ratio is a single division, so the mistakes are about which numbers go where: dividing the wrong way round, using gross profit instead of revenue, counting inventory in the quick ratio, or adding back only interest, not interest and tax, when working out earnings before interest and taxes. Rounding with a formula rather than with formatting is worth practicing too, because a formatted cell still holds the unrounded value.

Guide and exercises: financial ratios in Excel.

Show your understanding, not only your formulas

If you are asked to talk through your work, being able to explain the accounting behind a formula helps: why a deposit in transit increases the bank balance, why the allowance grows as invoices age, why a trial balance that balances can still contain errors. The bookkeeping side of each task is covered in our bookkeeping lessons, for example bank reconciliation journal entries and the depreciation journal entry. If your role is mainly bookkeeping, see bookkeeping test practice.

Working under time pressure

If your test is timed, speed comes from not having to think about the mechanics. A few things to practice until they are automatic:

• Read the whole task first. Note which cells are inputs and which you need to fill, so you build formulas that refer to them.
• Write one formula, then fill. Get the first row right, check it, add the dollar signs, and fill down. Then spot-check the last row.
• Check totals that must agree. Debit and credit totals, adjusted bank and book balances, a loan balance that ends at zero. These are free checks of your work.
• Translate the task into a function. "Total the amounts for this account" is a conditional sum. "Find this account's type" is a lookup. Naming the tool before you type saves time.

Take a timed practice test

When the tasks feel familiar one at a time, try them together against the clock. The Excel test for accounting jobs gives you 30 minutes for six tasks: a share-of-total calculation with locked references, totals by account and department, a lookup that has to catch account numbers missing from the chart, a full bank reconciliation from the check register and deposit list, and receivables and payables aging.

It works like a real timed test. There are no hints or feedback while the timer runs; you submit each answer and move on, or skip it. When time is up you get your score and the reference answer for every task, so you can see exactly where the minutes went. The numbers change every time you take it, so a retake is a fair test rather than a memory exercise. Score 70% or more for a certificate of completion you can send to an employer, who can check it on the verification page. The timed test uses Premium exercises, so it needs a Premium plan.

About these exercises

Every answer is checked by code, never by opinion: your formulas are run and the resulting values are compared with a correct solution. The first exercise of every lesson is free with no sign-up, and you can see every lesson on the lessons page. Premium unlocks the harder exercises in each lesson; plans are on the pricing page.

The same lessons also serve as MO-220 practice for the Excel for Accounting Associate exam; the MO-220 practice page maps them to the exam's objectives. Accounting Exercises is not affiliated with Microsoft or Certiport.