Excel Cheat Sheet

Q: How do I stop errors like #N/A from showing up in a report?

Wrap the formula in `IFERROR`, e.g. `=IFERROR(VLOOKUP(A2,F:H,3,FALSE),"Not found")`. Use `IFNA` instead when you only want to catch a failed lookup and still see real problems like `#VALUE!` - hiding every error makes broken formulas invisible.

Last updated August 6, 2026

Learn Excel free

Formula basics

Every formula starts with an equals sign. Excel calculates it and shows the result in the cell.

Operation	Syntax
Start a formula	`=` then the expression, e.g. `=2+2`
Reference another cell	`=A1`
Arithmetic	`+` `-` `*` `/` and `^` for powers
Control the order of operations	`=(A1+A2)*B1`
Join text (concatenate)	`=A1&" "&B1` or `=CONCAT(A1," ",B1)`
Comparison operators	`=` `<>` `>` `<` `>=` `<=`
Percentage of a value	`=A1*15%`
Add a comment to a formula	`=SUM(A1:A9)+N("monthly total")`
Show formulas instead of results	`Ctrl` + ` (toggle)
Turn a formula into its result	Copy, then Paste Special → Values

Cell references and ranges

The $ locks a row or column so it doesn't shift when you copy the formula - the single most useful thing to understand in Excel.

Reference	Meaning
`A1`	Relative - shifts when copied in any direction
`$A$1`	Absolute - never shifts
`$A1`	Column locked, row shifts
`A$1`	Row locked, column shifts
`A1:A10`	A range of ten cells down one column
`A1:C10`	A rectangular block
`A:A`	The entire column A
`1:1`	The entire row 1
`Sheet2!A1`	A cell on another sheet
`'My Sheet'!A1`	Another sheet whose name contains a space
`[Book2.xlsx]Sheet1!A1`	A cell in another workbook
Toggle `$` while editing	`F4` (Windows), `Cmd` + `T` (Mac)

Math and aggregation functions

The everyday totals. All of them take a range, a list of cells, or a mix.

Function	What it does
`=SUM(B2:B20)`	Adds every number in the range
`=AVERAGE(B2:B20)`	Mean of the numbers
`=MEDIAN(B2:B20)`	Middle value
`=MIN(B2:B20)` / `=MAX(B2:B20)`	Smallest / largest value
`=PRODUCT(B2:B5)`	Multiplies the values together
`=SUMPRODUCT(B2:B20,C2:C20)`	Multiplies pairwise, then sums - weighted totals
`=ABS(B2)`	Absolute value
`=POWER(B2,3)`	B2 cubed (same as `=B2^3`)
`=SQRT(B2)`	Square root
`=MOD(B2,2)`	Remainder - `=0` for even numbers
`=SUBTOTAL(109,B2:B20)`	Sums only the visible rows (ignores filtered-out ones)
`=RAND()` / `=RANDBETWEEN(1,100)`	Random decimal / random whole number

Logical functions

IF is the workhorse. IFS and IFERROR keep long formulas readable.

Function	What it does
`=IF(B2>1000,"Over","OK")`	One condition, two outcomes
`=IF(B2>1000,"Over",IF(B2>500,"Watch","OK"))`	Nested IF for three or more outcomes
`=IFS(B2>1000,"Over",B2>500,"Watch",TRUE,"OK")`	Flat alternative to nested IFs
`=AND(B2>0,C2>0)`	TRUE only when every condition holds
`=OR(B2>0,C2>0)`	TRUE when any condition holds
`=NOT(B2>0)`	Inverts TRUE/FALSE
`=IFERROR(A2/B2,0)`	Replaces an error with a fallback value
`=IFNA(VLOOKUP(...),"Not found")`	Catches only #N/A
`=ISBLANK(B2)`	TRUE for an empty cell
`=ISNUMBER(B2)` / `=ISTEXT(B2)`	Type checks - useful for validating imported data
`=SWITCH(B2,1,"Low",2,"Mid",3,"High","Other")`	Matches one value against a list of cases

Counting and conditional totals

The *IF and *IFS family answers "how many" and "how much" for rows that match a rule.

Function	What it does
`=COUNT(B2:B20)`	Counts cells containing numbers
`=COUNTA(B2:B20)`	Counts non-empty cells of any type
`=COUNTBLANK(B2:B20)`	Counts empty cells
`=COUNTIF(B2:B20,">100")`	Counts rows matching one condition
`=COUNTIF(B2:B20,"north")`	Wildcards: `*` any characters, `?` one character
`=COUNTIFS(B2:B20,">100",C2:C20,"Paid")`	Counts rows matching several conditions
`=SUMIF(C2:C20,"Paid",B2:B20)`	Sums B where C matches
`=SUMIFS(B2:B20,C2:C20,"Paid",D2:D20,"EU")`	Sums with several conditions
`=AVERAGEIF(C2:C20,"Paid",B2:B20)`	Conditional average
`=MAXIFS(B2:B20,C2:C20,"Paid")`	Largest value among matching rows
`=COUNTIF($A$2:A2,A2)>1`	Flags a duplicate as you go down the column
`=SUMPRODUCT((C2:C20="Paid")*(B2:B20))`	Conditional total without SUMIFS

Lookup and reference functions

Pulling a value out of another table. XLOOKUP is the modern replacement for VLOOKUP; INDEX/MATCH works in every Excel version.

Function	What it does
`=VLOOKUP(A2,$F$2:$H$50,3,FALSE)`	Finds A2 in the first column, returns the 3rd column. `FALSE` = exact match
`=XLOOKUP(A2,$F$2:$F$50,$H$2:$H$50,"Not found")`	Lookup range and return range are separate - can look left
`=INDEX($H$2:$H$50,MATCH(A2,$F$2:$F$50,0))`	The classic version that works anywhere
`=MATCH(A2,$F$2:$F$50,0)`	The position of A2 in the range
`=HLOOKUP(A2,$F$1:$Z$4,3,FALSE)`	Same as VLOOKUP but scanning a row
`=INDEX(B2:D20,2,3)`	The cell at row 2, column 3 of the block
`=XLOOKUP(A2,F:F,H:H,,-1)`	Approximate match - next smaller item (tier/band lookups)
`=OFFSET(A1,2,1)`	The cell 2 down and 1 right of A1
`=INDIRECT("Sheet"&B1&"!A1")`	Builds a reference from text
`=CHOOSE(B2,"Low","Mid","High")`	Picks the Nth item from a list
`=UNIQUE(A2:A100)`	The distinct values in a range (spills)
`=FILTER(A2:C100,C2:C100="Paid")`	The rows matching a condition (spills)

Text functions

Most real spreadsheets start with messy text. These are the clean-up tools.

Function	What it does
`=LEN(A2)`	Number of characters
`=LEFT(A2,3)` / `=RIGHT(A2,3)`	First / last 3 characters
`=MID(A2,4,5)`	5 characters starting at position 4
`=TRIM(A2)`	Removes leading, trailing, and repeated spaces
`=CLEAN(A2)`	Strips non-printable characters from imported data
`=UPPER(A2)` / `=LOWER(A2)` / `=PROPER(A2)`	Change case
`=SUBSTITUTE(A2,"-","")`	Replaces every occurrence of a substring
`=REPLACE(A2,1,3,"NEW")`	Replaces by position instead of by content
`=FIND("@",A2)` / `=SEARCH("@",A2)`	Position of a substring (FIND is case-sensitive)
`=TEXTSPLIT(A2,",")`	Splits text into cells on a delimiter
`=TEXTJOIN(", ",TRUE,A2:A9)`	Joins a range with a separator, skipping blanks
`=TEXT(A2,"0.00")`	Formats a number as text with a pattern
`=VALUE(A2)`	Converts a numeric string into a real number
`=EXACT(A2,B2)`	Case-sensitive comparison

Date and time functions

Excel stores a date as a number, which is why you can subtract two dates and get days.

Function	What it does
`=TODAY()` / `=NOW()`	Today's date / the current date and time
`=YEAR(A2)`, `=MONTH(A2)`, `=DAY(A2)`	Pull one part out of a date
`=DATE(2026,8,6)`	Builds a date from parts
`=B2-A2`	Days between two dates
`=DATEDIF(A2,B2,"m")`	Whole months between two dates (`"y"`, `"m"`, `"d"`)
`=EDATE(A2,3)`	Same day, three months later
`=EOMONTH(A2,0)`	Last day of A2's month
`=WEEKDAY(A2,2)`	Day of week, 1 = Monday with the `2` argument
`=NETWORKDAYS(A2,B2)`	Working days between two dates
`=WORKDAY(A2,10)`	The date 10 working days after A2
`=TEXT(A2,"yyyy-mm-dd")`	Formats a date as text
`=HOUR(A2)`, `=MINUTE(A2)`	Time parts

Rounding and number functions

Rounding for display is a format; rounding for calculation is a function.

Function	What it does
`=ROUND(A2,2)`	Rounds to 2 decimal places
`=ROUNDUP(A2,0)` / `=ROUNDDOWN(A2,0)`	Always up / always down
`=MROUND(A2,5)`	Rounds to the nearest multiple of 5
`=CEILING(A2,1)` / `=FLOOR(A2,1)`	Up / down to a multiple
`=INT(A2)`	Drops the decimal part
`=TRUNC(A2,1)`	Cuts off decimals without rounding
`=RANK(B2,$B$2:$B$20)`	Position of a value within a range
`=PERCENTILE(B2:B20,0.9)`	The 90th percentile
`=STDEV.S(B2:B20)`	Standard deviation of a sample
`=CORREL(B2:B20,C2:C20)`	Correlation between two columns

Error codes and what they mean

Each error points at a specific mistake - reading them saves a lot of guessing.

Error	Cause	Usual fix
`#DIV/0!`	Dividing by zero or by an empty cell	Wrap in `IFERROR`, or guard with `IF(B2=0,...)`
`#N/A`	A lookup found nothing	Check for stray spaces (`TRIM`) and matching data types
`#VALUE!`	Wrong type of argument - text where a number is expected	Check the referenced cells; try `VALUE()`
`#REF!`	The formula points at a deleted cell	Rebuild the reference
`#NAME?`	A misspelled function or an unquoted text string	Fix the spelling; add quotes around text
`#NUM!`	A numeric result Excel can't represent	Check for impossible arguments, e.g. `SQRT(-1)`
`#NULL!`	Two ranges that don't intersect	Check for a missing comma between arguments
`#SPILL!`	A dynamic array has no room to expand	Clear the cells below or to the right
`####`	Not an error - the column is too narrow	Widen the column
Circular reference	A formula includes its own cell	Remove the self-reference

