Essential Excel Formulas Every Finance Pro Needs

After 24 years of training in Singapore, I can tell you the truth about Excel formulas: finance professionals use about twelve of them every working day. The rest is decoration. SUM and SUMIFS for totals. IF for decision logic. XLOOKUP for matching. INDEX+MATCH for two-way lookup. ROUND for clean reporting. PMT, IRR and NPV for valuation. A handful of date and percentage helpers. That is the working set. Anyone can learn how to use Excel formulas in a week. The skill is knowing which one to reach for on Monday morning when your boss wants the variance report before lunch.

This guide walks you through each formula with a finance scenario you will actually meet, in roughly the order you will meet them — the five to memorise first, then the families: aggregation, decision logic, lookups, rounding, the three finance functions interviewers love, dates, percentages, and the SUBTOTAL formula that most courses skip. If you want the structured classroom path with WSQ funding for Singapore-based learners, our WSQ-funded Excel training course covers every formula below in the first three sessions.

The five formulas to memorise first (if your week starts tomorrow)

Start your weekend with these five. Nothing else. Together they cover roughly 70% of what an FP&A analyst, an auditor, or a junior corporate-finance hire will do in their first month.

Rank	Formula	What it does	When you reach for it
1	=SUM(range)	Adds a range of cells	Totalling a column of revenue, costs, headcount
2	=IF(test, value_if_true, value_if_false)	Returns one value if a condition is true, another if false	Flagging budget variances, bucketing customers
3	=XLOOKUP(lookup, lookup_array, return_array)	Finds a value in one list and returns the matching item from another	Pulling a customer name from a transaction ID
4	=SUMIFS(sum_range, criteria_range, criteria, ...)	Adds values that meet multiple conditions	“Total revenue for region = APAC AND product = X”
5	=ROUND(value, digits)	Rounds a number to a set number of decimals	Every report that goes to the board

Memorise the order. SUM gives you totals. IF gives you logic. XLOOKUP gives you matching across sheets. SUMIFS combines the first two. ROUND keeps the output presentable. Everything else in this article is a variation, an extension, or a specialised tool you reach for in a specific moment. Once these five are in your finger tips, the rest comes quickly.

SUM, SUMIF, SUMIFS — the foundation

=SUM(A2:A100) adds every numeric value in the range. That is the easy one. Start there.

=SUMIF(criteria_range, criteria, sum_range) adds only the values that meet one condition. Example — total revenue for the APAC region:

=SUMIF(B2:B100, "APAC", D2:D100)

B2:B100 is the region column. “APAC” is the filter. D2:D100 is the revenue column.

=SUMIFS(sum_range, criteria_range_1, criteria_1, criteria_range_2, criteria_2, ...) extends this to many conditions. Total revenue for region = APAC AND product = “Premium”:

=SUMIFS(D2:D100, B2:B100, "APAC", C2:C100, "Premium")

Note the order. SUMIFS asks for the sum range first. SUMIF asks for it last. That inversion trips up almost every new analyst — I’ve been teaching this for 24 years and I still see it. The fix is to type the sum range first whenever you see the “S” on the end of SUMIFS. For a deeper walkthrough of conditional sums with worked examples, see our companion guide on how to use SUMIF and SUMIFS in Excel.

Finance scenario. An audit team needs revenue totals broken down by product line, region and quarter. SUMIFS handles all three filters in one cell. Get it working for one combination, drag the formula across a small grid, and you have a full revenue cube without touching a pivot table. Your manager will be delighted.

Common gotcha. The criteria argument is a text string in quotes for exact matches, but uses operators in quotes for comparisons: ">100", "<>0", ">="&G2. The ampersand joins a cell reference into the criteria string. If your formula returns zero when you expect a number, check the quotes first.

AVERAGE and AVERAGEIFS — when totals lie, averages tell the truth

=AVERAGE(D2:D100) returns the arithmetic mean.

=AVERAGEIFS(average_range, criteria_range_1, criteria_1, ...) averages only the rows that meet your conditions. Average transaction value for APAC, premium product:

=AVERAGEIFS(D2:D100, B2:B100, "APAC", C2:C100, "Premium")

