Excel that holds up under pressure

Every branch manager at Sahara Mart keeps a version of the same spreadsheet, and every version breaks the same way. Someone inserts a row for a new product halfway down the list, and a SUM formula three rows above quietly stops including it. Nobody notices for two weeks, because the number still looks plausible — it's just wrong.

This happens because the sheet was built around ranges typed by hand — =SUM(B2:B47) — rather than around a structure that grows with the data. The fix is not a cleverer formula. It's building the sheet differently from the start.

Tables, not ranges

Turn any list of rows into an Excel Table (Insert > Table, or Ctrl+T) before you write a single formula against it. A Table gives the range a name, auto-expands when you add a row anywhere inside it — including the bottom — and every formula referencing it grows automatically. =SUM(SalesTable[Revenue]) will always sum every row of that column, whether the table has 40 rows or 4,000.

A named Table is not a formatting choice. It is the single change that prevents the most common silent error in branch reporting.

One row, one transaction — never a summary row mixed into the data

The second recurring fault in Sahara Mart's sheets: someone adds a "TOTAL" row at the bottom of the same table that holds the daily transactions. The moment that happens, any SUM or AVERAGE run over the whole table double-counts, because the total row is itself being summed. Keep raw data and summaries on separate sheets, always. The raw data sheet should contain nothing but rows of the same shape, one per transaction or one per day — no headers halfway down, no blank separator rows, no totals mixed in.

Sahara Mart's product list lives in one sheet; each branch's daily sales live in another, referencing products by SKU code. Somewhere, someone needs to look up a product's category or price from its SKU. This is the single most common operation in the whole course, and the most common way to quietly break a workbook.

Why VLOOKUP is the wrong default

=VLOOKUP(A2, Products!A:D, 3, FALSE) works — until someone inserts a new column into the Products sheet between columns A and D. The "3" was counting columns from the left edge of the range, and that count is now wrong, silently returning the wrong value rather than an error. This is worse than a formula that breaks loudly, because nobody notices.

Where XLOOKUP isn't available (some older Excel builds still in use at Sahara Mart's branch offices), INDEX/MATCH does the same job: =INDEX(Products!D:D, MATCH(A2, Products!A:A, 0)). Slightly more to type, same immunity to inserted columns.

Handling the "not found" case honestly

A lookup for a SKU that doesn't exist in the Products sheet — a typo, a discontinued item still showing in old sales data — returns #N/A by default. Wrapping it in IFERROR to hide that error is tempting and usually wrong: it silently converts a real data problem into a blank cell that looks like a legitimate zero. Better: =IFERROR(XLOOKUP(...), "CHECK SKU") — something visible enough that it gets caught in review, not something that disappears into the summary numbers unnoticed.

"What was Lekki branch's revenue from the beverages category last Tuesday" is a question with three conditions stacked on top of each other — branch, category, date. This is exactly what SUMIFS is built for, and it is the formula you will reach for more than any other in this course.

COUNTIFS works identically but counts matching rows instead of summing a column — useful for "how many transactions" rather than "how much revenue." AVERAGEIFS does the same for averages. All three share the exact same argument pattern, so learning SUMIFS well teaches you the other two for free.

The trap: an OR condition dressed up as if it were an AND

"Revenue from Lekki or Yaba branches" is not a job for a single SUMIFS with a branch condition — SUMIFS can't express OR inside one condition slot directly. The reliable pattern is to sum two SUMIFS calls: =SUMIFS(...,"Lekki",...) + SUMIFS(...,"Yaba",...). New analysts often try to jam both branch names into one condition and get a silent zero or an error, because SUMIFS is quietly expecting a single exact match per criteria pair, not a list.

The fastest way to build a useless pivot table is to drag every field you have into it "to see what's there." A pivot table should exist because you already have a question — module 1's discipline applies here directly — and its shape follows from that question, not the other way round.

Start from the question, not the field list

Chidi asks: "which branches are dragging down this month's average basket size?" That question names exactly what belongs where: Branch in Rows, Average of Basket Size in Values, month filtered in the filter area. Nothing else. A pivot with Branch, Category, Day of Week, Payment Method and three value fields all dropped in at once might technically contain the answer somewhere inside it, but it takes Chidi longer to find it than it would take him to ask you directly — which defeats the purpose of building it at all.

If a second question comes up, build a second pivot. Pivot tables are cheap to create and nearly free to delete — there's no cost to having several small, sharp ones instead of one sprawling one.

Every Monday, each of Sahara Mart's nine branches exports last week's till data as a CSV and emails it in. Every one of those nine CSVs is formatted slightly differently — one branch's till software puts the date as DD/MM/YYYY, another as text, a third leaves trailing spaces on every branch name. Manually cleaning nine files every week is not a sustainable habit; it is a guaranteed future mistake waiting for a busy Monday.

Build the cleaning step once, in Power Query

Power Query (Data > Get Data > From File) lets you record a sequence of cleaning steps — trim whitespace, fix the date column's type, rename columns to match your standard, filter out any test transactions — once, against one sample file. Every subsequent week, you point the same query at the new file and click Refresh; every step replays automatically, in order, on the new data.

The point of Power Query is not that it's faster once. It's that it's identical every single time, which a person redoing the same five steps by hand every Monday morning at 8am, tired, will not be.

A workbook that never shows an error is not necessarily a correct workbook — it might just be one where errors have been hidden rather than fixed. Two habits keep this from happening at Sahara Mart.

Data validation stops bad input at the source

Any cell a human types into directly — a branch manager entering a manual adjustment, say — should have Data Validation restricting what can go in it: a dropdown list of the nine valid branch names, rather than free text that will inevitably drift into "Lekki", "lekki", and "Lekki Branch" as three different values that don't group together in any pivot table.

An audit trail: who changed what, and why

When a number is adjusted manually — a correction for a till error, say — that adjustment should be visible, not buried inside the original figure. Keep a separate "Adjustments" column or sheet: original value, adjusted value, who made the change, and one line of reasoning. Six months later, when someone asks why April's Yaba figure looks unusual, the answer is one lookup away instead of a mystery.

A workbook only you can operate is a workbook that breaks the moment you're on leave. Chidi needs to be able to open any report you build, understand roughly how it works, and refresh it himself in a pinch — even though he didn't write a single formula in it.

A minimal handover standard

• A README tab, first in the workbook. Three lines: what this workbook does, where the source data comes from, and what to do if a number looks wrong.
• Colour-code by function. Blue text for numbers a person can safely type over (inputs); black for formulas nobody should touch. This single convention, used consistently, tells anyone at a glance what's safe to edit.
• Name the sheets for what they contain, not for how you built them. "Branch Summary" is useful to a stranger; "Sheet3 (working)" is not.

None of this is about making the workbook prettier. It's about making sure the analysis survives contact with someone other than you — including future-you, six months from now, who will have forgotten exactly why a formula was built the way it was.