In this article
- 3 formulas to learn first: SUMIFS for totals, XLOOKUP for lookups and COUNTIF for matching two lists, each with one example.
- The other 12 everyday formulas listed by job: counts, status, error messages, cleaning text, dates, deadlines and dynamic lists.
- XLOOKUP, UNIQUE and FILTER need Microsoft 365 or Excel 2021 and later; VLOOKUP and INDEX with MATCH are available in Excel 2016, 2019, 2021, 2024 and Microsoft 365.
- Turn every list into a Table first, and check that totals tie before you send anything.
Excel for chartered accountants is not about knowing hundreds of functions. A tax and accounts office runs on about fifteen formulas, used again and again on the same kinds of files: a fee register, a client list, a task tracker and a document checklist. This guide explains the three formulas to learn first, with one office example each, and then lists the other twelve by the job they do, so you know what to learn next.
Nothing here needs add-ins or paid tools. It is Excel for chartered accountants and their staff as it is used on an ordinary office computer. The examples use a simple fee register: Invoice Date in column B, Client Code in C, Partner in F, Amount in G, Received in H and Outstanding in I.
Excel for chartered accountants: formula 1, SUMIFS for totals
Most questions a partner asks about the fee register are totals with conditions. How much is outstanding for Anil's clients? How much did audit work bring in this year? SUMIFS answers both.
Total outstanding for partner Anil:
=SUMIFS(I:I, F:F, "Anil")
Read it as: add up column I, but only the rows where column F says Anil. You can add more conditions in pairs, such as a service type or a date range.
Watch one trap. In SUMIFS the range you add up comes first. In the older SUMIF it comes last. Staff who learned one and then use the other get wrong totals that look perfectly reasonable, and nobody notices until the totals do not tie.
Formula 2: XLOOKUP to pull data from another list
If you learn only one more formula, make it a lookup. Lookups save the most time in any office. Type a client code once and let Excel fetch the name, partner or mobile number from the client master.
Client name from the code in C2, with the client master on a sheet called Clients:
=XLOOKUP(C2, Clients!A:A, Clients!B:B, "Not found")
It looks for the code in column A of Clients, returns the matching name from column B, and shows "Not found" if there is no match. XLOOKUP is in Microsoft 365, Excel 2021 and Excel 2024. It is not in Excel 2016 or 2019. There, VLOOKUP or INDEX with MATCH do the same job.
Why does this one formula matter so much? Because most office registers repeat the same details again and again. When the partner name is typed by hand on every invoice, it will be typed wrongly somewhere. When it is looked up from one client master, it is correct everywhere, and a change in the master flows through to every sheet. One master list, many lookups: that is the habit that keeps an office's numbers consistent.
Excel for Tax Practice is 14 short lessons on real office files: cleaning data, lookups, matching two lists, pivot tables, Power Query, 4 ready office templates and an office dashboards workbook.
Formula 3: COUNTIF to match two lists
Matching two lists is a daily office job: documents received against the checklist, clients billed against the client master, fee receipts against the bank list. Say your checklist is in a sheet called Checklist (column A) and documents received are in a sheet called Received (column A). In Checklist B2:
=IF(COUNTIF(Received!A:A, A2)>0, "Received", "Pending")
COUNTIF counts how many times the document appears in the received list. More than zero means it came in. The IF around it turns that count into a word anyone can read.
The same idea finds duplicates. Count each value against its own column, and anything above one is repeated. Look at duplicates before you delete anything. Two rows may look the same and still be two genuine entries.
The most common reason a match fails is not the formula. It is the data. "Form 16" in one list and "Form-16" in the other will never match. Agree one short list of document names in the office, use it in both lists, and the formula does the rest.
The other twelve formulas, by job
Once the three above are second nature, these twelve cover almost everything else a tax and accounts office does in Excel. Learn them one at a time, on a real office need.
| Job | Formula | What it does |
|---|---|---|
| Counts | COUNTIFS | Counts rows that meet several conditions, such as unpaid invoices per partner |
| Status | IF and IFS | Turns a number into a word, such as Paid or Pending, or an ageing bucket |
| Errors | IFERROR | Shows a clean message instead of #N/A. Use it sparingly; errors often mean something. |
| Lookups (older Excel) | VLOOKUP | A classic lookup available in Excel 2016 and later |
| Lookups (any direction) | INDEX with MATCH | A lookup that can look to the left and is available in Excel 2016 and later |
| Cleaning | TRIM and CLEAN | Removes extra spaces and invisible characters from exported data |
| Labels | TEXT | Turns a date or number into a label, such as a month name for a report |
| Ageing | TODAY | Gives today's date, so days outstanding update by themselves |
| Renewals | EDATE | Moves a date forward by months, for renewal dates |
| Month ends | EOMONTH | Finds the last day of a month, for internal deadlines |
| Deadlines | NETWORKDAYS | Counts working days left, skipping weekends and office holidays |
| Live lists | UNIQUE and FILTER | Build a list with no repeats, or a live view for one partner. Microsoft 365, Excel 2021 or 2024 only. |
Knowing the names is the easy part. Each one has a small trap: the version it needs, a text date that looks like a date, a holiday list that is out of date, an ageing formula that counts paid invoices too. Those traps are learned by practice on realistic files with a known right answer, not by reading.
Put it together: a one-screen fee summary
The real value comes when the formulas work together. A one-screen fee summary lists each partner once, with total billed, total outstanding, the number of unpaid invoices and how many are more than 60 days old. A partner can read it in ten seconds. It uses only the formulas above: UNIQUE for the partner list, SUMIFS for the totals and COUNTIFS for the counts.
Add one check cell below it: the total outstanding in the summary minus the total outstanding in the register. It must show zero. If it does not, a partner name is spelt two ways or a row is missing. That single check catches more errors than any amount of re-reading.
If you rebuild the same report from fresh downloads every month, formulas are only half the answer. The copying, cleaning and stacking can be done by Power Query with one Refresh. The same idea scales into a full client MIS, covered in MIS Report Format in Excel for Clients (Free Template).
The same formulas run through year-end finalisation files, where one broken tie between the trial balance and the statements is easy to miss. For that work, our own SignReady runs the standard ties, movements and ratio checks for each client next to Tally and Excel.
Five habits that make every formula safer
- One clean list per job, not ten sheets with the same columns. One row per item, one header row.
- Tables everywhere (Ctrl+T), so ranges grow with the data.
- Totals that tie: after any summary, check that the grand total matches the register total.
- Show Formulas (Ctrl+`) before you send a file, to spot typed numbers where a formula should be.
- Send a PDF, not the workbook, when a client only needs to see the result.
These habits only work if the whole office follows them. One person typing numbers over formulas, or keeping a private copy of the register, undoes the rest.
When you need a formula you do not know, describe your layout to an AI tool instead of pasting the data. Our ChatGPT for CA: Safe Prompts for Daily Office Work guide shows how to ask for exactly that. For monthly repeat jobs, read Power Query and Macros for Accountants: Automate Excel.
The Excel for Tax Practice course teaches these formulas on real office files, with a practice workbook and answer key, 4 ready office templates (fee register, client master, work tracker and DSC register) and an office dashboards workbook. Start with the free first lesson on the Excel habits that save an hour, then work through lookups and matching two lists in the full Excel course.
Questions people ask
Which Excel formulas should a CA know?
The everyday set: SUMIFS, COUNTIFS, IF, IFERROR, XLOOKUP or VLOOKUP, INDEX with MATCH, COUNTIF for matching lists, TRIM, TEXT, TODAY, EDATE, NETWORKDAYS, UNIQUE and FILTER. Together they cover most registers, trackers and reports in an office.
Is XLOOKUP better than VLOOKUP?
Usually yes. XLOOKUP can look left, does not break when columns are inserted, and has a built-in "not found" message. But it needs Microsoft 365, Excel 2021 or later, so use VLOOKUP or INDEX with MATCH if anyone in the office has an older version.
How can I learn Excel for articleship?
Practise on the files you actually see in the office: a client list, a fee register, a document checklist. Learn one formula a day, use Tables, and build one small tracker end to end. A short course with practice files speeds this up.
How do I compare two lists in Excel?
Give both lists one common key, such as a client code, then use COUNTIF or XLOOKUP to mark each row as found or not found. Clean extra spaces with TRIM first, or matches will fail.
Can ChatGPT write Excel formulas?
Yes, if you describe the layout (which column holds what) instead of pasting real data. Always test the formula on your own sheet and check a few rows by hand.
Sources
- Microsoft Support, XLOOKUP function
- Microsoft Support, SUMIFS function
- Microsoft Support, FILTER function
Try the free first lesson, "Excel habits that save an hour". The Office plan gives every staff member every course, including Excel Automation with Power Query and macros.