Finance scenario. A monthly board pack reports total revenue, which is up 4%. Good news — until you notice the customer count is up 12%. The average revenue per customer is actually down. Totals can hide a deteriorating mix. AVERAGE and AVERAGEIFS surface that quickly and let you trend it month over month. Managers can be quite demanding. They want totals, averages and the comparison, and they want it before the 9 am meeting. With these two formulas you can give them all three.

Common gotcha. AVERAGE ignores blank cells but counts zeros. If your data uses 0 to mean “no sale” rather than leaving the cell blank, the average will be pulled down. Choose your zeros deliberately.

COUNT, COUNTA, COUNTIF — counting what matters

Three functions. Three different things.

• =COUNT(range) counts only numeric values.
• =COUNTA(range) counts non-empty cells — numbers, text, dates, anything not blank.
• =COUNTIF(range, criteria) counts cells that meet a condition.

The COUNT vs COUNTA distinction trips people up every time. Counting employee IDs (which may be text strings like “E0042”)? Use COUNTA. Counting invoice amounts (numbers)? Use COUNT.

=COUNTIF(range, criteria) example — count customers with revenue above $10,000:

=COUNTIF(D2:D100, ">10000")

Finance scenario. HR sends a payroll file. You need the headcount that received a bonus this quarter. Bonuses live in column F as a number; non-bonus rows are blank. =COUNT(F2:F500) returns the headcount. =COUNTA(F2:F500) would also work if there are no stray text values in that column. For payroll-grade Excel work — variable pay, salary bands, leave accruals — the Excel for HR professionals course goes deeper.

IF, nested IFs, and IFS — decision logic in your sheet

=IF(condition, value_if_true, value_if_false) is the workhorse:

=IF(D2 > 10000, "Large", "Small")

For three or more conditions, you nest IFs:

=IF(D2 > 100000, "Strategic", IF(D2 > 10000, "Large", IF(D2 > 1000, "Mid", "Small")))

That works but is hard to read. Modern Excel offers =IFS(), which is much cleaner:

=IFS(D2 > 100000, "Strategic", D2 > 10000, "Large", D2 > 1000, "Mid", TRUE, "Small")

The trailing TRUE, "Small" is the catch-all for anything that didn’t match. Without it, unmatched rows return #N/A, which then breaks every total downstream.

Finance scenario. A credit-control team buckets receivables by aging — current, 30 days, 60 days, 90+ days. IFS turns an aging report into a clean four-bucket column you can summarise with COUNTIFS or pivot. The boss can see “how many invoices in 60–90 days?” at a glance, without filtering.

Common gotcha. Order matters in IFS. Excel evaluates left to right and stops at the first match. If you put “60+ days” before “current” you will mis-bucket every row that qualifies for both. Read your conditions top to bottom; if any earlier line could swallow a later one, you have a bug.

VLOOKUP, XLOOKUP and the lookup family

VLOOKUP searches the first column of a table for your lookup value, then returns the matching entry from a column further right:

=VLOOKUP(lookup_value, table_array, column_index_number, [exact_match])

XLOOKUP, available in Excel 2021 and Microsoft 365, replaces VLOOKUP with a cleaner signature. The arguments, in order, are: the value to look up, the array to search, the array to return from, an optional fallback for if-not-found, an optional match mode, and an optional search mode.

=XLOOKUP(needle, search_array, result_array, [fallback], [match_mode], [search_mode])

Think of XLOOKUP as a dictionary. You give it a word (the lookup value), point at the dictionary’s word column (the lookup array) and its definition column (the return array), and it gives you the definition. Simple. No counting columns the way VLOOKUP makes you.

My opinion is straightforward: use XLOOKUP whenever your audience is on a recent Excel version. Three reasons.

1. XLOOKUP can return a value from a column to the left of the lookup column. VLOOKUP cannot — a limitation that has cost finance analysts millions of restructured tables.
2. XLOOKUP takes an explicit “if not found” argument. VLOOKUP returns #N/A, which you then have to wrap in IFERROR. Extra noise in every cell.
3. XLOOKUP defaults to exact match. VLOOKUP defaults to approximate match — a default that has corrupted more financial models than any other Excel quirk.

The one reason to still know VLOOKUP: legacy models, files shared with users on older Excel versions, and Singapore finance interviews where the interviewer wants to see you can do both. Be honest in the interview — say you reach for XLOOKUP first and only fall back to VLOOKUP when the file requires it. For a side-by-side workout on both functions, our walkthrough on how to use VLOOKUP and XLOOKUP in Excel goes deeper. Microsoft’s own XLOOKUP reference is also worth a bookmark.