Sorting, filtering, and data tools

Where a dataset stops being a grid of values and starts being something you can read.

Task	How
Sort a range	Data → Sort, or `Alt` + `A` then `S`
Add filter dropdowns	`Ctrl` + `Shift` + `L`
Format as a table	`Ctrl` + `T` - gives named ranges and auto-expanding formulas
Remove duplicates	Data → Remove Duplicates
Split one column into several	Data → Text to Columns
Flash Fill (pattern-based fill)	`Ctrl` + `E`
Freeze the header row	View → Freeze Panes → Freeze Top Row
Conditional formatting	Home → Conditional Formatting - colour cells by rule
Data validation (dropdown list)	Data → Data Validation → List
Name a range	Select it, then type a name in the Name Box
Trace a formula's inputs	Formulas → Trace Precedents
Goal Seek (solve for an input)	Data → What-If Analysis → Goal Seek

Pivot tables in five steps

The fastest way to summarize a few thousand rows.

Step	Action
1. Clean the source	One header row, no blank rows or merged cells
2. Insert	Select the data → Insert → PivotTable
3. Rows	Drag the field you want to group by into Rows
4. Values	Drag the number you want to total into Values
5. Summarize	Click the value field → Summarize Values By → Sum / Count / Average
Add a second dimension	Drag a field into Columns
Filter the whole table	Drag a field into Filters, or add a Slicer
Show percentages	Value field → Show Values As → % of Grand Total
Refresh after the data changes	`Alt` + `F5`
Read one cell of a pivot in a formula	`=GETPIVOTDATA("Sales",$A$3,"Region","EU")`

