Excel Cheat Sheet for Analysts¶

Quick reference for Excel formulas, pivot tables, and Power Query. Version noted where relevant.

Lookup Functions¶

Formula	Purpose	Example
`=XLOOKUP(value, lookup_range, return_range, "Not found")`	Lookup any direction (Excel 365)	`=XLOOKUP(A2, Products!A:A, Products!C:C, "N/A")`
`=VLOOKUP(value, table, col_num, FALSE)`	Left-to-right lookup	`=VLOOKUP(A2, Products!A:D, 3, FALSE)`
`=INDEX(range, MATCH(value, lookup_range, 0))`	Flexible lookup	`=INDEX(C:C, MATCH(A2, A:A, 0))`
`=HLOOKUP(value, table, row_num, FALSE)`	Top-to-bottom lookup	Rarely used

Formula	Purpose	Example
`=SUMIF(range, criteria, sum_range)`	Sum if one condition	`=SUMIF(D:D, "completed", G:G)`
`=SUMIFS(sum, r1, c1, r2, c2, ...)`	Sum if multiple conditions	`=SUMIFS(G:G, D:D, "completed", C:C, "Electronics")`
`=COUNTIF(range, criteria)`	Count matching cells	`=COUNTIF(D:D, "cancelled")`
`=COUNTIFS(...)`	Count with multiple conditions	`=COUNTIFS(D:D, "completed", C:C, "Books")`
`=AVERAGEIF(range, criteria, avg_range)`	Average if condition	`=AVERAGEIF(C:C, "VIP", G:G)`

Formula	Purpose	Example
`=IF(condition, if_true, if_false)`	Conditional	`=IF(G2 > 200, "Large", "Small")`
`=IFS(c1, v1, c2, v2, ...)`	Multi-condition IF (Excel 2019+)	`=IFS(G2>300, "L", G2>100, "M", TRUE, "S")`
`=AND(c1, c2)`	All conditions true	`=AND(G2>100, D2="completed")`
`=OR(c1, c2)`	Any condition true	`=OR(D2="pending", D2="processing")`
`=NOT(condition)`	Negate	`=NOT(D2="cancelled")`
`=IFERROR(formula, value_if_error)`	Handle errors	`=IFERROR(VLOOKUP(A2, B:C, 2, 0), "Not found")`

Formula	Example
`=LEFT(text, n)`	`=LEFT(A2, 3)` — first 3 characters
`=RIGHT(text, n)`	`=RIGHT(A2, 4)` — last 4 characters
`=MID(text, start, n)`	`=MID(A2, 5, 3)` — 3 chars from position 5
`=LEN(text)`	`=LEN(A2)` — character count
`=TRIM(text)`	Remove leading/trailing spaces
`=LOWER(text) / UPPER(text) / PROPER(text)`	Case conversion
`=SUBSTITUTE(text, old, new)`	Replace all occurrences
`=CONCATENATE(t1, t2)` or `=A1 & " " & B1`	Join text
`=TEXTJOIN(", ", TRUE, A1:A5)`	Join with delimiter (Excel 2019+)
`=TEXT(A1, "£#,##0.00")`	Format number as text

To create: Insert → Pivot Table → choose data range → New Worksheet

Task	How
Summarise by category	Drag "category" to Rows, "revenue" to Values
Compare by two dimensions	Drag to both Rows and Columns
Change aggregation	Right-click value → Value Field Settings → choose Sum/Count/Average
Add % of total	Value Field Settings → Show Values As → % of Grand Total
Group dates by month	Right-click date field → Group → Months
Filter to one category	Drag "category" to Filters, select from dropdown
Add slicers	PivotTable Analyze → Insert Slicer
Refresh after source change	Right-click pivot → Refresh

To launch: Data → Get Data → From File/Table/Other

Task	Power Query steps
Remove blank rows	Home → Remove Rows → Remove Blank Rows
Split column by delimiter	Transform → Split Column → By Delimiter
Pivot/Unpivot	Transform → Pivot Column / Unpivot Columns
Change type	Click column header → change type icon
Merge queries (JOIN)	Home → Merge Queries → choose join type
Add conditional column	Add Column → Conditional Column
Filter rows	Dropdown arrow on column header
Replace values	Transform → Replace Values

To refresh: Data → Refresh All (runs all Power Query steps on fresh data)

Shortcut	Action
`Ctrl + T`	Convert range to Table
`Ctrl + Shift + L`	Toggle AutoFilter
`Alt + =`	AutoSum
`F4`	Toggle absolute/relative reference ($)
`Ctrl + D`	Fill Down
`Ctrl + R`	Fill Right
`Ctrl + ;`	Insert today's date
`Ctrl + Shift + ;`	Insert current time
`Ctrl + 1`	Format Cells dialog
`Ctrl + Shift + ~`	General format
`Ctrl + Shift + $`	Currency format
`Ctrl + Shift + %`	Percentage format
`Alt + H + O + I`	AutoFit column width
`Ctrl + Home`	Jump to A1
`Ctrl + End`	Jump to last used cell