Excel Formulas Cheatsheet

REFERENCE v1.0

Excel Formulas Quick Reference

Everything you need day‑to‑day – from basic sums to advanced lookups and array formulas.

Formula Basics

Syntax Rules

All formulas start with =
Function names are case‑insensitive (SUM = sum)
Arguments separated by commas , or semicolons ;
Use : for ranges (A1:A10)
Use $ for absolute references ($A$1)
Use $A1 for mixed reference (column fixed)
Use A$1 for mixed reference (row fixed)

Cell References

Relative: A1 – changes when copied
Absolute: $A$1 – fixed
Mixed: $A1 or A$1 – one fixed, one relative
3D Reference: Sheet1:Sheet3!A1
Structured: Table1[Column1] (Excel Tables)

Basic Math Functions

Function	Description	Example
`=SUM()`	Adds numbers	`=SUM(A1:A10)`
`=AVERAGE()`	Average of numbers	`=AVERAGE(A1:A10)`
`=COUNT()`	Count numeric values	`=COUNT(A1:A10)`
`=COUNTA()`	Count non‑empty cells	`=COUNTA(A1:A10)`
`=COUNTBLANK()`	Count blank cells	`=COUNTBLANK(A1:A10)`
`=MIN()`	Minimum value	`=MIN(A1:A10)`
`=MAX()`	Maximum value	`=MAX(A1:A10)`
`=MEDIAN()`	Median value	`=MEDIAN(A1:A10)`
`=MODE()`	Most frequent value	`=MODE(A1:A10)`
`=STDEV()`	Sample standard deviation	`=STDEV(A1:A10)`
`=VAR()`	Sample variance	`=VAR(A1:A10)`
`=PRODUCT()`	Multiply numbers	`=PRODUCT(A1:A10)`
`=ABS()`	Absolute value	`=ABS(-10)` → 10
`=ROUND()`	Round to specified digits	`=ROUND(3.14159, 2)` → 3.14
`=ROUNDUP()`	Round up	`=ROUNDUP(3.14, 1)` → 3.2
`=ROUNDDOWN()`	Round down	`=ROUNDDOWN(3.14, 1)` → 3.1
`=INT()`	Integer part	`=INT(3.99)` → 3
`=MOD()`	Remainder	`=MOD(10, 3)` → 1
`=POWER()`	Exponentiation	`=POWER(2, 3)` → 8
`=SQRT()`	Square root	`=SQRT(16)` → 4
`=RAND()`	Random number (0-1)	`=RAND()`
`=RANDBETWEEN()`	Random integer	`=RANDBETWEEN(1, 100)`

Logical Functions

Function	Description	Example
`=IF()`	Conditional logic	`=IF(A1>10, "High", "Low")`
`=IFS()`	Multiple conditions (Excel 2016+)	`=IFS(A1>90,"A", A1>80,"B", A1>70,"C")`
`=AND()`	All conditions true	`=AND(A1>10, B1<20)`
`=OR()`	Any condition true	`=OR(A1>10, B1<5)`
`=NOT()`	Negate condition	`=NOT(A1=5)`
`=IFERROR()`	Handle errors	`=IFERROR(A1/B1, "Error")`
`=IFNA()`	Handle #N/A only	`=IFNA(VLOOKUP(A1,B1:C10,2,0), "Not Found")`
`=ISBLANK()`	Check if blank	`=ISBLANK(A1)`
`=ISNUMBER()`	Check if number	`=ISNUMBER(A1)`
`=ISTEXT()`	Check if text	`=ISTEXT(A1)`
`=ISERROR()`	Check if error	`=ISERROR(A1/B1)`
`=ISNA()`	Check if #N/A	`=ISNA(VLOOKUP(A1,B1:C10,2,0))`
`=SWITCH()`	Switch between values	`=SWITCH(A1, 1, "One", 2, "Two", "Other")`

Lookup & Reference Functions