Finance scenario. You receive a list of 5,000 transaction IDs and need to pull customer name, region and product for each. One XLOOKUP per column, dragged down. Done in three minutes.

INDEX + MATCH — the combo every analyst graduates to

INDEX returns the cell at a given row and column position in a range:

=INDEX(array, row_num, [column_num])

MATCH returns the position of a value in a one-dimensional list:

=MATCH(lookup_value, lookup_array, [match_type])

Combine them and you have a two-way lookup that does what XLOOKUP does, plus a few things XLOOKUP cannot:

=INDEX(D2:F100, MATCH(G2, A2:A100, 0), MATCH(H2, D1:F1, 0))

This finds the row where column A matches G2, the column where row 1 matches H2, and returns the cell at the intersection. A finance model with revenue by product (rows) and region (columns) is read this way.

Why bother if XLOOKUP exists? Two reasons. First, INDEX+MATCH works in every version of Excel back to the 1990s — useful when sharing files with auditors who still run Excel 2010. Second, INDEX+MATCH is the cleanest way to handle two-way lookups when both the row and the column key are variables. XLOOKUP can be nested to do this but reads less clearly.

Finance scenario. Pulling a single cell from a 12-month-by-20-product budget grid based on a user-selected month and product. INDEX+MATCH+MATCH does it in one cell, and it updates the moment the user changes either dropdown. For deeper modelling work that uses this pattern heavily, look at advanced data analytics with Excel.

ROUND, ROUNDUP, ROUNDDOWN — finance can’t afford rounding errors

Three formulas, three behaviours:

• =ROUND(number, digits) rounds normally (5 and above rounds up).
• =ROUNDUP(number, digits) always rounds up, regardless of the decimal.
• =ROUNDDOWN(number, digits) always rounds down.

=ROUND(123.456, 2)    → 123.46
=ROUNDUP(123.451, 2)  → 123.46
=ROUNDDOWN(123.459, 2) → 123.45

Negative digits rounds to the left of the decimal. =ROUND(1234, -2) returns 1200.

Finance scenario. A reconciliation comes in $0.01 short. After 24 years of teaching Excel I see this pattern weekly. The cause is almost always a chain of un-rounded percentage calculations that compound their fractional residue across rows. ROUND every intermediate calculation that will later be totalled, not just the final display value. Of course your auditors will be unhappy. The cell formatting that “shows” two decimal places does not change the underlying value — only ROUND does.

Common gotcha. Number formatting (right-click → Format Cells → Number → 2 decimal places) is a display choice. ROUND is a value choice. Mixing them produces totals that don’t tie. Always ROUND the value if downstream calculations depend on it.

PMT, IRR, NPV — the three finance functions you’ll be asked about in any interview

These three appear in Singapore finance interviews more often than any other Excel functions. Learn them in this order.

PMT — loan and mortgage payments

=PMT(rate_per_period, number_of_periods, present_value)

Monthly payment on a $500,000 loan, 30-year term, 4.5% annual rate:

=PMT(4.5%/12, 30*12, -500000)

Returns approximately $2,533.43. The negative sign on the present value reflects Excel’s cash-flow convention: money out is negative, money in is positive. Get this wrong and PMT returns a negative payment, which looks correct but is the wrong sign for the rest of your model.

IRR — internal rate of return on a series of cash flows

=IRR(values, [guess])

A column of cash flows starting with a negative outlay and followed by positive returns. IRR returns the discount rate at which net present value is zero. For irregular cash-flow dates, switch to =XIRR(values, dates) — this is the version a senior interviewer will expect.

NPV — net present value

=NPV(discount_rate, value1, [value2], ...)

NPV assumes cash flows occur at the end of each period. If your first cash flow is today (period zero, not period one), add it outside the function:

=NPV(10%, B2:B11) + B1

For irregular dates, use =XNPV(rate, values, dates) — the precise version that takes a date column rather than assuming equal periods. In practice, XNPV is almost always what you want; the regular NPV gives you a slightly wrong answer in any real project where cash flows arrive on calendar dates rather than fixed period-ends.

