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?

Power BI Basics Cheat Sheet

Google Sheets Formulas Cheat Sheet

Explore Topics

#ExcelForDataAnalysis#ExcelForDataAnalysisCheatSheet#DataScience#Beginner#LookupAggregationFormulas#BuildingAPivotTable#FunctionsShortcuts#Excel#MachineLearning#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services