Function	Description	Example
`=VLOOKUP()`	Vertical lookup	`=VLOOKUP(A1, B1:D10, 3, 0)`
`=HLOOKUP()`	Horizontal lookup	`=HLOOKUP(A1, B1:D10, 3, 0)`
`=XLOOKUP()`	Modern lookup (Excel 2021+)	`=XLOOKUP(A1, B1:B10, C1:C10, "Not Found")`
`=INDEX()`	Value at row/col	`=INDEX(A1:C10, 2, 3)`
`=MATCH()`	Position of value	`=MATCH(A1, B1:B10, 0)`
`=INDEX/MATCH`	Combine for flexible lookup	`=INDEX(C1:C10, MATCH(A1, B1:B10, 0))`
`=FILTER()`	Filter data (Excel 2021+)	`=FILTER(A1:C10, B1:B10="Active")`
`=SORT()`	Sort data (Excel 2021+)	`=SORT(A1:C10, 2, 1)`
`=UNIQUE()`	Unique values (Excel 2021+)	`=UNIQUE(A1:A10)`
`=CHOOSE()`	Select from list	`=CHOOSE(A1, "Red", "Blue", "Green")`
`=ADDRESS()`	Cell address as text	`=ADDRESS(1, 1)` → "$A$1"
`=ROW()`	Row number	`=ROW(A1)` → 1
`=COLUMN()`	Column number	`=COLUMN(A1)` → 1
`=OFFSET()`	Dynamic range	`=OFFSET(A1, 1, 2)`
`=INDIRECT()`	Reference from text	`=INDIRECT("A" & B1)`

VLOOKUP vs XLOOKUP

VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

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

VLOOKUP – limited to right‑to‑left lookups, requires exact column index
XLOOKUP – can look in any direction, returns arrays, more flexible
MATCH – returns position; use with INDEX for flexible lookups

Text Functions

Function	Description	Example
`=LEFT()`	Characters from left	`=LEFT("Hello", 2)` → "He"
`=RIGHT()`	Characters from right	`=RIGHT("Hello", 2)` → "lo"
`=MID()`	Characters from middle	`=MID("Hello", 2, 3)` → "ell"
`=LEN()`	Length of text	`=LEN("Hello")` → 5
`=FIND()`	Position of text (case‑sensitive)	`=FIND("l", "Hello")` → 3
`=SEARCH()`	Position of text (case‑insensitive)	`=SEARCH("L", "Hello")` → 3
`=REPLACE()`	Replace text at position	`=REPLACE("Hello", 2, 3, "i")` → "Hio"
`=SUBSTITUTE()`	Replace text by content	`=SUBSTITUTE("Hello World", "World", "Excel")` → "Hello Excel"
`=CONCATENATE()`	Join text (legacy)	`=CONCATENATE("Hello", " ", "World")`
`=CONCAT()`	Join text (modern)	`=CONCAT("Hello", " ", "World")`
`=TEXTJOIN()`	Join with delimiter (Excel 2019+)	`=TEXTJOIN(", ", TRUE, A1:A10)`
`=UPPER()`	Convert to uppercase	`=UPPER("hello")` → "HELLO"
`=LOWER()`	Convert to lowercase	`=LOWER("HELLO")` → "hello"
`=PROPER()`	Capitalise words	`=PROPER("hello world")` → "Hello World"
`=TRIM()`	Remove extra spaces	`=TRIM(" Hello ")` → "Hello"
`=CLEAN()`	Remove non‑printable characters	`=CLEAN(A1)`
`=TEXT()`	Format number as text	`=TEXT(1234.56, "$#,##0.00")` → "$1,234.56"
`=VALUE()`	Convert text to number	`=VALUE("123")` → 123

Date & Time Functions

Function	Description	Example
`=TODAY()`	Current date	`=TODAY()`
`=NOW()`	Current date and time	`=NOW()`
`=DATE()`	Create date	`=DATE(2024, 1, 15)`
`=YEAR()`	Year from date	`=YEAR(TODAY())`
`=MONTH()`	Month from date	`=MONTH(TODAY())`
`=DAY()`	Day from date	`=DAY(TODAY())`
`=WEEKDAY()`	Day of week	`=WEEKDAY(TODAY(), 1)`
`=WEEKNUM()`	Week number	`=WEEKNUM(TODAY())`
`=EDATE()`	Add/subtract months	`=EDATE(TODAY(), 3)`
`=EOMONTH()`	End of month	`=EOMONTH(TODAY(), 0)`
`=DATEDIF()`	Date difference	`=DATEDIF(A1, TODAY(), "Y")`
`=DAYS()`	Days between dates	`=DAYS(TODAY(), A1)`
`=WORKDAY()`	Add workdays (excl weekends)	`=WORKDAY(TODAY(), 10)`
`=NETWORKDAYS()`	Workdays between dates	`=NETWORKDAYS(A1, B1)`
`=DATEVALUE()`	Convert text to date	`=DATEVALUE("2024-01-15")`
`=TIME()`	Create time	`=TIME(14, 30, 0)`
`=HOUR()`	Hour from time	`=HOUR(NOW())`
`=MINUTE()`	Minute from time	`=MINUTE(NOW())`
`=SECOND()`	Second from time	`=SECOND(NOW())`

