Excel for CA Articleship: Functions You Will Actually Use

Learn the essential Excel functions every CA article should know and use during articleship. Explore practical Excel formulas such as VLOOKUP, XLOOKUP, SUMIF, COUNTIF, IF, IFERROR, Pivot Tables, and other time-saving features used in audit, accounting, financial analysis, and reporting. Understand how to apply these functions to real-world CA assignments, improve working paper preparation, automate repetitive tasks, and increase productivity during your CA articleship.

04 November, 2026

Nobody fails articleship because their Excel is weak. But it quietly decides how late you sit in office, and what work your senior hands you next.

The article who can summarise a 40,000 line ledger before lunch gets pulled into the interesting parts of the audit. The one who scrolls through it gets more vouching.

This is not a list of fifty functions. It is the short list that actually comes up, and what each one is for.

The five functions that cover most of the work

Lookups. This is the one you cannot avoid. Matching party names in the books against GSTR-2B, pulling a rate from a master sheet, tagging 500 ledger entries with a group head. XLOOKUP is easier and does not break when columns move, but it needs Microsoft 365 or Excel 2021 onwards. Plenty of firms still run Excel 2019 or older, so learn VLOOKUP first.

SUMIFS. Totalling by condition. Expenses by head, sales by month, purchases above a threshold. Once you are comfortable with SUMIFS you stop building manual subtotals.

COUNTIFS. Mostly for finding duplicates. Duplicate invoice numbers and vendor codes are a standard audit finding, and COUNTIFS is how you catch them in seconds.

IFERROR. Wraps a formula so #N/A does not spread across your working. Use it honestly though. An #N/A often means the item is genuinely not there, which is itself the finding.

TRIM, and date handling. Half the problems in client data are invisible: trailing spaces that break lookups, dates stored as text so they will not sort. TRIM fixes the first. For the second, spot it (left aligned numbers are text) and fix it with Text to Columns.

Pivot tables matter more than clever formulas

If you learn one thing properly, learn pivot tables.

A senior hands you a 40,000 row general ledger and asks what the monthly trend of repairs expense looks like. With formulas that is a long afternoon. With a pivot table it is thirty seconds, and you can then drill into the month that looks odd.

That is the real use in audit. Pivots are not for presentation, they are for finding the thing worth asking about: the month where one expense head jumps, the vendor who suddenly accounts for most of the purchases.

Start with three habits. Convert your data to a Table so the pivot picks up new rows. Put the ledger head in rows and the month in columns. Then double click any number you do not believe.

Reconciliations: the task you will actually be given

In your first month somebody will hand you two files and ask you to reconcile them. GSTR-2B against the purchase register, the bank statement against the bank book, or 26AS against TDS in the books.

Every one of these follows the same method:

  1. Clean both sides first. TRIM the text, fix dates stored as text, check the amounts are numbers
  2. Decide your matching key. Invoice number plus GSTIN beats party name, because party names are spelt three different ways in every ledger
  3. Lookup from A into B, then from B back into A. People forget the second direction, which is where you find what the books are missing
  4. Keep the #N/A items. Those two lists are your reconciliation: in books not in portal, and in portal not in books
  5. Explain each difference instead of forcing a tally: timing difference, wrong GSTIN, credit note not booked, vendor filed late

That last step is the job. Anyone can make two numbers agree by plugging a difference. The point is to say why they differ.

Shortcuts worth putting into muscle memory

You do not need fifty. These save real time in a large file.

Shortcut

What it does

Ctrl + Down / Right

Jump to the end of the data

Ctrl + Shift + Down

Select everything below, to fill a formula down

F4

Lock a reference, and repeat the last action

Alt + ;

Select visible cells only, not hidden rows

Ctrl + Shift + L

Turn filters on and off

Ctrl + `

Show all formulas at once

That last one is underrated. Before you trust a file a previous article made, press it and see what the formulas do.

Three habits that save you from redoing work

Never hardcode a number. If a figure came from somewhere, keep the formula. The day the client sends revised data, a file full of typed numbers must be rebuilt from scratch.

Keep your workings in the file. A separate sheet showing how you got from the trial balance to the audit file figure. When the manager asks three weeks later, you will not remember, but the sheet will.

Leave a trail somebody else can follow. Label your columns, colour the cells you changed, note the source of each file. A brilliant formula nobody can understand is worse than a simple one they can.

And what to skip for now: VBA and macros, clever array formulas, dashboard building. None of it comes up in your first year, and the time is better spent getting fast at the list above. Power Query is worth a look later, since it automates exactly that cleanup.

Want to become an Excel expert and gain practical internal audit skills?

Excel is an essential tool for every CA student and finance professional. Take your skills to the next level with our Excel Champ with Master Blaster of Internal Audit course, combining Excel training from basic to advanced levels with practical internal audit knowledge.

Learn Excel practically, strengthen your audit skills and become more industry-ready.

Related Searches

What is excel and excel basic features? | Excel Tips for CA Students | Essential Formulas, Shortcuts & Tools for Articleship

FAQs

Should I learn VLOOKUP or XLOOKUP?

Both, in that order. XLOOKUP is better, but many firms and clients still run older versions, so VLOOKUP is the one that always works.

Is Excel enough, or should I learn Power BI?

Excel is enough for articleship. Power BI is useful later, mostly if you move into industry or analytics. Getting fast in Excel first is the better investment.

Do CA firms test Excel in interviews?

Many do, and the test is usually practical. Expect a messy file and a reconciliation, not a quiz on function names.

How long does it take to get good?

Two or three weeks of practice on real client-style data covers the list above. Speed comes from doing it daily in office, not from more tutorials.

CA Tushar Makkar
Author - Auditing in real life | Consulting in India, US, Europe and Middle East | Content creator | Ex-PwC | CA AIR 47 Nov' 17 | YouTuber 40k+ | Expertise in manage accounts and Audit

Bridging Textbooks & Corporate Worlds |Exclusive Special offer | ENROLL NOW