Excel Formulas Cheat Sheet: Complete Reference (2025)

Excel formulas turn a grid of numbers into a working calculator, database, and reporting tool. This cheat sheet covers every category of function you'll actually use — with real examples, quick-reference tables, and the mistakes that waste hours of debugging.

Quick reference table

Formula syntax basics

Every Excel formula starts with =. Key syntax rules:

Reference lock shortcut: Press F4 to cycle through A1 → $A$1 → A$1 → $A1 → A1.

Logical functions

IF

=IF(logical_test, value_if_true, value_if_false)

=IF(A2>100, "High", "Low")
=IF(A2="", "Empty", A2)
=IF(AND(A2>0, B2>0), "Both positive", "Check values")

IFS (multiple conditions, no nesting)

=IFS(condition1, result1, condition2, result2, ..., TRUE, default)

=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", A2>=60,"D", TRUE,"F")

SWITCH (match a value against a list)

=SWITCH(value, match1, result1, match2, result2, ..., default)

=SWITCH(A2, 1,"Jan", 2,"Feb", 3,"Mar", "Unknown")

AND / OR / NOT

=AND(A2>0, B2>0, C2>0)          ' All must be TRUE
=OR(A2="Admin", A2="Manager")   ' Any must be TRUE
=NOT(ISBLANK(A2))               ' Reverse the logic

IFERROR / IFNA

=IFERROR(formula, value_if_error)

=IFERROR(VLOOKUP(A2,Table,2,0), "Not found")
=IFNA(XLOOKUP(A2,B:B,C:C), "Missing")   ' Only catches #N/A

Lookup functions

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
' range_lookup: FALSE = exact match, TRUE = approximate (sorted data required)

=VLOOKUP(A2, D:F, 2, FALSE)       ' Find A2 in column D, return col E
=VLOOKUP(A2, $D$2:$F$100, 3, 0)  ' 0 is shorthand for FALSE

VLOOKUP limits: looks left-to-right only; the lookup column must be the first column; breaks when you insert/delete columns.

INDEX + MATCH (more flexible than VLOOKUP)

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

=INDEX(C:C, MATCH(A2, B:B, 0))   ' Return value from C where B matches A2
=INDEX(A:A, MATCH(MAX(B:B), B:B, 0))  ' Find the name of the max value

Two-way lookup:

=INDEX(B2:D10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:D1, 0))
' Finds cell at intersection of row G1 and column G2

XLOOKUP (Excel 365 / 2021+)

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

=XLOOKUP(A2, B:B, C:C)                         ' Basic: find A2 in B, return C
=XLOOKUP(A2, B:B, C:C, "Not found")            ' Custom error message
=XLOOKUP(A2, B:B, C:C, , -1)                   ' Wildcard match
=XLOOKUP(A2, B:B, C:D)                         ' Return multiple columns at once
=XLOOKUP(A2, B:B, C:C, , , -1)                 ' Search from last (find latest)

MATCH

=MATCH(lookup_value, lookup_array, [match_type])
' Returns the position (row/column number), not the value

=MATCH("Alice", A:A, 0)     ' Position of "Alice" in column A (exact)
=MATCH(MAX(B:B), B:B, 0)    ' Position of the largest value

CHOOSE

=CHOOSE(index_num, value1, value2, ...)

