Module 3: Excel / Spreadsheets

Module 3: Excel / Spreadsheets

Same depth, continuing the Data Analyst track — this is the Excel/Spreadsheet module. The senior-level thread to pull through all of this is “Excel is a legitimate analytical tool with real limits, not a thing real analysts grow out of” — that framing alone differentiates a candidate from someone who dismisses it as junior-level work.

1. Formulas — VLOOKUP/XLOOKUP, INDEX-MATCH, SUMIFS/COUNTIFS, Array Formulas

The lookup family — know the evolution, because interviewers test whether you know why the newer functions exist, not just that they exist:

Function Limitation / advantage When to use
VLOOKUP(lookup_value, table, col_index, [exact]) Only looks rightward; breaks if columns are inserted/reordered (col_index is a hardcoded position) Legacy spreadsheets, simple left-to-right lookups
INDEX(MATCH()) Looks in either direction; immune to column insertion since MATCH finds position dynamically The “old-school robust” combo — still the right answer in Excel versions without XLOOKUP, or when you need to return a column position dynamically
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Looks either direction, built-in “not found” handling, no col-index counting, supports approximate match modes explicitly The modern default in Excel 365 — but not available in older Excel versions or Google Sheets’ older releases, which matters in interviews about “which company’s environment” 
=VLOOKUP(A2, Lookup_Table, 3, FALSE)              ' rigid - breaks if a column is inserted into Lookup_Table
=INDEX(Lookup_Table[Value], MATCH(A2, Lookup_Table[Key], 0))   ' robust to column reordering
=XLOOKUP(A2, Lookup_Table[Key], Lookup_Table[Value], "Not Found")  ' cleanest modern syntax

Interview talking point: “I default to INDEX-MATCH or XLOOKUP over VLOOKUP, because VLOOKUP’s column index is a hardcoded integer — if someone inserts a column into the lookup table, every VLOOKUP silently returns the wrong value with no error. That’s a real production failure mode I’ve seen break a finance model, and it’s exactly the kind of fragility a senior analyst designs out from the start.”

SUMIFS / COUNTIFS — the multi-condition aggregation workhorses:

=SUMIFS(Sales[Amount], Sales[Region], "South", Sales[Month], ">="&DATE(2026,1,1))
=COUNTIFS(Orders[Status], "Returned", Orders[Customer], A2)

Key nuance: these take range pairs, not a single boolean condition — and all ranges must be the same size/shape or you get a #VALUE! error. This is a common silent-error trap with merged or inconsistent ranges.

Array formulas — the modern shift (dynamic arrays in Excel 365):

=SUMPRODUCT((Sales[Region]="South")*(Sales[Amount]))     ' classic array-style aggregation, pre-365
=FILTER(Sales[Amount], Sales[Region]="South")             ' modern dynamic array — spills automatically
=SORT(UNIQUE(Sales[Customer]))                            ' chained dynamic array functions

Talking point on dynamic arrays: “Dynamic array functions like FILTER, UNIQUE, and SORT changed the mental model — formulas now ‘spill’ into adjacent cells automatically instead of needing Ctrl+Shift+Enter array-entry syntax. It’s worth knowing both, because plenty of production spreadsheets in enterprise environments are still on older Excel versions without this.”

2. PivotTables and PivotCharts

The conceptual point that matters most: a PivotTable is a GROUP BY with a UI — same underlying logic as the SQL aggregation from the last module, just reshaped into rows/columns/values.

PivotTable Area SQL Equivalent
Rows GROUP BY
Columns A second GROUP BY dimension, pivoted wide
Values Aggregate function (SUM, COUNT, AVG) in SELECT
Filters WHERE
Calculated Field A derived column computed inside the aggregation

Senior nuances that get tested:

  • “Distinct Count” as a Values aggregation requires the data to be loaded into the Data Model (Power Pivot), not a plain PivotTable on a flat range — a common gotcha when someone needs COUNT(DISTINCT customer_id) equivalent behavior.
  • Calculated Fields vs. Calculated Items: a Calculated Field operates on the underlying data (safe, recalculates correctly); a Calculated Item operates on the displayed pivot result and can produce subtly wrong totals when mixed with certain aggregations — this is a known footgun worth naming.
  • Grouping dates — right-click → Group lets you roll daily transaction data up to month/quarter/year without touching the source data, which is the Excel equivalent of DATE_TRUNC in SQL.

PivotCharts are just a PivotTable with a chart bound to it — filtering the chart filters the table and vice versa. The talking point: “PivotCharts are great for quick exploratory iteration, but for anything going into a recurring stakeholder report, I’d rebuild the same logic in a BI tool like Power BI/Tableau, because PivotCharts don’t have good version control or sharing/permissions story outside the workbook.”

3. Data Validation, Conditional Formatting, What-If Analysis

Data Validation — prevents bad data at entry rather than catching it downstream:

Data → Data Validation → List: =Lookup_Categories

Talking point: “Data validation dropdowns are a cheap, high-leverage way to prevent the categorical-inconsistency problem — ‘NY’ vs ‘New York’ vs ‘ny’ — that otherwise eats 60-80% of cleaning time downstream. It’s a control placed at collection time instead of a fix applied at analysis time.” (Nice callback to the “Clean” stage of the lifecycle from the orientation module.)

