Spreadsheet Data Structure: Rules for Clean Data

A spreadsheet is structured correctly when the data on a tab forms one single rectangle: one header row of unique labels, one record per row, one value per cell, no blank rows or columns inside it, no merged cells, no totals or notes sitting in the middle of it, and numbers and dates stored as numbers and dates rather than as text. That is the whole rule set. It matters because structure is what sorting, filtering, pivot tables, table formatting and CSV exports all read before they do anything. A sheet that breaks these rules cannot be reliably formatted by you, by Excel, or by any tool, because none of them can tell where the data starts and stops.

Most guidance on this subject is written for researchers preparing data for analysis, or for auditors checking a model. Both make it sound like a compliance exercise, which is why office workers skip it. The practical framing is simpler. Structure decides what appearance is even available to you. You cannot apply banded rows to something that is not a range. You cannot right-align a currency column that contains the text "$1,200 (est.)". Structure first, appearance second, and the second part gets easy once the first is right.

Why does structure decide what formatting is possible?

Every feature that makes a sheet look finished works by detecting a rectangle. Click a cell and press the sort button, and the program expands outward from that cell until it hits a blank row and a blank column, and calls whatever it found "your data". Insert a table, and it does the same thing and then asks you to confirm the range. Build a pivot table, and it reads the first row as field names.

That detection step is the hinge. Get it right and one click gives you a header row that filters, banded rows, a totals row that respects the filter, and number formats that apply down the whole column. Get it wrong and the same click grabs half your data, or grabs your title cell as a field name, and you spend the next twenty minutes fixing something that was never broken in the first place.

Microsoft's own guidelines for organizing and formatting data on a worksheet say this plainly: avoid blank rows and columns inside a range, keep unrelated data separated from it by at least one blank row and column, and use borders rather than blank rows to divide labels from data. The academic version is Hadley Wickham's Tidy Data paper, which compresses the same idea into three lines: each variable forms a column, each observation forms a row, each type of observational unit forms a table. Office spreadsheets and research datasets disagree about almost everything else and agree completely about this.

The eight-rule audit

Run this against any sheet you are about to format or send. Each row is pass or fail, and a fail is not a style opinion. It has a visible symptom and a specific thing that stops working.

#	Rule	Pass looks like	Fail symptom you can see	What stops working
1	One table per tab	The tab holds a single rectangle of data and nothing else	Two or three blocks stacked down the sheet, or side by side	Sort and filter grab the wrong block. Table formatting cannot cover both
2	One header row, unique labels	Row 1 is a single row of distinct, non-blank column names	Two stacked header rows, merged spanning headers, blank label cells	Pivot tables refuse the range or name fields "Column1". Filters point at the wrong row
3	One record per row	Every row is one order, one employee, one invoice	One person occupying three rows, or one row holding January through December	Counting rows stops meaning counting things. Pivot totals double count
4	One value per cell	Smith and Jane in separate columns, 12 and kg separate	Jane Smith, 12 kg, London / Paris, 2025-Q3 (revised)	Sorting by surname is impossible. Arithmetic on the number is impossible
5	No blank rows or columns inside the range	The rectangle is unbroken from the header to the last record	A blank row used as a visual gap between months or teams	Range detection stops at the gap. Pivot tables and filters see only the top section
6	No merged cells inside the range	Every cell in the range is its own cell	A category label merged across four rows	Sorting is blocked outright. Copy, paste and fill behave unpredictably
7	Numbers and dates stored as their own type	Right-aligned by default, usable in a formula	Left-aligned numbers, a green corner triangle, dates that will not sort chronologically	SUM returns zero. Date filters offer text choices instead of a calendar
8	No totals, notes or titles inside the range	Totals sit below with a blank row between, or in a table's own totals row	A grand total row in the middle, a note column beside the data	Totals get sorted into the data. Filtering hides the note, or the total lands inside the filter

Eight rules, and in practice one of them causes most of the damage.

What is one row per record, really?

