Excel for Accountants: The Formulas and Skills That Matter
Essential Excel skills for accountants and bookkeepers: SUMIFS for ledgers, lookups for reconciliations, ROUND, dates, absolute references and error checks.
By Ayoub Farid · · 1 min read
Accounting work is built on spreadsheets even when there's dedicated software: reconciliations, schedules, accruals, budgets, variance reports. These are the skills accounting roles lean on.
Formulas
| Task | Formula |
|---|---|
| Totals by account or period | SUMIFS |
| Matching transactions between two lists | XLOOKUP, COUNTIFS, compare two columns |
| Rounding to cents | ROUND |
| Aging receivables | =TODAY()-C2 with IFS bands |
| Month end | =EOMONTH(A2,0) |
| Variance | =B2-C2 and =(B2-C2)/C2 |
| Checks | =SUM(debits)-SUM(credits) should equal 0 |
Reconciliation in practice
Bank statement in one sheet, ledger in another. For each bank line:
=COUNTIFS(Ledger!$C:$C,C2,Ledger!$A:$A,A2)Zero means "not in the ledger". Then filter on zero. For amounts within a tolerance, use helper columns with ROUND.
Aging buckets
Days overdue in D2:
=IFS(D2<=30,"0–30",D2<=60,"31–60",D2<=90,"61–90",TRUE,"90+")Then SUMIF by bucket.
Habits that prevent errors
- Lock rates and totals with $. See absolute references.
- Round where amounts must tie out, instead of only formatting.
- Build check cells (should be zero) and highlight them red if not.
- Never type numbers into formula columns.
Beyond formulas
Pivot tables for trial balance summaries, Power Query for importing bank or ERP exports, and data validation for consistent account codes.
Frequently asked questions
What Excel skills do accountants need?
SUMIFS, lookups, ROUND, date functions, pivot tables, absolute references and reconciliation techniques.
Do accountants still use Excel?
Yes, widely, alongside accounting software, for analysis, schedules and reconciliations.
What is the most useful Excel function for accountants?
SUMIFS and XLOOKUP are used constantly for summaries and reconciliations.
How do I reconcile two lists in Excel?
Use COUNTIFS or XLOOKUP to find each item from one list in the other and flag the missing ones.