Excel Formulas for Finance15 Best to Master in 2026

Forget AI for a minute. Before you ask a chatbot to “analyse this ledger”, it is worth asking a more basic question: could you do it yourself in Excel in two minutes? Because the people who get the most out of AI tools are exactly the ones who already know what a PivotTable is, what SUMIFS does, and why VLOOKUP just returned the wrong number. The Excel formulas for finance in this guide are the foundation. Learn them first, and every AI tool you layer on top gets more useful, because you can finally check its work.

This is a hands-on guide written for accountants, analysts and finance teams, not for programmers. Every formula below comes with what it does, the exact syntax, an example you can copy, and the mistake that catches people out. There are roughly 15 core Excel formulas for finance here, grouped so you can find what you need fast, plus the handful of bonus tools that separate a tidy workbook from a messy one.

The honest case for basics first. AI assistants like Claude for Excel are genuinely useful, and we are big fans of using AI in finance. But AI amplifies skill, it does not replace it. If you cannot read a SUMIFS, you cannot tell when the AI quietly summed the wrong column. AI hallucinates. It will sometimes hand you a total that looks right and simply is not, stated with total confidence, and the basics are how you catch it before it reaches a report. Master these, then let the machine do the heavy lifting.

Excel formulas for finance, at a glance

Here is the cheat sheet. Skim it, then jump to any section below for the detailed walkthrough. Functions marked “365 or 2021+” are not available in Excel 2019 or 2016. For the full reference, Microsoft keeps an authoritative list of Excel functions by category.

Start here: the five simplest formulas

Before the powerful stuff, make sure these five are second nature. They are the foundation everything else builds on, and they are genuinely easy. Every formula begins with an equals sign.

• SUM adds a range of numbers: =SUM(B2:B50). Faster still, select the column and press Alt+= for an instant total.
• AVERAGE gives the mean: =AVERAGE(B2:B50).
• COUNT counts numbers and COUNTA counts non-empty cells: =COUNTA(A2:A50) tells you how many invoices you have.
• MAX and MIN return the largest and smallest values: =MAX(B2:B50).
• Plain arithmetic works too: =B2*0.18 for tax, or =B2-C2 for a difference.

PivotTables: summarise anything in 60 seconds

If you learn one thing from these Excel formulas for finance, learn PivotTables. A PivotTable turns a 10,000-row transaction export into a clean summary, total expense by vendor, sales by month, input tax credit by GSTIN, without a single formula. It is the fastest payback in all of Excel.

How to build one

Start by turning your data into a proper Table (select the range and press Ctrl+T). A Table source means new rows are picked up automatically when you refresh. Then:

1. Click any cell inside your data, then Insert > PivotTable, and choose New Worksheet.
2. In the PivotTable Fields pane, drag a text field such as Vendor into Rows.
3. Drag a date field such as Date into Columns.
4. Drag your Amount field into Values. Drag Department into Filters for a report-level filter.

Group dates by month, quarter or year

Right-click any date inside the pivot, choose Group, and select Months and Years together. If you select only Months, January 2025 and January 2026 collapse into a single “Jan”, a classic period-mixing error.

Watch out: a PivotTable does not update on its own. Right-click and Refresh after the source changes. If your source is a fixed range rather than a Table, new rows are ignored until you change the data source. Also, a Values field defaults to Count instead of Sum if the column contains any stray text or blanks, which silently understates your totals. Microsoft has a clear walkthrough on how to create a PivotTable to analyse worksheet data.

PivotTable or SUMIFS? Use a PivotTable to explore and slice data fast when the questions keep changing. Use SUMIFS when you need a fixed, formula-driven report that updates live in the same layout every month. Most finance teams use both: a PivotTable to investigate, then SUMIFS to lock down the monthly pack.

SUMIFS, COUNTIFS and the criteria that power them

SUMIFS is the workhorse among Excel formulas for finance. It adds up the numbers that meet your conditions: spend for one vendor, sales in one month, tax on one rate. Once you can write criteria well, SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS all work the same way.

SUMIFS (and the SUMIF trap)

The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The numbers you actually add up come first. You can add up to 127 condition pairs, and they all combine with AND logic.

=SUMIFS(Amount, Vendor, "Acme", Date, ">="&E1, Date, "<="&E2)

That totals the Amount column where the vendor is Acme and the date falls between the two cells E1 and E2.

The number-one Excel mistake: SUMIFS puts the sum range first, but the older SUMIF(range, criteria, [sum_range]) puts it last. Mix them up and Excel either errors or silently sums the wrong column. When in doubt, use SUMIFS for everything. Microsoft documents the full SUMIFS function argument order.

Writing criteria (the part nobody explains)