Rule 3 is the one people nod at and then break, because "record" is doing quiet work in that sentence. A record is one instance of the thing the sheet is about. If the sheet is about orders, one row is one order. If it is about employees, one row is one employee. The test is whether you can say what a single row represents in four words. If the answer needs a conjunction, the structure is wrong.

The common failure is the wide layout: one row per person, with twelve columns named Jan, Feb, Mar and so on. That is a report, and it is a perfectly good report. It is a bad data range, because the month is now a column heading instead of a value, and you cannot filter by month, sort by month, or add a thirteenth month without redesigning the sheet. Wickham's paper calls this exact pattern out as the most common form of messy data, and the fix is the same in a spreadsheet as in a statistics package: the thing that varies becomes a column of values, not a row of headings.

The other failure is the tall one: a person spread down three rows because their address needed the space. Now a row count is not a headcount, a filter on department returns partial people, and any per-person calculation has to guess where one person ends. Give the address its own column and let the column be wide.

Why do blank spacer rows cause so much damage?

Because the blank row is the very thing every range detector treats as the edge of your data. When you leave a gap between March and April to make the sheet easier to read, you have told the program that your data ended in March. Sort will only sort March. Filter will only offer March values. A pivot table built on that range will stop at March and produce numbers that are internally consistent and completely wrong, which is worse than an error message.

Blank columns do the same thing sideways, and there is a second trap with them. Data placed to the left or right of a range can be hidden when you filter, because filtering hides whole rows. A note column beside the data disappears along with the rows it explains.

The visual separation you wanted is still available. It just has to be formatting rather than emptiness: a border under the last row of each month, a subtle fill on the month column, or an actual grouping. Every one of those survives sorting and filtering, and a blank row does not. If you want the sections to stay visually distinct after the reader sorts the sheet, formatting is the only version that holds.

Merged cells belong to this same family of structural damage, and they are common enough to deserve their own treatment. The short version is that a merged cell inside a data range blocks sorting outright and makes every automatic range detection unreliable. There is a proper replacement for the look that merging gives you, covered in what to use instead of merged cells.

Where should units, notes and totals live?

Units go in the column header, never in the cell. Weight (kg) as a header with 12 in the cell is structured data. 12 kg in the cell is a text string that looks like a number, and it will not sum, will not sort numerically, and will not accept a number format. The same applies to currency symbols, percent signs and thousands separators typed by hand. Those are all display decisions, and display decisions belong to the number format rather than to the stored value.

Notes go in their own column inside the range, or on a separate tab, and never floating in the margin beside the data. A note parked two columns to the right of the range is invisible to anyone who filters the sheet and easy to destroy for anyone who inserts a column.

Totals have two correct homes. Either they sit below the data with a genuine blank row between them and the last record, so range detection stops before reaching them, or they live in a real table's totals row. That second option is worth knowing about: a table's totals row uses SUBTOTAL rather than SUM, which means it ignores rows hidden by a filter and recalculates as the reader filters. A hand-typed SUM at the bottom of a filtered list keeps showing the unfiltered figure, and nobody notices until a number gets quoted in a meeting.

What totals must never be is a row in the middle of the data. Sort the sheet once and the total row travels with everything else, lands between two records, and gets counted as data by the next person who builds anything on top of it.

When is a second tab the right answer?

When the tab is trying to be two things at once. The clearest signal is that you can point at two different row meanings on one sheet. A tab holding transactions and, further down, a summary by category, is two tables. Split it: transactions on one tab, summary on another, with the summary reading from the transactions.

The same applies to reference data. A list of products with prices and a list of orders are different observational units, and keeping each in its own table is the tidy-data rule and the database-normalisation rule at the same time. A single fact should be recorded in one place, so that when a price changes you change one cell rather than hunting through six hundred rows.

Two tables side by side on one tab is technically survivable if you leave a full blank column and row between them, which is what Microsoft's guidance recommends when you genuinely must. It is still worse than two tabs, because every sort, filter and format action becomes a question of which block you clicked in first. Tab organisation is cheap. Use it.