Finance scenario. An investment committee asks for the IRR of a project with cash flows of -$1,000,000 today, $200,000 a year for the next six years, and a $500,000 terminal value in year seven. Type the cash flows into a column, wrap them in IRR, and you have your answer in 30 seconds. How good is that? For modelling work that goes beyond these basics — sensitivity tables, scenario switches, multi-sheet models — Power Query and Power Pivot for deeper analysis is the next step.

Date math: TODAY, EOMONTH, DATEDIF — aging reports and accruals

Three date functions that solve roughly 80% of finance date problems:

• =TODAY() returns today’s date and updates whenever the file opens. Useful as the anchor for any aging calculation.
• =EOMONTH(start_date, months) returns the last day of the month a specified number of months from the start date. =EOMONTH(TODAY(), 0) is end of this month; =EOMONTH(TODAY(), 3) is end of the month three months from now.
• =DATEDIF(start, end, unit) returns the difference between two dates, where unit is “y” (years), “m” (months), or “d” (days).

Finance scenario. An AR aging report needs to bucket receivables by days outstanding from today.

Days outstanding: =TODAY() - invoice_date
Aging bucket: =IFS([days]<=30,"Current",[days]<=60,"30-60",[days]<=90,"60-90",TRUE,"90+")

This pair gives you a one-formula aging column that updates every time the file opens. The accountancy team can sort, filter and total without touching it again. The collections manager opens the file on Monday morning and sees a fresh aging — no rebuild needed.

Common gotcha. DATEDIF is technically undocumented in modern Excel (Microsoft kept it for backwards compatibility but stopped advertising it). It still works. Use it. If your IT team locks it down, the workaround for "months between" is =(YEAR(end)-YEAR(start))*12 + MONTH(end) - MONTH(start). Singapore-based finance teams looking at WSQ-funded upskilling can check SkillsFuture Singapore for current course-fee subsidies.

Percentage formulas — variance, growth, margin

Three percentage formulas cover most monthly board-pack needs.

Variance (actual vs budget):

=(actual - budget) / budget

Returns a decimal. Format as percentage. A positive number means actual exceeded budget.

Growth (period-over-period):

=(current_period - prior_period) / prior_period

Margin:

=(revenue - cost) / revenue

Gross margin if cost is COGS, operating margin if cost is operating expenses.

Format the result cell as Percentage (Ctrl+Shift+%) — do not multiply by 100 inside the formula. Do both and you get 0.42% when you meant 42%. Then your boss asks why margins fell off a cliff and you have a small problem.

Finance scenario. A board pack needs revenue variance vs budget, monthly growth vs prior month and gross margin for ten product lines. Three formulas, dragged down ten rows. The board sees a clean table rather than a chart that obscures the underlying numbers.

Common gotcha. Dividing by the wrong base. For year-on-year growth, the denominator is the prior period, not the current. For variance, the denominator is the budget, not the actual. Pick the convention your finance function uses and document it once in the model. Better to be explicit than to have a quiet argument with the controller in November.

SUBTOTAL — the formula that ignores filtered rows

=SUBTOTAL(function_num, range) performs an aggregation on a range, optionally ignoring hidden or filtered rows.

The function_num is a code:

• 1 = AVERAGE
• 2 = COUNT
• 3 = COUNTA
• 4 = MAX
• 5 = MIN
• 9 = SUM (the most common)
• 109 = SUM ignoring rows hidden manually as well as by filter

For most audit work, you want code 9 for SUM. The 100-series codes also exclude rows hidden by Hide Row (not just by Filter).

=SUBTOTAL(9, D2:D100)

When you apply a filter to the range D2:D100, the SUBTOTAL recalculates on only the visible rows. SUM does not — it adds all rows whether visible or hidden.

Finance scenario. An auditor filters a transaction list to show only the FY24 entries. The total at the top of the page needs to reflect only those visible entries. SUBTOTAL handles this; SUM would still show the all-years total and produce a wrong tickmark in the working papers. Of course the audit reviewer will be unhappy. A small change of one formula avoids the entire conversation. If your team relies on filters heavily, our step-by-step guide on how to filter data in Excel pairs neatly with SUBTOTAL.

Writing equations the right way

Three rules that prevent more errors than any other Excel habit. After 24 years of teaching, these are the three I make every learner repeat back.

1. Start with an equals sign. Every formula in Excel begins with =. Type SUM(A1:A10) without the equals sign and Excel treats it as literal text. New users hit this on day one. Then never again.