Keyboard shortcuts - the essentials

The dozen that save the most time.

Action	Windows	Mac
Edit the active cell	`F2`	`Ctrl` + `U`
Confirm and stay in the cell	`Ctrl` + `Enter`	`Ctrl` + `Enter`
New line inside a cell	`Alt` + `Enter`	`Ctrl` + `Option` + `Enter`
AutoSum	`Alt` + `=`	`Cmd` + `Shift` + `T`
Toggle `$` in a reference	`F4`	`Cmd` + `T`
Fill down from the cell above	`Ctrl` + `D`	`Cmd` + `D`
Fill right	`Ctrl` + `R`	`Cmd` + `R`
Paste Special	`Ctrl` + `Alt` + `V`	`Cmd` + `Ctrl` + `V`
Insert today's date	`Ctrl` + `;`	`Cmd` + `;`
Repeat the last action	`F4`	`Cmd` + `Y`
Undo / redo	`Ctrl` + `Z` / `Ctrl` + `Y`	`Cmd` + `Z` / `Cmd` + `Shift` + `Z`
Show formulas	`Ctrl` + `	`Ctrl` + `

Moving around a large sheet without touching the mouse.

Action	Windows	Mac
Jump to the edge of the data	`Ctrl` + arrow	`Cmd` + arrow
Select to the edge of the data	`Ctrl` + `Shift` + arrow	`Cmd` + `Shift` + arrow
Select the whole column / row	`Ctrl` + `Space` / `Shift` + `Space`	`Ctrl` + `Space` / `Shift` + `Space`
Select the current region	`Ctrl` + `A`	`Cmd` + `A`
Go to cell A1	`Ctrl` + `Home`	`Fn` + `Ctrl` + `Left`
Go to a specific cell	`Ctrl` + `G`	`Ctrl` + `G`
Next / previous sheet	`Ctrl` + `PgDn` / `PgUp`	`Option` + `Right` / `Left`
Insert rows or columns	`Ctrl` + `Shift` + `+`	`Cmd` + `Shift` + `+`
Delete rows or columns	`Ctrl` + `-`	`Cmd` + `-`
Hide a column / row	`Ctrl` + `0` / `Ctrl` + `9`	`Cmd` + `0` / `Cmd` + `9`
Find / replace	`Ctrl` + `F` / `Ctrl` + `H`	`Cmd` + `F` / `Ctrl` + `H`
Select only visible cells	`Alt` + `;`	`Cmd` + `Shift` + `Z`

