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 LET to make formulas readable — a finance sheet should be auditable like a working paper.
  • Availability matters: XLOOKUP, FILTER, LET, LAMBDA require Microsoft 365 or Excel 2021+. On older Excel, fall back to INDEX/MATCH and array formulas.

The 25 formulas — by category

Lookup 5 Aggregation 4 Logic / error 2 Date / text 5 Financial 3 Dynamic array (365) 6

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 doesFinance 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 wildcards
XLOOKUP 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

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.