Criteria are more flexible than they look:

• Operators go inside quotes: ">1000", "<>0", "<=500".
• To compare against a cell, keep the operator quoted and join the cell with &: ">="&E1.
• Wildcards work in text: ? is one character, * is any number, so "INV*" matches every invoice reference that starts with INV.
• Plain numbers need no quotes; text and dates do.

COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS

COUNTIFS counts rows that meet conditions and has no sum range at all: =COUNTIFS(Status, "Pending", Amount, ">=10000") counts large invoices awaiting approval. MAXIFS and MINIFS return the biggest or smallest value that meets conditions, handy for finding the largest open balance per customer. For something the *IFS functions cannot do, such as OR logic or weighted sums, SUMPRODUCT is the power tool: =SUMPRODUCT((Rate="GST18")*Amount).

XLOOKUP, VLOOKUP and INDEX MATCH: find the right number

Lookups pull a value from a master table: a price by item code, a GST rate by HSN code, a vendor name by account number. This is where most finance teams either save hours or quietly corrupt a report, so it pays to understand it properly.

Where VLOOKUP fails, and how XLOOKUP fixes it

VLOOKUP has been the default for twenty years and it works, but it has four design flaws that catch finance users out constantly. XLOOKUP, available in Microsoft 365, Excel 2021 and 2024, was built to remove all four. Here is the honest side by side.

The three traps in action

1. The left-lookup wall. Say your sheet has the vendor name in column C and the account code in column A, and you have the name but want the code. VLOOKUP cannot look left, full stop. XLOOKUP does not care about column order:

=XLOOKUP("Acme Ltd", Names, Codes)

2. The silent approximate-match error. This is the one that corrupts reports. If you write =VLOOKUP(A2, RateTable, 3) and forget the FALSE, VLOOKUP does an approximate match, grabs the closest row, and returns a number that looks reasonable but is wrong. No error appears. Always write it in full with FALSE.

3. The column-insert trap. =VLOOKUP(A2, B:E, 3, FALSE) returns the third column, currently column D. Insert one column inside that range and the third column is now something else, so the formula keeps running but returns the wrong field. XLOOKUP points at the actual return column, so an inserted column does not break it.

XLOOKUP (the modern default)

If you have Microsoft 365, Excel 2021 or 2024, use XLOOKUP. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). It is exact-match by default, it can look in any direction, and it does not break when you insert a column.

=XLOOKUP(A2, Vendors[Code], Vendors[PaymentTerms], "Not found")

It can also hand back a whole row at once. Point the return range at several columns and the result spills into the cells alongside, so one formula returns a vendor’s region, terms and tax rate together:

=XLOOKUP(A2, Vendors[Code], Vendors[Region:GSTRate])

That fourth argument, "Not found", returns a clean message instead of an ugly #N/A when a code is missing. Microsoft documents the full XLOOKUP function.

VLOOKUP (works everywhere, with care)

VLOOKUP is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Two rules keep you safe:

• Always end with FALSE (or 0) for an exact match. If you omit it, VLOOKUP defaults to approximate match and can return a silently wrong value.
• It only looks rightwards. The lookup value must be in the leftmost column, and col_index_num counts columns inside the table, so inserting a column breaks it.

=VLOOKUP(A2, RateTable, 3, FALSE)

INDEX + MATCH (the flexible classic)

When you need to look leftwards, or want a lookup that survives column inserts, pair INDEX with MATCH. MATCH finds the position, INDEX returns the value at that position. Always pass 0 to MATCH for an exact match.

=INDEX(Master[Name], MATCH(A2, Master[Code], 0))

The headline trick is the two-way lookup: drive both the row and the column with MATCH to read any cell from a grid by its labels.

=INDEX(Grid, MATCH(RowLabel, RowHeaders, 0), MATCH(ColLabel, ColHeaders, 0))

IF, IFS and clean logic

Logic functions make decisions: flag an overdue balance, band a risk rating, classify a tax line. The trick is keeping them readable.

IF and nested IF

IF(logical_test, value_if_true, value_if_false) is the building block. You can nest them, but past two or three levels they become hard to audit. Note that if you omit the false branch entirely, Excel returns the word FALSE rather than a blank, so use "" when you want nothing shown.

IFS (cleaner bands)

IFS replaces a stack of nested IFs with a tidy list of test-and-result pairs. It needs Excel 2019 or 365. The catch: IFS has no default, so add TRUE as the final condition to catch everything else.

=IFS(B2>90, "High", B2>60, "Medium", B2>30, "Low", TRUE, "Current")

IFERROR and IFNA (no more ugly errors)