Keyboard shortcuts - formatting

Number formats are the ones worth memorizing - they come up constantly.

Action	Windows	Mac
Format Cells dialog	`Ctrl` + `1`	`Cmd` + `1`
Bold / italic / underline	`Ctrl` + `B` / `I` / `U`	`Cmd` + `B` / `I` / `U`
Currency format	`Ctrl` + `Shift` + `$`	`Ctrl` + `Shift` + `$`
Percentage format	`Ctrl` + `Shift` + `%`	`Ctrl` + `Shift` + `%`
Number format with 2 decimals	`Ctrl` + `Shift` + `!`	`Ctrl` + `Shift` + `!`
Date format	`Ctrl` + `Shift` + `#`	`Ctrl` + `Shift` + `#`
General (remove) format	`Ctrl` + `Shift` + `~`	`Ctrl` + `Shift` + `~`
Outline border	`Ctrl` + `Shift` + `&`	`Cmd` + `Option` + `0`
Remove borders	`Ctrl` + `Shift` + `_`	`Cmd` + `Option` + `-`
Copy formatting (Format Painter)	`Ctrl` + `Shift` + `C`, then `Ctrl` + `Shift` + `V`	`Cmd` + `Shift` + `C`, then `Cmd` + `Shift` + `V`

The Excel formulas, functions, and shortcuts you reach for most, on one page. This Excel cheat sheet is a quick reference for the things that actually come up in a working spreadsheet - writing formulas, absolute vs relative cell references, IF and the counting functions, VLOOKUP and XLOOKUP, cleaning up text, dates, what each error code means, and the keyboard shortcuts worth committing to memory.

Everything here works in Excel for Windows and Mac, and almost all of it works unchanged in Google Sheets and LibreOffice Calc. Function names are given in English - that is what Excel stores internally, though a non-English install of Excel displays them translated.

Excel cheat sheet FAQ

Is this Excel cheat sheet free?

Yes - the whole page is free, with no signup and nothing to download. Copy any formula straight out of the tables.

What are the most important Excel formulas to know?

If you only learn ten: SUM, AVERAGE, IF, COUNTIF, SUMIF, XLOOKUP (or VLOOKUP), INDEX with MATCH, TRIM, TEXT, and IFERROR. Between them they cover totalling, conditional logic, pulling values from another table, cleaning up messy text, and stopping errors from breaking a report.

What does the $ mean in an Excel formula?

It locks part of a reference so it doesn't move when you copy the formula. $A$1 always points at A1; $A1 keeps column A but lets the row change; A$1 keeps row 1 but lets the column change. Press F4 (or Cmd + T on a Mac) while editing a reference to cycle through the four combinations.

Should I use VLOOKUP or XLOOKUP?

Use XLOOKUP if your Excel has it (Microsoft 365 and Excel 2021 onward). It takes the lookup column and the return column as separate arguments, so it can look to the left, it doesn't break when someone inserts a column, and it has a built-in "not found" argument. VLOOKUP is still worth knowing because it appears in every older workbook you'll inherit. INDEX/MATCH is the version that works in every Excel release.

Do these formulas work in Google Sheets?

Nearly all of them, unchanged - formulas, cell references, IF, COUNTIF/SUMIF, the text and date functions, VLOOKUP, INDEX/MATCH, UNIQUE and FILTER. The keyboard shortcuts differ more, and a few functions are Excel-only (such as some of the newer dynamic array functions), so check anything unusual before relying on it.

Why do the function names look different in my Excel?

Excel translates function names to your interface language, so a German install shows SUMME instead of SUM and a French one SOMME. The file itself stores the English name, which is why documentation, this cheat sheet, and shared workbooks all use the English form. Type the translated name in your own Excel and it means exactly the same thing.

How do I stop errors like #N/A from showing up in a report?

Wrap the formula in IFERROR, e.g. =IFERROR(VLOOKUP(A2,F:H,3,FALSE),"Not found"). Use IFNA instead when you only want to catch a failed lookup and still see real problems like #VALUE! - hiding every error makes broken formulas invisible.

Where can I practise these formulas?

Coddy's free interactive Excel course runs a real spreadsheet in your browser: each lesson gives you a small dataset and a task, and checks the formula you type. Nothing to install, and there's a free certificate at the end.