Statistical Functions

Function	Description	Example
`=COUNTIF()`	Count with condition	`=COUNTIF(A1:A10, ">10")`
`=COUNTIFS()`	Count with multiple conditions	`=COUNTIFS(A1:A10, ">10", B1:B10, "<5")`
`=SUMIF()`	Sum with condition	`=SUMIF(A1:A10, ">10", B1:B10)`
`=SUMIFS()`	Sum with multiple conditions	`=SUMIFS(B1:B10, A1:A10, ">10", C1:C10, "Active")`
`=AVERAGEIF()`	Average with condition	`=AVERAGEIF(A1:A10, ">10", B1:B10)`
`=AVERAGEIFS()`	Average with multiple conditions	`=AVERAGEIFS(B1:B10, A1:A10, ">10", C1:C10, "Active")`
`=SUMPRODUCT()`	Sum of products	`=SUMPRODUCT(A1:A10, B1:B10)`
`=LARGE()`	K‑th largest	`=LARGE(A1:A10, 2)`
`=SMALL()`	K‑th smallest	`=SMALL(A1:A10, 2)`
`=RANK()`	Rank of value	`=RANK(A1, A1:A10, 0)`
`=PERCENTILE()`	Percentile	`=PERCENTILE(A1:A10, 0.9)`
`=QUARTILE()`	Quartile	`=QUARTILE(A1:A10, 1)`

Array Formulas

Classic: Ctrl + Shift + Enter (CSE) – wrap in {}
Dynamic: Excel 365 – spill results automatically

=SUM(A1:A10 * B1:B10) → {=SUM(A1:A10 * B1:B10)}

=MAX(IF(A1:A10>0, A1:A10)) → {=MAX(IF(A1:A10>0, A1:A10))}

=TRANSPOSE(A1:A10) → transposes rows/columns

Common Formula Examples

Nested IF with AND/OR

=IF(AND(A1>10, B1<20), "Pass", "Fail")
=IF(OR(A1>10, B1<5), "Yes", "No")

VLOOKUP with IFERROR

=IFERROR(VLOOKUP(A1, B1:C10, 2, 0), "Not Found")

INDEX/MATCH (Flexible Lookup)

=INDEX(C1:C10, MATCH(A1, B1:B10, 0))

Dynamic Date Range

=SUMIFS(Sales, Date, ">="&TODAY()-30, Date, "<="&TODAY())

Count Unique Values

=SUMPRODUCT(1/COUNTIF(A1:A10, A1:A10))

Running Total

=SUM($B$2:B2)  // drag down

Extract Last Word

=RIGHT(A1, LEN(A1)-FIND("*", SUBSTITUTE(A1, " ", "*", LEN(A1)-LEN(SUBSTITUTE(A1, " ", "")))))

Keyboard Shortcuts

Action	Windows	Mac
Edit cell	`F2`	`Ctrl` + `U`
Absolute reference toggle	`F4`	`⌘` + `T`
Insert formula	`=`	`=`
AutoSum	`Alt` + `=`	`⌘` + `Shift` + `T`
Insert function dialog	`Shift` + `F3`	`Shift` + `F3`
Fill down	`Ctrl` + `D`	`⌘` + `D`
Fill right	`Ctrl` + `R`	`⌘` + `R`
Select range	`Ctrl` + `Shift` + `→/↓`	`⌘` + `Shift` + `→/↓`
Insert new worksheet	`Shift` + `F11`	`Shift` + `F11`
Trace precedents	`Ctrl` + `[`	`⌘` + `[`

Common Error Values

#DIV/0! – division by zero
#N/A – value not available (lookup failed)
#NAME? – unrecognised function name
#NULL! – invalid range intersection
#NUM! – invalid number
#REF! – invalid cell reference
#VALUE! – wrong argument type
#SPILL! – array formula can't spill (Excel 365)
#CALC! – calculation error (Excel 365)

📌 Quick Reference

Math: SUM, AVERAGE, COUNT, MIN, MAX, ROUND, MOD
Logical: IF, IFS, AND, OR, NOT, IFERROR
Lookup: VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH, FILTER
Text: LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE, TEXTJOIN
Date: TODAY, DATE, YEAR, MONTH, DAY, EOMONTH, DATEDIF
Statistical: COUNTIF, SUMIF, AVERAGEIF, SUMPRODUCT
Array: Ctrl+Shift+Enter (classic) or dynamic arrays (Excel 365)
Errors: #DIV/0!, #N/A, #NAME?, #REF!, #VALUE!

← Back to All Cheatsheets