Most of your first year is spent in Excel: reconciling, vouching, sampling and summarising. These are the formulas you will use most, with the situation each one solves.
The formulas, with where you use them
| Formula | Example | Use it for |
|---|---|---|
| XLOOKUP | =XLOOKUP(A2,GSTR2B!B:B,GSTR2B!H:H,"Not found") | Pull the GSTR-2B tax value for each purchase invoice |
| SUMIFS | =SUMIFS(D:D,B:B,"Vendor X",C:C,">="&F1) | Total by party or period for ledger checks |
| COUNTIFS | =COUNTIFS(B:B,B2) | Find duplicate invoice numbers |
| IF / IFERROR | =IF(E2-F2=0,"Matched","Check") | Flag differences in reconciliations |
| TEXT / TRIM | =TRIM(UPPER(A2)) | Clean GSTINs and party names before matching |
| Pivot Table | Insert, then PivotTable | Rate-wise or month-wise summaries in seconds |
Keep a "Total check" cell at the top of every working. If it does not match the trial balance or the return, stop and find out why before you go further.
Matching: the usual reconciliation flow
- Clean both lists: trim spaces, same case, same date format.
- Create a key such as GSTIN plus invoice number.
- Use XLOOKUP on the key to bring in the other side's amount.
- Subtract and flag differences above a tolerance.
- Pivot the exceptions by supplier to chase.
Try the difference calculator
Is the difference material?
Compare two totals and a tolerance.
Frequently asked questions
Which Excel formula should a CA article assistant learn first?
SUMIFS and a lookup (XLOOKUP or VLOOKUP). Between them they handle most reconciliations.
XLOOKUP or VLOOKUP?
XLOOKUP is more flexible and easier to read where available. VLOOKUP works in older versions.
How do I find duplicate invoices?
Use COUNTIFS on the invoice number and filter results above 1, or use conditional formatting for duplicates.
How do I clean GSTINs before matching?
Use TRIM and UPPER, and remove stray characters so keys match exactly.
Is AI useful for Excel work?
It can help draft formulas, but verify every result against source totals.