The exception is the deliberately built report, where the tab is a presentation surface rather than a data range. Those can have titles, merged headings, subtotal blocks and spacing, because nobody is going to pivot them. Keep the raw range on its own tab and let the report tab be a report. The rules in Excel table design best practices cover what the report tab should then look like.

How do you restructure a sheet without redoing the data?

Non-destructively, in this order, and always on a copy.

1. Duplicate the tab first. Everything below is reversible only if the original still exists.
2. Delete the decoration, not the data. Title rows above the header, blank spacer rows inside the range, note cells in the margin. Move them to a notes column or a separate tab rather than deleting the content.
3. Collapse the header to one row. If you have two stacked header rows, combine them into one label per column: Q1 Revenue rather than a merged Q1 above a Revenue.
4. Unmerge, then refill. Unmerging leaves the value in the top-left cell only, so the other cells need the value copied down. This is a judgement call about what the label actually applied to, which is why it is a manual step.
5. Split combined cells into columns. Text to Columns handles the mechanical cases such as Jane Smith or 12 kg.
6. Convert text numbers and text dates to real values. Left alignment is the tell, and the fix is a conversion rather than a format change. Applying a date format to a text string changes nothing.
7. Move totals out. Below the range with a blank row, or into a table totals row.
8. Re-run the eight-rule audit. Then, and only then, format.

Step 6 and the whitespace and header cleanups in steps 2 and 3 are exactly what the spreadsheet cleaner does automatically. It fixes numbers stored as text, trims stray leading and trailing spaces, which affect sorting and searching in ways that are invisible on screen, and normalises header rows. What it deliberately does not do is unmerge cells or split a multi-table tab into separate tabs. Those are structural decisions with a right answer that depends on what the data means, and a tool that guesses at them produces a confidently wrong file. Those stay with you. Everything mechanical does not.

If the sheet is going out to someone today, the pre-send pass in the messy spreadsheet checklist is the faster route. Structure work is the version you do when the sheet is going to be reused.

One last reason to care, for anyone whose file ends its life as a CSV export. RFC 4180, the document that describes the CSV format, defines a file as one record per line with an optional single header line carrying the field names, and every record holding the same number of fields. A sheet that already obeys the eight rules maps onto that exactly. A sheet with merged cells, stacked headers and a total row in the middle does not, and the export will quietly flatten all of it into something the receiving system reads as garbage.

FAQ

Should totals be inside the data range?

No. Put them below the last record with a blank row in between, or use a real table's totals row. A total inside the range gets sorted into the data, counted by pivot tables, and swept up by any formula that reads the whole column. The table totals row is the better of the two options because it uses SUBTOTAL, so it recalculates when the reader filters instead of continuing to show the unfiltered figure.

Can I have two tables on one sheet?

You can, if you separate them by at least one fully blank row and one fully blank column, which is what Microsoft's guidance recommends for unrelated ranges. But it makes every sort, filter and format action depend on which block you clicked in first, and it makes a table style impossible to apply cleanly. Two tabs cost nothing and remove the ambiguity. Reserve the two-blocks-on-one-tab layout for a report tab that nobody is going to pivot.

Why does my pivot table stop at row 40?

Almost always because there is a blank row at 41, or a blank column inside the range, and the pivot source range stopped there. Check the source range on the pivot table, then look for the gap. The other frequent cause is a header problem: a pivot table needs a single row of unique, non-blank labels, so stacked header rows or merged spanning headers will either be rejected or produce fields named Column1. Fix the gap or the header, then refresh rather than rebuilding.

Is it OK to put units in the cell with the number?

No. 12 kg is text, not a quantity. It will not sum, it will not sort numerically, it will not accept a number format, and it will sort alphabetically so that 100 kg lands before 12 kg. Put the unit in the column header as Weight (kg) and keep the cell as a plain 12. The same rule covers currency symbols, percent signs and thousands separators: those belong to the number format, which changes how the value is displayed without changing what is stored.