Topic 4 — Spreadsheets — the analyst's first tool

Goal: Know the spreadsheet skills that handle most real analysis work.

Lesson 4.1 — The tool you already half-know

On Nadia's third day at Perch, Marcus from marketing dropped by her desk with a question: "How are sofa sales doing this month versus last?" She braced for some intimidating database. Priya, her mentor, slid a Google Sheet across the screen instead. "Start here. Half of what we do never leaves a spreadsheet."

That surprised Nadia, who'd assumed real analysts only touched "real tech." But it's true at most companies. Spreadsheets — Excel or Google Sheets — still do a large share of real-world analysis: the quick sanity check, the one-off ad-hoc question, the lightweight weekly report nobody wants to build a pipeline for. They're also the thing interviewers reach for most, because they reveal how you think with data in five minutes.

Nadia had an edge here. In her old retail-ops job she'd lived in spreadsheets, building inventory and weekly sales reports by hand. So the grid felt like home. A spreadsheet is a grid of cells, each named by its column letter and row number — the cell where column B meets row 5 is B5. You put data in cells, then write formulas that calculate from them: type =B5*0.9 and the cell shows that result, recomputing the instant B5 changes.

The grid plus the formula is the whole foundation. Everything else in this topic is a smarter formula.

What Nadia hadn't done before was use a spreadsheet to ask questions rather than just store numbers. That shift — from list-keeper to analysis tool — is what the next four lessons are about.

Lesson 4.2 — Five functions that summarize almost anything

Marcus wanted a number: total sofa revenue this month. Nadia had a column of 600 order amounts and no desire to add them by hand.

She typed =SUM(D2:D601) and had her answer in a second. That's the core aggregation family, and a handful of functions in it cover most summaries you'll ever need:

• SUM — add up a column of numbers (total revenue).
• AVERAGE — the mean (average order value).
• COUNT — how many numeric cells have values; COUNTA counts non-empty cells of any type, which matters when you're counting things like names or statuses.
• MIN and MAX — the smallest and largest value (cheapest and priciest order).

Five functions, and Nadia could already describe a dataset: how much, how many, the average, the range. The trick is reading a question — "how many orders?" "what's the typical basket?" — and knowing which function answers it, not memorizing the functions themselves. COUNT versus COUNTA is the classic beginner stumble: if a column holds text like Shipped / Returned, COUNT reports zero because none of it is a number, while COUNTA gives you the real total.

Lesson 4.3 — The conditional family does the real work

Then Marcus refined the question, the way customers always do: "Actually — just the North region, and just sofas."

A plain SUM can't do that; it adds everything. This is where the conditional family earns its keep, and it's the genuine workhorse of spreadsheet analysis. Each core function has a conditional twin that only counts rows meeting a test:

• SUMIF / SUMIFS — sum values only where a condition holds.
• COUNTIF / COUNTIFS — count rows that match.
• AVERAGEIF / AVERAGEIFS — average the matching rows.

To total North-region sales, Nadia wrote =SUMIF(B2:B601, "North", D2:D601): look down the region column, and wherever it says North, add the matching amount. The plural ...IFS versions let you stack conditions — North and sofa and this month — which is how almost every real request actually arrives.

Two more pieces round out the everyday toolkit. IF labels or branches a value: =IF(D2>500, "high", "low") tags each order. You can nest them for more bands, though past two or three it gets hard to read. And IFERROR is the safety net — wrap a formula in =IFERROR(yourformula, 0) and a broken calculation shows 0 instead of a screen full of red #DIV/0! errors. Priya's rule, which Nadia adopted fast:

Define the metric in words before you pick the formula. "Average order value for repeat customers in May" tells you it's AVERAGEIFS before you touch a key.

Lesson 4.4 — Lookups, and cleaning up the mess

Nadia's sales sheet listed customer_id, not names. Marcus wanted names. The matching names lived in a second sheet. She needed to reach across and pull each one in.

That's a lookup. VLOOKUP is the old standby — give it an ID, a table to search, and which column to return, and it finds the match. The newer XLOOKUP does the same job more flexibly: it can look both left and right (VLOOKUP only searches rightward), and it takes a built-in "not found" value, so you skip the IFERROR wrapper VLOOKUP needs to hide an #N/A. XLOOKUP needs Microsoft 365, Excel 2021, or Excel for the web; on older Excel you'll see VLOOKUP, or the classic INDEX/MATCH combo that does the same thing.

A lookup is the spreadsheet version of a SQL JOIN — stitch a value from one table onto another by a shared key. Nadia clocked that early, and it made joins far less scary when she met them in Topic 5.

Real data also arrives dirty, and a few text and date helpers clean it without retyping a thing. TRIM strips stray spaces (the reason " North" won't match "North"). LEFT, RIGHT, and MID slice characters out of a cell. CONCAT (or just &) glues fields together — =A2&" "&B2 makes a full name. UPPER, LOWER, and PROPER fix casing. For dates, TODAY() gives the current date, and DATEDIF measures the gap between two dates — handy for "days since last order," exactly the repeat-purchase question Perch cares about.

Lesson 4.5 — PivotTables, and knowing when you've outgrown the grid

A week in, Dana, the VP of Ops, wanted total revenue by region and by product category. Nadia's hand drifted toward a thicket of SUMIFS formulas. Priya stopped her. "Pivot it."

The PivotTable is the single most powerful thing in a spreadsheet, and it writes no formula at all. You point it at your table, drag region into Rows, category into Columns, and amount into Values — and a full cross-tab appears, totals down both edges. Drag month in and it re-slices instantly. Because it answers "what's the total or average of X, broken down by Y?" — the most common analytical question there is — a PivotTable plus a quick chart (Topic 9) can be a complete mini-analysis in minutes.

It paid off the same afternoon: Dana asked the follow-up "what about by month too?" and Nadia just dragged a field and turned the screen around.

But spreadsheets have a ceiling, and recognizing it is itself an analyst skill. They shine on smaller datasets and quick work. Push past a few hundred thousand rows and they crawl, formulas break silently, and one mis-sorted column corrupts everything. Excel's hard limit is 1,048,576 rows (and 16,384 columns) — but the practical ceiling is well below that; things get painful in the hundreds of thousands. Perch's full order history runs into millions of rows and lives in the warehouse. When Nadia hit that wall, the answer wasn't a faster laptop. It was SQL — which is exactly where Topic 5 goes.

Worked example — Nadia answers Marcus end to end

Marcus's real ask: "For repeat customers in the North region, what's our average order value this month — and is it higher than for first-time buyers?" One sentence, the whole topic inside it.

1. Clean first. The region column has stray spaces, so North-with-a-space silently won't match. Nadia wraps it: =TRIM(B2) down a helper column. (Lesson 4.4.)
2. Define the metric in words. "Average order amount, where region = North and customer type = repeat, in May." Said aloud, the function names itself. (Lesson 4.3.)
3. Compute it. =AVERAGEIFS(amount, region, "North", type, "repeat", month, "May") — multiple conditions, one number. She runs it again with "first-time" to get the comparison. (Lesson 4.3.)
4. Pull names for the examples Dana will ask about. =XLOOKUP(A2, customers!A:A, customers!B:B, "unknown") grabs each customer's name from the other sheet. (Lesson 4.4.)
5. See the whole pattern. A PivotTable with customer type in Rows and amount (set to Average) in Values shows repeat versus first-time side by side, no formula needed. (Lesson 4.5.)

Answer for Dana, one sentence: "Repeat customers in the North spend about 30% more per order than first-timers this month — worth pushing repeat-purchase ads there." No database, no code. A spreadsheet did it, and Nadia chose the right tool at every step.