=CHOOSE(WEEKDAY(TODAY()), "Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Math functions

SUM / SUMIF / SUMIFS

=SUM(A1:A100)
=SUM(A1,B1,C1)               ' Non-adjacent cells
=SUM(A1:A100, C1:C100)       ' Multiple ranges

=SUMIF(range, criteria, [sum_range])
=SUMIF(B:B, "East", C:C)     ' Sum C where B = "East"
=SUMIF(A:A, ">100", B:B)     ' Sum B where A > 100
=SUMIF(A:A, "<>"&"", B:B)    ' Sum B where A is not blank

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
=SUMIFS(C:C, B:B, "East", A:A, ">100")   ' Sum C where B="East" AND A>100
=SUMIFS(D:D, A:A, ">="&E1, A:A, "<="&F1) ' Sum D between dates in E1 and F1

SUMPRODUCT (multiply ranges, then sum — no array formula needed)

=SUMPRODUCT(array1, [array2], ...)

=SUMPRODUCT(B2:B100, C2:C100)          ' Sum of (price × quantity)
=SUMPRODUCT((A2:A100="East")*(C2:C100))  ' Conditional sum without SUMIFS
=SUMPRODUCT((A2:A100="East")*(B2:B100="Q1")*(C2:C100)) ' Multi-condition

Rounding

=ROUND(number, num_digits)    ' Round to N decimal places
=ROUND(3.14159, 2)            ' → 3.14
=ROUND(1234, -2)              ' → 1200 (round to nearest 100)

=ROUNDUP(3.001, 0)            ' → 4 (always rounds away from zero)
=ROUNDDOWN(3.999, 0)          ' → 3 (always rounds toward zero)
=MROUND(17, 5)                ' → 15 (round to nearest 5)
=CEILING(17, 5)               ' → 20 (round up to nearest 5)
=FLOOR(17, 5)                 ' → 15 (round down to nearest 5)

=INT(3.9)                     ' → 3 (floor toward negative infinity)
=TRUNC(3.9)                   ' → 3 (truncate decimal part)
=MOD(17, 5)                   ' → 2 (remainder)

Statistical / counting functions

COUNT family

=COUNT(A1:A100)        ' Count numeric cells
=COUNTA(A1:A100)       ' Count non-empty cells (any type)
=COUNTBLANK(A1:A100)   ' Count empty cells

=COUNTIF(range, criteria)
=COUNTIF(A:A, "Apple")        ' Count exact match
=COUNTIF(A:A, "A*")           ' Count starting with A (wildcard)
=COUNTIF(B:B, ">"&100)        ' Count values > 100
=COUNTIF(A:A, A2)             ' Count cells equal to A2

=COUNTIFS(range1, criteria1, range2, criteria2, ...)
=COUNTIFS(A:A, "East", B:B, ">100")  ' AND condition

Statistical functions

=AVERAGE(A1:A100)
=AVERAGEIF(A:A, ">0", B:B)       ' Average of B where A > 0
=AVERAGEIFS(C:C, A:A, "East", B:B, ">100")

=MEDIAN(A1:A100)      ' Middle value
=MODE(A1:A100)        ' Most frequent value
=MAX(A1:A100)
=MIN(A1:A100)
=LARGE(A1:A100, 3)    ' 3rd largest
=SMALL(A1:A100, 3)    ' 3rd smallest
=STDEV(A1:A100)       ' Standard deviation (sample)
=STDEVP(A1:A100)      ' Standard deviation (population)
=PERCENTILE(A1:A100, 0.9)  ' 90th percentile
=RANK(A2, A:A, 0)     ' Rank descending (0) or ascending (1)

Text functions

Extracting text

=LEFT(text, num_chars)
=LEFT("Excel 2025", 5)     ' → "Excel"

=RIGHT(text, num_chars)
=RIGHT("user@email.com", 3) ' → "com"

=MID(text, start_num, num_chars)
=MID("Hello World", 7, 5)  ' → "World"
=MID(A2, FIND("@",A2)+1, LEN(A2))  ' Extract domain from email

=LEN(text)
=LEN("Hello")              ' → 5

Cleaning text

=TRIM(A2)                  ' Remove leading/trailing spaces and extra spaces
=CLEAN(A2)                 ' Remove non-printable characters
=UPPER(A2)                 ' "HELLO"
=LOWER(A2)                 ' "hello"
=PROPER(A2)                ' "Hello World" (title case)

Searching and replacing

=FIND(find_text, within_text, [start_num])   ' Case-sensitive, returns position
=SEARCH(find_text, within_text)              ' Case-insensitive
=FIND("@", A2)             ' Position of @ in email

=SUBSTITUTE(text, old_text, new_text, [instance_num])
=SUBSTITUTE(A2, " ", "_")              ' Replace all spaces with underscores
=SUBSTITUTE(A2, " ", "_", 1)          ' Replace only first space
=SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")  ' Remove both parentheses

Joining text

=CONCAT(text1, text2, ...)           ' Excel 2016+
=CONCAT(A2, " ", B2)

=TEXTJOIN(delimiter, ignore_empty, text1, ...)  ' Excel 2019+
=TEXTJOIN(", ", TRUE, A2:A10)        ' Join non-empty cells with comma

' Ampersand operator (all versions)
=A2&" "&B2
=A2&" ("&B2&")"

TEXT formatting

=TEXT(value, format_text)
=TEXT(TODAY(), "dd/mm/yyyy")      ' → "14/07/2026"
=TEXT(1234567.8, "#,##0.00")      ' → "1,234,567.80"
=TEXT(0.153, "0.0%")              ' → "15.3%"
=TEXT(A2, "000000")               ' Zero-pad to 6 digits

Date and time functions

=TODAY()               ' Today's date (recalculates every day)
=NOW()                 ' Current date and time

=DATE(year, month, day)
=DATE(2026, 7, 14)     ' Serial number for 2026-07-14

=YEAR(date)    =MONTH(date)    =DAY(date)
=YEAR(TODAY())         ' → 2026

=WEEKDAY(date, [return_type])
=WEEKDAY(TODAY(), 2)   ' 1=Mon, 2=Tue... 7=Sun (type 2)

=DATEDIF(start_date, end_date, unit)
' unit: "Y"=years, "M"=months, "D"=days, "YM"=months within year
=DATEDIF(A2, TODAY(), "Y")    ' Age in complete years

=EDATE(start_date, months)
=EDATE(TODAY(), 1)     ' Same day, next month

=EOMONTH(start_date, months)
=EOMONTH(TODAY(), 0)   ' Last day of current month
=EOMONTH(TODAY(), 1)   ' Last day of next month

=WORKDAY(start_date, days, [holidays])
=WORKDAY(TODAY(), 10)  ' 10 working days from today

=NETWORKDAYS(start_date, end_date, [holidays])
=NETWORKDAYS(A2, B2)   ' Working days between two dates

Financial functions

=PMT(rate, nper, pv, [fv], [type])
' Monthly payment on a loan
=PMT(5%/12, 60, -20000)    ' 5% annual / 12 months, 60 payments, $20,000 loan

=FV(rate, nper, pmt, [pv], [type])
' Future value of an investment
=FV(8%/12, 120, -500)      ' Save $500/month at 8% for 10 years

=PV(rate, nper, pmt, [fv], [type])
' Present value of future cash flows
=PV(5%/12, 60, -500)       ' PV of $500/month for 5 years at 5%

=NPV(rate, value1, value2, ...)
=NPV(10%, B2:B6)            ' Net present value of cash flows in B2:B6

=IRR(values, [guess])
=IRR(A2:A8)                 ' Internal rate of return

=RATE(nper, pmt, pv)
=RATE(60, -350, 15000)      ' Interest rate per period

Modern array functions (Excel 365 / 2021+)

FILTER

=FILTER(array, include, [if_empty])
=FILTER(A2:C100, B2:B100="East")           ' Rows where column B = "East"
=FILTER(A2:C100, (B2:B100="East")*(C2:C100>100), "No results")  ' AND
=FILTER(A2:C100, (B2:B100="East")+(B2:B100="West"))  ' OR

SORT / SORTBY

=SORT(array, [sort_index], [sort_order], [by_col])
=SORT(A2:C100, 2, -1)      ' Sort by column 2 descending

=SORTBY(array, by_array1, sort_order1, ...)
=SORTBY(A2:B100, B2:B100, -1)    ' Sort by column B descending

UNIQUE

=UNIQUE(array, [by_col], [exactly_once])
=UNIQUE(A2:A100)            ' Deduplicated list
=UNIQUE(A2:B100)            ' Unique rows across both columns

SEQUENCE

=SEQUENCE(rows, [cols], [start], [step])
=SEQUENCE(10)               ' 1, 2, 3... 10
=SEQUENCE(5, 3, 0, 10)      ' 5×3 grid starting at 0, step 10

LET (define variables in a formula)

=LET(name1, value1, name2, value2, ..., result)
=LET(
    total, SUM(A2:A100),
    avg,   AVERAGE(A2:A100),
    total - avg
)

Useful formula patterns

Remove duplicates (count occurrences)

=COUNTIF($A$2:A2, A2)       ' In column B: returns 1 for first occurrence, 2+ for duplicates

Running total

=SUM($A$2:A2)               ' Expands as you drag down

Rank without ties affecting subsequent ranks

=RANK(A2, $A$2:$A$100, 0) + COUNTIF($A$2:A2, A2) - 1

Lookup with multiple criteria (no XLOOKUP)

=INDEX(C:C, MATCH(1, (A:A=G1)*(B:B=G2), 0))   ' Ctrl+Shift+Enter (array formula)

Dynamic dropdown source (unique sorted list)

=SORT(UNIQUE(FILTER(A2:A100, A2:A100<>"")))

Extract numbers from text

=SUMPRODUCT(MID(0&A2, LARGE(ISNUMBER(--MID(A2, ROW($1:$100), 1))*ROW($1:$100), ROW($1:$100))+1, 1)*10^ROW($1:$100)/10)
' Modern alternative: TEXTBEFORE / TEXTAFTER (365)

Age from date of birth

=DATEDIF(A2, TODAY(), "Y")  ' Complete years
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months"

Keyboard shortcuts for formulas

Common mistakes

Criteria syntax reference

FAQ

What is the difference between VLOOKUP and INDEX/MATCH? VLOOKUP can only look left-to-right and requires the lookup column to be first. INDEX/MATCH works in any direction, doesn't break when you insert columns, and handles larger datasets faster. XLOOKUP (Excel 365/2021+) supersedes both.

Why does my VLOOKUP return the wrong value? The most common cause is using TRUE (approximate match) when you need FALSE (exact match). Always use FALSE / 0 unless you explicitly need an approximate match on a sorted table.

How do I make a formula not recalculate every day? Replace TODAY() or NOW() with a hardcoded date. To paste today's date as a static value, press Ctrl+; (current date) or Ctrl+Shift+; (current time).

How do I apply a formula to a whole column without dragging? Double-click the fill handle (the small square at the bottom-right of the cell) — Excel extends the formula to match the length of the adjacent column. Alternatively, select the range, type the formula, then press Ctrl+D.

What is a named range and when should I use one? A named range labels a cell or range (e.g., TaxRate instead of $C$1). Use them when a value appears in many formulas — it makes formulas readable (=Price*TaxRate) and makes updates easy (change one cell, every formula updates). Create via Formulas → Define Name or Ctrl+F3.

What is the difference between SUMIF and SUMPRODUCT for conditional sums? SUMIFS is faster and clearer for simple AND conditions. SUMPRODUCT is more flexible — it handles OR logic, calculated criteria, and works in older Excel versions. =SUMPRODUCT((A2:A100="East")*(B2:B100>100)*(C2:C100)) is the SUMPRODUCT equivalent of a SUMIFS with two criteria.

How do I find duplicates in a column? =COUNTIF($A$2:$A$100, A2)>1 returns TRUE for any duplicate. Format these cells with conditional formatting for visual highlighting. With FILTER (365): =FILTER(A2:A100, COUNTIF(A2:A100, A2:A100)>1) lists all duplicate values.

What is an array formula and do I still need Ctrl+Shift+Enter? Legacy array formulas require Ctrl+Shift+Enter and show {=formula} curly braces. In Excel 365/2021+, most formulas are automatically "dynamic arrays" — you just press Enter, and results spill into neighbouring cells automatically. Use CTRL+SHIFT+ENTER only when working in older Excel versions that don't support dynamic arrays.