Reports that leadership sees should never show #N/A or #DIV/0!. Wrap the risky part: =IFERROR(VLOOKUP(A2, Master, 3, FALSE), "Vendor not in master"). One important distinction: IFERROR traps every error type, while IFNA traps only #N/A. Around a lookup, IFNA is often safer because a genuine #REF! bug still surfaces instead of being hidden.

IF vs IFS vs SWITCH: which to reach for

• IF for a single yes or no test with two outcomes, such as profit or loss.
• Nested IF for two or three bands, but stop there. More becomes unreadable and a single misplaced bracket breaks the whole thing.
• IFS for many thresholds checked in order, such as an aging ladder. Remember the TRUE catch-all, and that it needs Excel 2019 or 365.
• SWITCH when you map one exact value to many results, such as a tax code to a description. It only does exact matches, not greater-than logic.

CONCAT, TEXTJOIN and tidy text

Text functions build reference codes, clean imported data and format labels for reports. They are quietly some of the most useful Excel formulas for finance work.

Joining text: &, CONCAT and TEXTJOIN

The simplest join is the & operator: ="INV-" & TEXT(A2, "0000") builds INV-0042. For joining a whole range with a separator, TEXTJOIN is the modern choice (Excel 2019 or 365). Its argument order trips people up: the delimiter comes first, then the ignore-empty flag, then the cells.

=TEXTJOIN(", ", TRUE, B2:B20)

That joins a column of department names into one comma-separated cell, skipping blanks because the second argument is TRUE. The older CONCATENATE still works but cannot take a whole range; CONCAT (2019 or 365) can.

Four ways to join text, and the right one depends on your version and whether you need a separator:

• The & operator for a quick join of a few items, in any version of Excel.
• CONCATENATE is the legacy function; it cannot take a whole range, so you list each cell.
• CONCAT (2019 or 365) is the same idea but accepts a whole range in one go.
• TEXTJOIN (2019 or 365) puts a separator between every item and can skip blank cells, which none of the others do.

Formatting and cleaning: TEXT, TRIM, CLEAN

TEXT(value, "format") turns a number into a formatted string, useful for report headers: ="Total payable: " & TEXT(SUM(D2:D50), "$#,##0.00"). Remember the result is text, so it will not calculate. For messy imports, =TRIM(CLEAN(A2)) strips extra spaces and stray line breaks; if a pasted value still looks wrong, the culprit is often a non-breaking space, which you remove with SUBSTITUTE(A2, CHAR(160), " "). Pull out parts of a code with LEFT, MID and RIGHT, for example =MID(A2, 5, 6) for a six-digit document number starting at position five.

ROUND, dates and working days

Finance lives and dies on correct rounding and correct period-ends. These small functions prevent big reconciliation headaches.

ROUND (and why display formatting is not enough)

ROUND(number, num_digits) rounds to a set number of decimals, half away from zero. Always round tax with a real formula, not just cell formatting, so the booked figure matches the document: =ROUND(taxable_value*0.18, 2). Use ROUNDUP and ROUNDDOWN to force the direction, and MROUND(amount, 0.05) to round an invoice total to the nearest five cents. If you want to see correct CGST and SGST splits, our GST calculator applies exactly this kind of rounding.

Period-ends and due dates: EOMONTH, EDATE, WORKDAY

• =EOMONTH(A2, 0) returns the last day of the transaction’s month, the standard accrual cut-off.
• =EDATE(A2, 1) returns the same day one month later, useful for net-one-month payment terms.
• =WORKDAY(A2, 30, Holidays) adds 30 working days to a date, skipping weekends and a holidays range. For custom weekends, use WORKDAY.INTL and NETWORKDAYS.INTL.

SUBTOTAL: sum only what you can see

When you filter a ledger, a normal SUM still adds the hidden rows. =SUBTOTAL(109, Amount) sums only the visible, filtered rows. The code 9 includes manually hidden rows; 109 excludes them, and both ignore rows hidden by a filter. To total a column while ignoring error cells, =AGGREGATE(9, 6, Amount) does what plain SUM cannot.

Money math: PMT, NPV and IRR

These are the Excel formulas for finance that turn the spreadsheet into a genuine finance tool rather than a calculator. They handle loans, leases and investment appraisal.

PMT, IPMT and PPMT (loans and leases)

PMT(rate, nper, pv) returns the level instalment on a loan. Use the per-period rate and the number of periods, and enter the loan amount as a negative present value so the payment comes back positive.

=PMT(9%/12, 60, -500000)

That is the monthly instalment on a 500,000 loan at 9% annual over 60 months. IPMT and PPMT split any single instalment into its interest and principal parts, which is how you build an amortisation schedule. To see a full schedule with prepayments, try our advanced EMI loan calculator.