2. Use the right reference type. Excel has three: relative (A1), absolute ($A$1), and mixed ($A1 or A$1). The dollar sign is like superglue — wherever it sits in the reference, that part stays stuck when you copy the formula. When you copy a formula:

• Relative refs shift with the cell. Copying =A1 from B1 to B2 produces =A2.
• Absolute refs do not shift. Copying =$A$1 from B1 to B2 still references A1. The two dollar signs glue both row and column.
• Mixed refs lock only the row or only the column. =$A1 keeps the column fixed but lets the row shift; =A$1 is the reverse.

Press F4 with the cursor inside a reference to cycle through the four variants. Do try it out — once your fingers know the F4 shortcut, the dollar-sign mental model takes care of itself.

3. Respect order of operations. Excel follows standard math: parentheses, exponents, multiplication and division, then addition and subtraction. When you are not sure, add parentheses. =A1+A2*A3 is not the same as =(A1+A2)*A3. Two extra brackets are cheaper than a wrong board pack.

FAQ

What are the 5 most useful Excel formulas for finance professionals?

The five most useful formulas, in order, are SUM (totals), IF (decision logic), XLOOKUP (matching across sheets), SUMIFS (totals with multiple conditions) and ROUND (clean reporting). Together they cover roughly 70% of the daily formula work in FP&A, audit and corporate finance roles. Learn them in this order — each one is a building block for what comes after. Anything fancier is a variation of these five.

How do I use multiple IF statements in Excel?

You can either nest IF statements inside each other or use the cleaner IFS function (available in Excel 2019 and later). For a three-bucket classification: =IFS(D2>100000, "Large", D2>10000, "Mid", TRUE, "Small"). The trailing TRUE is the catch-all that handles any value not matched by earlier conditions — without it, unmatched rows return #N/A. Order matters: Excel evaluates conditions left to right and returns on the first match. If you put a wider bucket before a narrower one, the wider bucket will swallow rows that should have gone to the narrower.

What's the difference between VLOOKUP and XLOOKUP, and which should I use?

XLOOKUP is the better choice, in three ways. First, XLOOKUP can return values from a column to the left of the lookup column; VLOOKUP cannot. Second, XLOOKUP defaults to exact match; VLOOKUP defaults to approximate match — a default that has corrupted thousands of financial models. Third, XLOOKUP takes an if_not_found argument directly, so you don't need to wrap it in IFERROR. Use XLOOKUP if your team is on Excel 2021 or Microsoft 365. Use VLOOKUP only when sharing files with users on older versions.

How do I calculate IRR in Excel for a project with irregular cash flows?

Use =XIRR(values, dates) rather than =IRR(values). IRR assumes cash flows happen at equal intervals (annual by default). XIRR takes an explicit date column, so it handles real-world cash flows that arrive on calendar dates: an initial investment in January, a return in April, another in November, and so on. Put the cash flows in one column (initial outlay as negative), the corresponding dates in the next column, and wrap both columns in XIRR. The function returns the annualised internal rate of return — the number your investment committee actually wants to see.

Why does SUM give a different answer than SUBTOTAL on a filtered list?

SUM adds every value in the range, regardless of whether rows are filtered or hidden. SUBTOTAL, with function code 9, adds only the values in visible (unfiltered) rows. This is the difference auditors care about: when they filter a transaction list to the relevant year or vendor, the running total at the top of the page should reflect only the visible rows. Use =SUBTOTAL(9, range) in any working paper that will be filtered. Use SUM when you want the unconditional total of the whole range. Both have their place; the trick is knowing which one your reviewer expects.

How do I write a percentage formula without getting the calculation backwards?

The convention is (new - old) / old, or more generally, (numerator - denominator) / denominator. For variance vs budget: (actual - budget) / budget. For period-over-period growth: (current - prior) / prior. The denominator is whichever value you are comparing against. Format the result cell as Percentage (Ctrl+Shift+%) — do not multiply by 100 inside the formula itself, or the percentage formatting will multiply it again and you will end up with 0.42% when you meant 42%.

---

I hope you'll find this useful on your next Monday morning. Pick the five formulas, give them a try in a real workbook this week, and watch how much faster your variance reports come together. If you want a structured path through these and another fifty practical formulas, with WSQ-funded options for Singapore-based finance teams, our Excel Training in Singapore is where most of our 48,000+ trained professionals start.