What structured data is
Structured data is information laid out in rows and columns, where every value has a type and a meaning. It lives in tables, spreadsheets and databases, as opposed to emails, documents and notes.
These two files hold much the same information. Ask them both a question and see which one can answer.
Free text in a column. Nothing here is a column, so nothing can be sorted, filtered or counted.
One person per row, one attribute per column. Every question has an answer.
Rows and columns
A column is one attribute: a name, a date, an amount. A row is one thing: a customer, an order, a payment. Hold to that and anything can read the table, from a spreadsheet formula to Mira.
Point at a column letter, or a row number.
What makes it good
Rows and columns are not enough on their own. A sheet can look structured and still be unusable if the values inside it do not follow rules. Six of them do most of the work. Each sheet below has one mistake on it. Flip the switch to see it fixed.
One fact per cell
Each cell holds exactly one piece of information. If you need a name and a city, that is two columns.
ABC1customeramount2John Smith / London1200.003Jane Doe / NYC640.004Acme Corp (Berlin)310.00A name and a city in one cell. Nothing can group these by city.
Two facts, two columns.
Consistent types
Every value in a column is the same kind of thing: all dates, all numbers, or all text.
AB1order_dateamount215 March249.0032026-03-16£89.504last Tuesday412Three ways of writing a date. Sorting them puts March after Tuesday.
One form for every date, and a number is only a number.
Stable column names
A column's name is the contract between your data and everything that reads it. It should not change between exports.
ABC1Date (Q1)Column FRev £ (new)22026-03-15West249.0032026-03-16East89.5042026-03-17West412.00A quarter in the name, a name that means nothing, and a name that will change next month.
Plain names that say what the column holds, and keep saying it.
One table per sheet
Each sheet holds one table, starting at row 1, column A. Two sets of data belong on two sheets.
ABCD1idamountTotal21001249.00750.503100289.5041003412.00Sheet1A total floating beside the data. Anything reading the sheet takes it for a fourth order.
The total has a sheet of its own.
No hidden meaning
Colour, merged cells and notes are invisible to anything reading the sheet. If it matters, it belongs in a column.
ABCD1Invoices, March2invoicecustomeramount3INV-31Alice249.004INV-32Bob89.50Red means overdue and green means paid, but only to a person. The merged title pushes the headers to row 2.
The meaning is a column now, and the headers are on row 1.
Honest gaps
A missing value is genuinely empty. Zero means they paid nothing. Empty means you do not know. Those are different facts.
AB1customerrevenue2Alice1200.003Bob–4CarolN/A5David0A dash, a word and a zero, all standing in for “we don't know”. The zero goes into every average.
Unknown is empty. David really did pay nothing, so his zero stays.
The same data, twice
An export as it arrived from the old system. It looks fine, it will give a wrong answer, and it will not say so. Four fixes turn it into something that can be asked a question.
As exported. This is how it left the old system. It looks fine. It will give a wrong answer.
One table per sheet
The most common problem is not bad data inside a table. It is several tables on one sheet: a summary in the corner, a lookup pasted alongside, two unrelated sets stacked with a blank row between them. To a person it looks organised. To anything reading it, it is one table with holes in it.
- Three tables on one sheet, and nothing on the sheet marks where one ends
- A blank row standing in for a boundary, which nothing can read
- Two rows of column names, at row 2 and row 8, for two different things
Why it matters
Structure is not housekeeping. It decides what can be asked of the data at all, and whether the answer can be trusted when it comes back. The dangerous mistakes are the ones that never throw an error.
Bob’s revenue is not known. Record it as
Average 600.00. The zero counts as a sale of nothing and pulls the average down. Nothing flags it.
The average in B7 is =AVERAGE(B2:B5), and it never changed. Only what the cells said did.
What good structure buys you
Filter, sort and group with nothing to clean first
When every column holds one kind of thing, a question about a date or a region can be answered straight away.
Tables join together reliably
A stable ID column is what ties a customer to their orders, and an order to its returns.
Answers come back right
Mira reads what the data says, not what you meant by it. A zero that meant unknown goes into the average as zero.
Mistakes show up instead of hiding
Messy data rarely throws an error. It gives a slightly wrong answer that looks perfectly fine.
How tables connect
Your data will not sit in one table. What makes many tables usable is a column they share, so a question can run across them.
The customer_id column is in both files. That is the thread from each order to the person who placed it. Point at a row on either side.
Before you connect anything
Six checks. They take an afternoon and they head off most of what goes wrong later.