Excel for Accountants: The Useful Functions

Excel is the most undervalued item on an accountant’s CV, largely because everyone claims it and few people can do the things that actually save time.

What follows is not a list of two hundred functions. It is the short set that covers most real work, plus the one pattern that turns a full day of reconciliation into twenty minutes.

Lookups — the foundation

Matching one list against another is the single most common accounting task in Excel: invoices against payments, your register against a portal download, a payroll register against a bank statement.

XLOOKUP is the current function for this and handles most cases cleanly, including returning a chosen value when nothing matches. If you are on a version without it, INDEX combined with MATCH does the same job and is worth knowing anyway, because it works in both directions where the older VLOOKUP does not.

Learn one of these properly rather than three of them badly. The common failure is not the formula — it is data that does not match because of trailing spaces or numbers stored as text, which is why TRIM and VALUE belong in the same lesson.

Conditional aggregation

SUMIFS and COUNTIFS answer nearly every question a manager asks: total by customer, by month, by tax rate, by cost centre. They take multiple conditions, which is what makes them more useful than the single-condition versions.

If you can write a SUMIFS confidently you can answer most ad-hoc questions without building anything.

Dates, which are where people lose hours

Accounting is full of date arithmetic: ageing a receivables list, working out whether an invoice has passed 180 days, bucketing by period.

The essential ones are EOMONTH for period ends, EDATE for shifting by months, and plain subtraction for the number of days between two dates. The trap is dates stored as text, which look identical on screen and refuse to calculate. If date arithmetic returns something absurd, check that first.

A directly useful application: an ageing column on your purchase register flags invoices approaching the 180-day mark before the input tax credit reversal becomes unavoidable.

PivotTables

For summarising a large transaction list quickly, nothing else comes close, and they take about an hour to learn to a useful standard.

The habit worth building is refreshing rather than rebuilding: structure the source as a proper table so new rows are picked up automatically. People who rebuild the pivot every month are doing a job the software will do.

The reconciliation pattern

This is the one worth learning properly, because it recurs constantly — GSTR-2B against your purchase register, bank statement against your cash book, ledger against a supplier statement.

1. Normalise both sides first. TRIM the text, convert numbers stored as text, standardise the date format. Most reconciliation failures are formatting, not substance.
2. Build a matching key — often an invoice number, sometimes a concatenation of a few fields where numbers repeat across suppliers.
3. Look up each side in the other. Two columns: found in theirs, found in mine.
4. Work the three buckets. In both and agreeing needs nothing. In both and differing is usually an amount or a rate. In one only is either something they have not reported or something you have not booked — and those are different problems with different owners.

That structure is the whole job. Everything else is following up the exceptions.

What not to bother with yet

Macros and VBA are further down the list than their reputation suggests. Most accounting work is served by the functions above, and an accountant who writes a macro to automate something they do not fully understand has built a faster way to be wrong.

Quick reference: which function, and why

The functions above cover most of the work. Here they are side by side, as a reference to come back to rather than something to memorise in order.

Function	Use case	Why it, and not something else
XLOOKUP	Matching one list against another: invoices against payments, your register against a portal download	Works in both directions, handles a missing match cleanly, and is the current standard
INDEX and MATCH	The same lookups, on a version of Excel without XLOOKUP	Does the same job as XLOOKUP and works in both directions where VLOOKUP does not
SUMIFS	Totals by customer, month, tax rate or cost centre	Takes multiple conditions at once, answering a question without building a report
COUNTIFS	How many invoices, transactions or entries meet several conditions together	Same logic as SUMIFS, for counts rather than totals
EOMONTH	Finding the last day of a month, for period-end and ageing work	Handles months of different lengths and leap years without a manual lookup
EDATE	Shifting a date forward or back by a number of months, for due dates and renewal dates	Moves by calendar months rather than a fixed number of days, which matters when months differ in length
TRIM	Cleaning text before a lookup or a match	Removes the invisible trailing spaces that make two identical-looking values fail to match
VALUE	Converting a number stored as text into an actual number	Fixes the single most common cause of a lookup or a sum returning zero or an error
TEXT and &, or CONCAT	Building a composite key when one field is not unique on its own	Produces a consistent, joinable string; TEXT keeps date formats consistent across both sides
PivotTables	Summarising a large transaction list by category, period or any other field	Restructures the data on demand without formulas, and refreshes when the source table grows

The reconciliation pattern, in sequence

The pattern described above is easier to apply as a numbered sequence than as a set of principles to hold in mind at once.

1. Normalise both sides: trim the text fields, convert any numbers stored as text, and put both dates into the same format. Most reconciliation failures at this stage are formatting, not substance.
2. Build a matching key on each side, usually the invoice number, or a concatenation of a few fields where a plain invoice number repeats across suppliers.
3. Look up each side in the other, adding a column on each list that records whether it was found on the opposite list.
4. Work the three resulting buckets: found on both and agreeing needs nothing; found on both and differing is usually a wrong amount or rate; found on one side only is either something the other party has not reported yet, or something you have not booked yet, and those are different problems with different people responsible for fixing them.

A concrete version of step one: a purchase register storing invoice numbers as ‘INV-1002’ against a supplier portal download storing the same invoice as ‘1002’ will fail every single lookup until one side is stripped down to match the other, usually with a helper column built on a text function rather than by editing the original data.

Building a composite key when one field alone is not unique

Some registers reuse invoice numbers across suppliers, restarting at 1 for every vendor, which breaks a matching key built on the invoice number alone. The fix is a composite key: join the supplier code or name with the invoice number and, where needed, the date, keeping the date in a consistent text format so it joins cleanly. Both sides of the reconciliation then get the same composite key built the same way, and the lookup runs against that instead of the ambiguous single field.

This one habit, building a key deliberately rather than assuming the obvious field is unique, is behind most reconciliations that fail to match when they should.

Stopping lookups from failing silently

A lookup that returns an error is at least visible. The more expensive failure is one that returns a wrong-but-plausible value, or a blank that looks like a genuine zero, because nothing flags it for a second look.

Two habits reduce this. First, treat dates and invoice numbers as suspect until proven otherwise. Numbers stored as text look identical to real numbers on screen and will not sum, sort or match correctly against a numeric key; converting them fixes it. Second, build source data as a proper Excel table rather than a plain range. A formula written against a table expands automatically as rows are added, closing off the common failure of a SUMIFS or lookup range that quietly stops covering the last few rows of a growing register, understating a total without any error message at all.

Both habits cost a few minutes to build once and keep paying for themselves every month after. For the accounting side these totals eventually feed, TallyPrime for accountants: what to learn first covers where the numbers actually come from.

Frequently asked questions