NPV, XNPV, IRR and XIRR (appraisal)

For evenly spaced cash flows, NPV(rate, values) and IRR(values) do the job, but real cash flows are rarely evenly spaced. The dated versions are what accountants actually use: =XNPV(rate, values, dates) and =XIRR(values, dates) take the actual payment dates, so a lumpy set of receipts and outflows is valued correctly.

Watch out: NPV assumes the first cash flow is one period from today, not today, so add a period-zero outflow separately or use XNPV with real dates. Mind the sign convention throughout: outflows negative, inflows positive.

Modern formulas: FILTER, SORT, UNIQUE and LET

If you have Microsoft 365, Excel 2021 or 2024, dynamic arrays change how you work. One formula returns many results that “spill” into the cells below, and they update live. None of these work in Excel 2019 or 2016.

• =FILTER(Data, (Amount>100000)*(Status="Open"), "None") returns every row that matches, no PivotTable needed. Multiply conditions for AND, add them for OR.
• =SORT(UNIQUE(GSTIN)) returns a clean, de-duplicated, sorted list of counterparties, live.
• =LET(gross, B2, rate, 10%, tds, gross*rate, gross-tds) names parts of a formula so it reads like plain English and computes each part once.

Watch out: if a dynamic array cannot spill because a cell below it is occupied, you get a #SPILL! error. Clear the cells in the way. Reference a whole spilled range with the anchor cell plus a hash, for example =COUNTA(A2#).

Make it readable and safe

Correct numbers in an ugly, error-prone sheet still lose trust. Three features do most of the work.

• Number formatting (Ctrl+1): use thousands separators and show negatives in red or brackets with a custom code such as #,##0;[Red](#,##0). This is display only; the stored value is unchanged.
• Conditional Formatting (Home > Conditional Formatting): highlight negatives, overdue receivables, duplicates or top values automatically. Data bars and colour scales turn an aging report into something readable at a glance.
• Data Validation (Data > Data Validation > List): add drop-downs for cost centres, GL codes or Yes/No fields. Clean input is what keeps your SUMIFS and lookups from breaking later.

Faster and tidier: the habits of good workbooks

A few habits make every formula above easier to write and audit:

• Excel Tables (Ctrl+T): structured references like tblAP[Amount] read clearly and auto-expand as you add rows, so your summaries never miss new data.
• Lock your references with F4: a tax-rate cell should be absolute, $B$2, so dragging a formula down does not let it drift. This is the single most common copy-down error.
• Paste Special (Ctrl+Alt+V): paste values to freeze formulas, transpose rows to columns, or multiply a whole range by minus one to flip signs.
• Select fast: Ctrl+Shift+Down selects to the end of a column, and Alt+= drops in an AutoSum.
• Keep it quick: avoid whole-column ranges and volatile functions like OFFSET and INDIRECT in big models; bind to a Table column instead.

Absolute vs relative references: what the dollar sign does

The $ sign locks part of a cell reference so it does not move when you copy the formula. This is the single biggest source of copy-down errors in finance, so it is worth getting right.

Press F4 while editing a reference to cycle through the four versions. The classic mistake: you write =D5*B2 to apply a tax rate sitting in B2, drag it down, and B2 quietly becomes B3, then B4, corrupting every line below. Lock it as =D5*$B$2 and the rate stays put.

This is not the same as Freeze Panes. Locking with $ freezes a reference inside a formula. Freeze Panes (View, then Freeze Panes) only freezes rows or columns on screen so your headings stay visible while you scroll. They sound similar but do completely different jobs.

What Excel’s error messages mean

Errors are not failures, they are Excel telling you exactly what went wrong. Working with Excel formulas for finance means meeting these regularly, so learn to read the seven you will actually see and you will fix most problems in seconds.

How to learn these Excel formulas for finance fast

You do not need a course. You need one real workbook and a week to make these Excel formulas for finance stick. Grab our free practice workbook, with made-up data, every formula worked out and a Try Yourself tab, as your starting point.

1. Start with five: PivotTables, SUMIFS, XLOOKUP, IF and ROUND cover most daily finance work.
2. Rebuild a report you already make by hand, using only formulas. Friction is where you learn.
3. Learn the gotchas, not just the syntax: the SUMIFS argument order, VLOOKUP’s FALSE, IFS needing a TRUE default. These are where time is lost.
4. Add one new formula a week. TEXTJOIN this week, FILTER next. Small steps stick.

Want a beginner version? This guide is pitched at intermediate users who already find their way around a spreadsheet. If you would like a from-scratch beginner version that starts with cell references and simple sums, tell us in the comments below and we will build one.