Conditional Formatting — visual encoding of rules, useful but a known performance trap on large sheets (formula-based conditional formatting recalculates on every change, and can make a large workbook sluggish). Senior tip: prefer built-in rule types (data bars, color scales, top/bottom N) over custom formulas when possible, for performance.

What-If Analysis — three tools, each for a different shape of question:

Tool Question it answers Limitation
Goal Seek “What input value gives me this exact output?” (single variable, single target) Only solves for one variable at a time
Data Tables “Show me the output across a range of one or two input variables” (sensitivity table) Recalculates the whole table on every change — slow on large models
Scenario Manager “Compare 3-5 named, multi-variable scenarios” (best case / base case / worst case) Doesn’t scale well past a handful of scenarios; not a substitute for proper Monte Carlo

Talking point: “For anything beyond 2-3 input variables or true probabilistic ranges, I’d push back on doing it in Excel’s Scenario Manager and instead build a proper simulation — even a simple Monte Carlo in Python — because Excel’s what-if tools are built for discrete scenario comparison, not distributional uncertainty.” This shows you know the tool’s edges, which is exactly what “when Excel isn’t the right tool” (section 5) is testing for.

4. Power Query / Power Pivot for ETL-Lite Workflows

This is the part of Excel that’s genuinely architect-relevant — it’s Excel’s ETL layer, and most “Excel is junior-level” critiques don’t account for it.

Power Query (the “E” and “T” — Extract & Transform):

  • A GUI-driven, repeatable, recorded transformation pipeline (each step is logged in the “Applied Steps” pane, similar conceptually to a dbt model’s lineage, just visual instead of code).
  • Connects to far more than local files — databases, APIs, folders of files, web pages — and refreshes rather than requiring manual re-paste, which is the single biggest upgrade over “copy-paste and reformat” Excel work.
  • Under the hood it’s generating M language code — you can view/edit it directly in the Advanced Editor, which is the bridge between “GUI user” and “understands what’s actually happening.”
// Example of what Power Query generates under the hood (M language)
let
    Source = Sql.Database("server", "database"),
    FilteredRows = Table.SelectRows(Source, each [order_date] >= #date(2026,1,1)),
    GroupedRows = Table.Group(FilteredRows, {"customer_id"}, {{"TotalSpend", each List.Sum([order_amount]), type number}})
in
    GroupedRows

Power Pivot (the “L” — Load, into a proper data model):

  • Lets you build relationships between multiple tables (like a mini star schema) without flattening everything into one giant sheet via VLOOKUP — this directly mirrors the fact/dimension table concept from the orientation module.
  • Introduces DAX (Data Analysis Expressions) — the formula language for calculated columns and measures, conceptually similar to SQL aggregates but evaluated in a different context model (row context vs. filter context), which is its own deep topic.
  • This is the same underlying engine that powers Power BI — so competency here transfers directly.

Talking point that ties it together: “Power Query and Power Pivot are what let Excel scale past ‘a single flat sheet of copy-pasted data’ into something closer to a lightweight, GUI-based ETL and semantic layer. When people say Excel doesn’t scale, they usually mean they’re not using these — they’re still doing manual VLOOKUP-and-paste workflows. The actual ceiling is elsewhere: version control, concurrency, and true big-data volume.”

5. When Excel Is the Right Tool vs. When It Isn’t

This is the section that demonstrates judgment, not just tool knowledge — exactly what’s being probed for in a senior interview.

Factor Excel is right when… Excel breaks down when…
Scale Dataset fits comfortably in memory (roughly under ~1M rows per sheet limit, but realistically sluggish well before that) Data is in the tens of millions of rows, or growing continuously — needs a real database/warehouse
Repeatability One-off or occasional analysis; a single analyst owns the file The same analysis needs to run daily/hourly on fresh data automatically — needs a pipeline (Airflow/dbt) feeding a BI tool, not a manually-refreshed workbook
Version control Single owner, low collaboration risk Multiple people editing concurrently — Excel has no real diffing, branching, or merge-conflict resolution; “Sales_Final_v3_USE_THIS_ONE.xlsx” is a well-known anti-pattern
Auditability Quick exploratory work where the trail doesn’t need to be reconstructed later Regulatory/financial reporting where every number needs a reproducible, reviewable lineage — formulas buried in cells are much harder to audit than version-controlled SQL/dbt code
Collaboration scale 1-3 people, low concurrent editing Org-wide self-serve reporting — needs a governed BI semantic layer (Power BI/Looker) with proper access control, not a shared file on a network drive
Computation complexity Standard aggregation, lookups, basic stats True statistical modeling, ML, simulation at scale — needs Python/R

The interview-ready synthesis line: “My rule of thumb: Excel is the right tool right up until the moment a spreadsheet needs to be trusted by someone other than its author — at that point you need version control, a single source of truth, and a refresh process that doesn’t depend on a person remembering to do it manually. That’s not a knock on Excel; it’s a description of where any single-file, single-owner tool hits its ceiling, and it’s the same reasoning that pushes a growing analytics team from spreadsheets toward a warehouse + BI tool stack.”

This is also a good moment to connect back to your DA-vs-DE-vs-BI module from earlier: the Excel → Power Query/Power Pivot → Power BI progression is literally the analyst’s path toward the BI Analyst boundary, and the analyst → dbt/warehouse path is the boundary toward Data Engineering.