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
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#Excel#MachineLearning#CheatSheet#SkillVeris
