Excel remains the working language of finance. The difference between someone who "uses Excel" and someone who closes the books two days faster is usually a short list of formulas — used well, combined in twos and threes, and named clearly. Here are the twenty-five formulas we consider non-negotiable for anyone working in accounting, audit, tax or FP&A, each with a real finance use case and a copy-ready example.
Key takeaways
- Modern spill functions (
XLOOKUP,FILTER,UNIQUE,SORT) remove entire classes of drag-fill errors — prefer them when your Excel version supports them. - Formulas rarely work alone. Learn them in pairs:
XLOOKUP + IFERROR,SUMIFS + EOMONTH,FILTER + UNIQUE,LET + LAMBDA. - Use named ranges and
LETto make formulas readable — a finance sheet should be auditable like a working paper. - Availability matters:
XLOOKUP,FILTER,LET,LAMBDArequire Microsoft 365 or Excel 2021+. On older Excel, fall back toINDEX/MATCHand array formulas.
The 25 formulas — by category
Categorised for the FY 2082/83 finance workflow. Modern dynamic-array functions now hold the largest share.
The twenty-five formulas — with a real finance use case for each
| Formula — what it does | Finance use case and example |
|---|---|
| 1. SUM / SUMIFS Total a range; SUMIFS totals with multiple conditions. | Month-wise expense totals by department and cost centre from a ledger dump.=SUMIFS(Amount, Dept, "Finance", Month, "Shrawan") |
| 2. COUNTIFS Count rows meeting several criteria. | Count overdue invoices per client for a receivables ageing note.=COUNTIFS(Client, A2, DaysOverdue, ">30") |
| 3. AVERAGEIFS Conditional average. | Average processing days for approved-only claims.=AVERAGEIFS(Days, Status, "Approved") |
| 4. XLOOKUP Modern lookup — any direction, built-in "if not found". | Pull a vendor's PAN or bank account from a master list into the payment sheet.=XLOOKUP(A2, Vendors[Name], Vendors[PAN], "Not found") |
| 5. VLOOKUP Classic vertical lookup (still everywhere). | Map ledger codes to account names in legacy templates you inherited.=VLOOKUP(A2, CodeMap, 2, FALSE) |
| 6. INDEX + MATCH Flexible lookup that survives column inserts. | Two-way lookup: rate for a given grade (row) and year (column).=INDEX(Rates, MATCH(Grade,GradeCol,0), MATCH(Year,YearRow,0)) |
| 7. IF / IFS Branching logic. | Flag variances: within budget / review / escalate.=IFS(V<5%,"OK", V<10%,"Review", TRUE,"Escalate") |
| 8. IFERROR Trap errors with a clean fallback. | Wrap lookups so missing codes show 'Not mapped' instead of #N/A.=IFERROR(VLOOKUP(...), "Not mapped") |
| 9. SUMPRODUCT Multiply-then-sum arrays; conditional math on older Excel. | Weighted average interest rate across a loan portfolio.=SUMPRODUCT(Balance,Rate)/SUM(Balance) |
| 10. EOMONTH / EDATE Month-end and month-shift dates. | Auto-compute VAT deposit deadlines (25 days after month end) and EMI schedules.=EOMONTH(A2,0)+25 |
| 11. TEXT Format numbers/dates as text. | Build invoice references like "INV-2082-07" from dates.="INV-"&TEXT(A2,"yyyy-mm") |
| 12. ROUND / ROUNDDOWN / MROUND Control decimals explicitly. | Round TDS to the rupee so the ledger reconciles with the bank payment.=ROUND(Gross*1.5%, 0) |
| 13. PMT Loan instalment for a rate/term. | Test EMI options before signing a term loan.=PMT(Rate/12, Months, -Principal) |
| 14. NPV / IRR Discounted value and return of periodic cash flows. | Appraise a capex proposal or compare two investment options.=NPV(10%, CashFlows) + InitialInvestment |
| 15. XNPV / XIRR NPV/IRR with actual dates instead of even periods. | Real return on irregular investor drawdowns and distributions.=XIRR(CashFlows, Dates) |
| 16. FILTER Spill rows matching criteria (365 / Excel 2021+). | Live extract of unpaid invoices that updates as data changes.=FILTER(Invoices, Invoices[Status]="Unpaid") |
| 17. UNIQUE Distinct values. | De-duplicate a customer list before audit circularisation.=UNIQUE(A2:A5000) |
| 18. SORT / SORTBY Ordered spill. | Automatic ranked list of top-10 debtors by outstanding.=SORT(FILTER(...), 2, -1) |
| 19. TEXTJOIN Concatenate with a delimiter. | Combine address fields for postal labels or e-billing uploads.=TEXTJOIN(", ", TRUE, A2:D2) |
| 20. TRIM / CLEAN Strip stray spaces and non-printable characters. | Fix system exports where 'ABC ' silently fails to match 'ABC'.=TRIM(CLEAN(A2)) |
| 21. LET Name intermediate steps inside a single formula. | Make a slab-based tax formula readable.=LET(t, TaxableIncome, IF(t<=500000, t*1%, ...)) |
| 22. LAMBDA Define reusable custom functions in the workbook. | One reusable SLABTAX() function for salary TDS across sheets. |
| 23. TEXTSPLIT / TEXTBEFORE / TEXTAFTER Modern text parsing (365). | Split "Sharma, Ram | Kathmandu" into columns without helper columns.=TEXTSPLIT(A2, ",", "|") |
24. LOOKUP with wildcardsXLOOKUP and MATCH support * ? wildcards. | Match "PVT LTD" companies where names vary slightly across systems.=XLOOKUP("*"&A2&"*", List, List, "", 2) |
| 25. PIVOTBY / GROUPBY Formula-driven pivot tables (M365, 2024+). | Live department × month expense pivot that refreshes automatically.=PIVOTBY(Dept, Month, Amount, SUM) |
How to actually learn them
- Adopt in pairs. Most real tasks combine two formulas —
XLOOKUP + IFERROR,SUMIFS + EOMONTH,FILTER + UNIQUE,LET + LAMBDA. Learn the pairs; don't drill single functions. - Rebuild one real report. Take last month's manually-built statement and reconstruct it with formulas only — no hard-coded values, no copy-paste. You will find every weak spot in your working knowledge in half a day.
- Prefer spill functions where available.
FILTER,UNIQUE,SORT,SEQUENCE,PIVOTBYremove the "did I drag the formula down far enough?" class of errors entirely. - Name your ranges.
=SUMIFS(Amount, Month, ReportMonth)is auditable at a glance;=SUMIFS(C:C, A:A, F1)is not. - Use
LETaggressively. It is the single biggest readability win of the last five years — nestingIFs insideIFs is an anti-pattern in 2026. - Learn Power Query for repeatable data-cleaning tasks. Once a workflow appears twice, it belongs in Power Query, not in a formula chain.
A finance sheet should be readable by the next person like a working paper — formulas are your audit trail.
What about AI in Excel?
Microsoft 365 Copilot inside Excel can now generate PIVOTBY, FILTER and even LAMBDA formulas from a natural-language prompt over your data. Treat it the same way you would a fluent but junior colleague — excellent starting point, always reviewed by you before it hits a filed return. The tools we surveyed in our overview of AI tools apply here too: match the tool to the task, don't paste confidential data into free tiers, and verify anything that will be signed.
Getting your team's templates rebuilt
Most firms are running a monthly close on 2015-vintage nested-IF templates. Rebuilding one recurring report into a clean, spill-driven, named-range version usually saves half a day every month, forever.