← All Kits · Excel Kit · Article 0: the build order

Name Your Data So Your Formulas Stop Breaking

Article 3 of Excel for Business Analysts · One keystroke, and every formula after it reads like a sentence · Part of the Analyst Prep Kit

This article gives you the first thing to do with any data you load: press Ctrl+T and give the table a name. It takes five seconds, and it changes every formula you write afterwards. They read like sentences, they fill themselves down, and they keep working when new rows arrive.

Here's the difference in one line. Without a name, a formula says =IF(H2>=2000,1,0) and you have to go look up what column H is. With a name, the same formula says =IF([@TotalReviews]>=2000,1,0) and it explains itself.

Everything here comes from the same build as the rest of this series: an Excel dashboard over 82,956 Steam games, walked in the eight-step build order. Naming the data is step 1 of those eight, and it is the step people skip fastest, because nothing visibly breaks when you skip it. The breakage arrives later, addressed to whoever inherits the file.

What a Table is, and what the name buys you

Answer this first, from your own files: when you open a spreadsheet you built six months ago, how long does it take to work out what $H$2:$H$83000 was? That gap between the formula and its meaning is what a Table closes.

An Excel Table is a range that Excel has been told to treat as one object: a header row, columns with names, and a boundary that grows when data grows. The name is the part that matters. Once the range is called Games, a formula can say Games[TotalReviews], which is a phrase a person can read, instead of a coordinate a person has to decode.

A spreadsheet gets read far more often than it gets written, usually by someone who was not there when it was built, and often that someone is you in six months. Named columns are how the file explains itself when you are not around to.

Make one: Ctrl+T, then name it

1. Click any cell inside your data. One cell is enough. Excel finds the edges.
2. Press Ctrl+T. A dialog shows the range it found and a checkbox saying "My table has headers." If your first row is column names, leave it ticked. Click OK.
3. Name it. The ribbon now shows a Table Design tab. At the far left is the Table Name box, holding a default like Table1. Replace it with a real name: Games in this build. One word, starting with a letter, no spaces.

That's the whole move. The banding and the filter arrows you now see are cosmetic and removable. The name is the substance.

Table1 deserves one sentence of contempt: it is a name that names nothing, and a file with Table1, Table2 and Table3 is exactly as unreadable as one with no names at all. Name the thing for what it holds.

Reading structured references

Formulas over a Table use column names in brackets. Two shapes cover nearly everything, and the difference between them is one small symbol. Before the explanation: both shapes appear in the next code block. Predict what the @ changes.

Games[TotalReviews]      the whole column, all 82,956 values
[@TotalReviews]          this row's value, in the row the formula sits in

The @ means "this row." A whole-column reference feeds the formulas that measure, like COUNTIF(Games[Segment], "Loved, hidden") from article 1. An @ reference feeds the formulas that label one row at a time, like the Segment column itself.

You rarely type the brackets by hand. Click a cell while writing a formula and Excel writes the structured reference for you. Reading them is the skill, and now you can.

The three behaviors that stop formulas breaking

New rows join the table on their own. Type or paste below the last row and the boundary extends. Every formula pointing at Games[Price] now includes the new rows, without being touched. The classic breakage this kills: a SUM pinned to H2:H83000 that silently ignores the rows someone added in March.

Calculated columns fill themselves down. Type one formula in a new column and Excel fills all 82,956 rows the moment you press Enter. That is how the Segment column in this build labelled the whole file in one keystroke. No drag, no double-click on the fill handle, no last-hundred-rows-missed.

The reference moves with the data. Insert a column to the left and H2 now points at the wrong field, quietly. [@TotalReviews] points at the column called TotalReviews wherever it sits. Renamed reality breaks coordinates. It does not break names.

Say out loud which of the three has actually bitten you. Nearly everyone has one, and it is usually the pinned range that stopped including new rows.

Before and after, same formula

The IsProven flag from the real build, both ways:

Picture the workbook you use most. Read one of its formulas as a stranger would. If the stranger has to open the data sheet to understand it, that formula is a coordinate, not a sentence.

The edge cases worth knowing

Column names with spaces need their own brackets. Games[Total Reviews] works, but every reference gets noisier. Single-word or CamelCase headers keep formulas clean, which is why this build's columns are TotalReviews and PctPositive.

Merged cells and Tables do not mix. Excel refuses to make a Table over merged cells. This is the Table doing you a favor: merged cells break sorting, filtering and pivots too, and analysis data should not contain them.

The total row is a trap dressed as a convenience. Table Design offers a total row at the bottom. It moves every time the data grows, which is exactly where a KPI cell must not live. Keep your headline numbers outside the table, in check cells, per article 2.

Tables reach across sheets by name. A formula on the dashboard sheet can say COUNTIF(Games[Segment], "Loved, hidden") with no data!$D$2:$D$83000 anywhere in it. The name travels; the coordinates never had to exist.

Why this works

Two findings, one about spreadsheets and one about who reads them.

Spreadsheets are programs written by people who do not think of themselves as programmers, and they are read and modified collaboratively far more than their builders expect. Nardi and Miller's field study found spreadsheet work is routinely distributed across people, with one person's formulas debugged and extended by others (Nardi & Miller, 1991, International Journal of Man-Machine Studies, 34(2), 161-184). A formula that names its inputs is one a second person can pick up. A coordinate formula asks every reader to rebuild the writer's memory first.

And errors ride on exactly that gap. Panko's review of spreadsheet audits found errors in a few percent of cells as the normal case, not the exception (Panko, 1998, Journal of Organizational and End User Computing, 10(2), 15-21). The pinned-range and shifted-column mistakes that structured references remove are not exotic. They are the ordinary way ordinary files go wrong.

Run it on your own file

1. Open the file you touch most. Not a fresh one. The one with history.
2. Click inside the main data, press Ctrl+T, confirm the headers, and name it for what it holds.
3. Read the existing formulas. They still work, and they still say H2. Old references do not convert themselves.
4. Write the next formula the new way. Click cells and let Excel produce the bracketed names. Read it back as a sentence before you press Enter.
5. Add ten fake rows at the bottom, then delete them. Watch the boundary grow and the column formulas fill. Now you trust the behavior because you saw it, not because a page told you.

Converting every old workbook is not worth an afternoon. Name the data in files you are still building, and let the archived ones rest.

A cheat sheet

References

• Nardi, B. A., & Miller, J. R. (1991). Twinkling lights and nested loops: Distributed problem solving and spreadsheet development. International Journal of Man-Machine Studies, 34(2), 161-184.
• Panko, R. R. (1998). What we know about spreadsheet errors. Journal of Organizational and End User Computing, 10(2), 15-21.

How long did your answer at the top take, the one about decoding $H$2:$H$83000 in a six-month-old file? That time is what the name deletes.