Excel for Data Analysis Cheat Sheet
Reference for Excel lookup and aggregation formulas, PivotTable construction, and productivity shortcuts for everyday spreadsheet data analysis.
2 PagesBeginnerMar 15, 2026
Lookup & Aggregation Formulas
The formulas used most often for analyzing spreadsheet data.
excel
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE) ' exact match lookup=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C) ' modern replacement for VLOOKUP=SUMIFS(C:C, A:A, "US", B:B, ">100") ' sum with multiple conditions=COUNTIFS(A:A, "US", B:B, ">100") ' count with multiple conditions=AVERAGEIFS(C:C, A:A, "US") ' average with a condition=INDEX(C:C, MATCH(A2, A:A, 0)) ' flexible lookup in any direction=IFERROR(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "Not found")=TEXT(A2, "yyyy-mm-dd") ' format a value as text
Building a PivotTable
The steps to summarize a table interactively.
- Select data range- Click inside your table, then Insert > PivotTable (ensure headers have no blank/merged cells)
- Rows/Columns area- Drag categorical fields here to group data
- Values area- Drag numeric fields here; defaults to Sum, change via Value Field Settings
- Filters area- Drag fields here to add report-level filters above the pivot
- Refresh- Right-click > Refresh (or Ctrl+Alt+F5) updates the pivot after source data changes
- Calculated field- PivotTable Analyze > Fields, Items & Sets > Calculated Field for custom formulas within the pivot
Functions & Shortcuts
Tools and keyboard shortcuts that speed up analysis.
- VLOOKUP/XLOOKUP- Look up a value in one column/table and return a corresponding value from another column
- INDEX/MATCH- Combination lookup that works left-to-right or right-to-left, unlike VLOOKUP
- Text-to-Columns- Data tab tool to split a single column into multiple by delimiter or fixed width
- Conditional Formatting- Highlights cells automatically based on rules (e.g. color scale, top 10%, duplicate values)
- Ctrl+T- Convert a range to an Excel Table (structured references, auto-expanding ranges)
- F4- Toggle between relative and absolute cell references ($A$1) while editing a formula
Dynamic Array Functions
Modern spill-based functions for filtering, sorting, and reshaping ranges without helper columns.
excel
=FILTER(A2:C100, (B2:B100="US") * (C2:C100>100)) ' multi-condition filter, spills results=SORT(FILTER(A2:C100, B2:B100="US"), 3, -1) ' filter then sort descending=SORTBY(A2:A100, B2:B100, -1, A2:A100, 1) ' sort by one array using another=UNIQUE(A2:A100, FALSE, TRUE) ' values that occur exactly once=SEQUENCE(12, 1, 1, 1) ' spill 1..12 down a column=TAKE(SORT(A2:C100, 3, -1), 10) ' top 10 rows after sorting=A2:A100# ' spill reference: refers to a whole spilled range
Power Query (M) Transform
Reusable ETL step reshaping and cleaning a source table before it lands on the sheet.
excel
let Source = Excel.CurrentWorkbook(){[Name="RawSales"]}[Content], ChangedType = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Amount", type number}}), Filtered = Table.SelectRows(ChangedType, each [Amount] > 0), Grouped = Table.Group(Filtered, {"Region", "Date"}, { {"TotalSales", each List.Sum([Amount]), type number}, {"OrderCount", each Table.RowCount(_), Int64.Type} }), AddedMonth = Table.AddColumn(Grouped, "Month", each Date.StartOfMonth([Date]), type date)in AddedMonth
Array Formulas & Custom LAMBDA
Multi-cell array math and a reusable named function for repeated business logic.
excel
{=SUM((A2:A100="US")*(C2:C100))} ' legacy Ctrl+Shift+Enter array formula=SUMPRODUCT((A2:A100="US")*(B2:B100>100)*C2:C100) ' equivalent, no CSE needed' Define once via Name Manager as MARGIN_PCT:=LAMBDA(revenue, cost, (revenue-cost)/revenue)' Then call it anywhere on the sheet:=MARGIN_PCT(D2, E2)' Reduce a range with a running accumulator=REDUCE(0, C2:C100, LAMBDA(acc, x, acc + x))
What-If Analysis Tools
Built-in tools for sensitivity analysis and optimization beyond static formulas.
- Data Table (1/2-variable)- What-If Analysis > Data Table recalculates a formula across a grid of input values without copy-pasting formulas
- Goal Seek- Back-solves a single input to hit a target output value for one formula cell
- Solver add-in- Optimizes an objective cell subject to constraints by adjusting multiple variable cells (linear/nonlinear)
- Scenario Manager- Saves and switches between named sets of input values to compare outcomes side by side
- Circular reference + iterative calc- File > Options > Formulas > Enable iterative calculation, needed for models with intentional feedback loops
- Power Pivot / DAX measures- Add-in for building an in-memory data model with relationships and DAX measures across millions of rows
Power Pivot DAX Measures
Measures written for a Power Pivot data model, used when PivotTables need calculations beyond simple aggregation.
excel
Total Sales := SUM(FactSales[Amount])YoY Growth % :=VAR CurrentSales = [Total Sales]VAR PriorYearSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))RETURN DIVIDE(CurrentSales - PriorYearSales, PriorYearSales)Running Total :=CALCULATE( [Total Sales], FILTER(ALLSELECTED(DimDate[Date]), DimDate[Date] <= MAX(DimDate[Date])))
Pro Tip
Convert your source range into an Excel Table (Ctrl+T) before building formulas or PivotTables - structured references like Table1[Sales] auto-expand as new rows are added, so formulas and pivots don't silently miss new data.
Was this cheat sheet helpful?
Explore Topics
#ExcelForDataAnalysis#ExcelForDataAnalysisCheatSheet#DataScience#Beginner#LookupAggregationFormulas#BuildingAPivotTable#FunctionsShortcuts#DynamicArrayFunctions#MachineLearning#CheatSheet#SkillVeris
