In this article
- Set up the practice file first
- Excel formulas for chartered accountants: totals and counts (1 to 4)
- Lookups: pull data from another list (5 to 7)
- Matching lists and cleaning text (8 to 10)
- Dates and deadlines (11 to 13)
- Dynamic lists for quick reports (14 and 15)
- Put it together: a one-screen fee summary
- Five habits that make every formula safer
- Questions people ask
- Sources
- 15 formulas grouped by job: totals and counts, lookups, matching lists, cleaning text, dates, and dynamic lists.
- Every example uses one fee register and one client list, so you can build them in a single practice file.
- XLOOKUP, UNIQUE and FILTER need Microsoft 365 or Excel 2021 and later; VLOOKUP and INDEX with MATCH work everywhere.
- 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 gives you those 15 formulas, each with a copy-ready example, so you or your staff can build them in one practice file today.
Every example below uses the same two sheets, so you can follow along. 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.
Set up the practice file first
Make a new workbook with two sheets. Use made-up data, not real client files.
| Sheet | Columns |
|---|---|
| Fees (the fee register) | A Invoice No, B Invoice Date, C Client Code, D Client Name, E Service, F Partner, G Amount, H Received, I Outstanding (=G2-H2) |
| Clients (the client master) | A Client Code, B Client Name, C Partner, D Mobile, E Renewal Date |
Fill 20 to 30 rows. Then click inside each list and press Ctrl+T to turn it into a Table. Tables grow automatically when you add rows, so your formulas keep working next month. The examples use plain column references like G:G so they are easy to read; inside Tables you can use the column names instead.
Excel formulas for chartered accountants: totals and counts (1 to 4)
1. SUMIFS: total with conditions
Total outstanding for partner Anil:
=SUMIFS(I:I, F:F, "Anil")
Total billed for audit work by Anil: =SUMIFS(G:G, F:F, "Anil", E:E, "Audit"). Note that the range you add up comes first in SUMIFS. In the older SUMIF it comes last, which is a common source of mistakes.
2. COUNTIFS: count with conditions
How many invoices are still unpaid for Anil: =COUNTIFS(F:F, "Anil", I:I, ">0"). Use it for any "how many" question: tasks pending per person, clients per partner, documents still to come.
3. IF and IFS: a status in words
A simple status: =IF(I2>0, "Pending", "Paid").
An ageing bucket, using days outstanding in column J (formula 10 below): =IFS(I2=0, "Paid", J2<=30, "0 to 30", J2<=60, "31 to 60", TRUE, "Over 60"). IFS is in Microsoft 365 and Excel 2019 or later; in older versions, nest IF inside IF.
4. IFERROR: a clean message instead of #N/A
Wrap any formula that may fail: =IFERROR(VLOOKUP(C2, Clients!A:B, 2, FALSE), "Check code"). Use it sparingly. An error is often telling you something real, such as a client code typed wrongly.
Lookups: pull data from another list (5 to 7)
If you learn only one group of formulas from this guide on Excel for chartered accountants, make it this one. Lookups save the most time in any office. Type a client code once and let Excel fetch the name, partner or mobile number.
5. XLOOKUP
Client name from the code in C2: =XLOOKUP(C2, Clients!A:A, Clients!B:B, "Not found"). It looks in column A of Clients, returns the matching value 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.
6. VLOOKUP
The same job in every version: =VLOOKUP(C2, Clients!A:D, 2, FALSE). The 2 means "second column of the range". Always end with FALSE for an exact match, or Excel may return the wrong client.
7. INDEX with MATCH
Works in every version and can look to the left: =INDEX(Clients!B:B, MATCH(C2, Clients!A:A, 0)). MATCH finds the row; INDEX returns the value from that row.
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.
Matching lists and cleaning text (8 to 10)
8. COUNTIF: found or not found
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")
Clients with no invoice this year, in Clients F2: =IF(COUNTIF(Fees!C:C, A2)=0, "No invoice", "").
To find duplicates in a list, count each value against its own column: =COUNTIF(A:A, A2)>1 shows TRUE for every repeated invoice number. Look at duplicates before you delete anything. Two rows may look the same and still be two genuine entries.
9. TRIM and CLEAN: remove hidden spaces
Data exported from software or copied from a website often has extra spaces and invisible characters. In a helper column: =TRIM(CLEAN(B2)). Then copy the helper column and use Paste Special, Values over the original. Keep the original file untouched in case you need to start again.
10. TEXT: dates and numbers as labels
A month label for reports: =TEXT(B2, "mmm-yy") turns 15-04-2026 into Apr-26. Use it for headings and summaries, not for calculations, because the result is text, not a date.
Dates and deadlines (11 to 13)
Excel stores every date as a number, so you can add, subtract and compare dates like any other figure.
11. TODAY: days outstanding
Days since the invoice, only for unpaid ones, in J2: =IF(I2>0, TODAY()-B2, ""). TODAY updates itself every time the file opens, so the register ages on its own.
12. EDATE and EOMONTH: renewals and month ends
A renewal date one year after a start date: =EDATE(B2, 12). The last day of the invoice month: =EOMONTH(B2, 0). The 7th of the next month, for an internal "send the monthly report by" date: =EOMONTH(B2, 0)+7.
13. NETWORKDAYS: working days left
Working days from today to a deadline in K2, skipping weekends and office holidays listed in a sheet called Holidays: =NETWORKDAYS(TODAY(), K2, Holidays!A:A). If your office works on Saturdays, use NETWORKDAYS.INTL and set the weekend to Sunday only.
Dynamic lists for quick reports (14 and 15)
14. UNIQUE: a clean list of names
A list of every client billed, with no repeats: =UNIQUE(Fees!C2:C500). The result spills down by itself. Use it as the first column of a small summary, then add SUMIFS next to it for totals per client.
15. FILTER: a live view for one person
All unpaid invoices for Anil, in one live list: =FILTER(Fees!A2:I500, (Fees!F2:F500="Anil")*(Fees!I2:I500>0), "Nothing pending"). The * means "and both conditions are true". Give each partner a sheet with their own FILTER and nobody has to sort the master register again.
UNIQUE and FILTER need Microsoft 365, Excel 2021 or Excel 2024. In older versions, use a pivot table or the AutoFilter button instead.
Put it together: a one-screen fee summary
Now combine the formulas into one small summary sheet that a partner can read in ten seconds. Make a new sheet called Summary.
- In A2, list the partners with =UNIQUE(Fees!F2:F500).
- In B2, total billed per partner: =SUMIFS(Fees!G:G, Fees!F:F, A2), copied down.
- In C2, total outstanding: =SUMIFS(Fees!I:I, Fees!F:F, A2).
- In D2, number of unpaid invoices: =COUNTIFS(Fees!F:F, A2, Fees!I:I, ">0").
- In E2, unpaid for more than 60 days: =COUNTIFS(Fees!F:F, A2, Fees!J:J, ">60").
- Below the table, add a check cell: =SUM(C2:C10)-SUM(Fees!I:I). It must show zero. If it does not, a partner name is spelt two ways or a row is missing.
Add a simple column chart of outstanding by partner and you have a page you can print or save as PDF for the Monday meeting. The same idea scales into a full client MIS, covered in MIS Report Format in Excel for Clients (Free Template).
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.
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: 25 Safe Prompts for Daily Office Work guide has ready prompts for exactly that. And if you rebuild the same report every month, the next step is Power Query and macros, explained in Power Query and Macros for Accountants: Automate Excel and taught hands-on in Excel Automation for Tax & Accounts Offices.
The Excel for Tax Practice course teaches these formulas on real office files, with practice sheets, 4 ready office templates 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.
