Before you analyse it, find out what you have

The four questions before any analysis

Somebody sends you a spreadsheet and asks what it shows. Before you ask any tool anything, four questions:

1. Who produced this, and from what system?
2. What period does it cover, and is the last period complete?
3. What is one row?
4. What does each column actually mean?

Every serious error in office data analysis is an answer to one of those that nobody checked. AI makes it much faster to produce an answer, and does nothing whatever to make these four questions less necessary. It is very good at giving you a confident mean of a column that should never have been averaged.

What is one row

Get this wrong and every total is wrong by a multiple.

A file exported as "orders" that has been joined to line items has one row per line item, not per order. Sum the order value column and you have counted a three-item order three times. The total looks big and plausible; nobody notices; it goes in a board paper.

The check takes ten seconds: count the rows, count the distinct order numbers. If they differ, one row is not one order, and you need to know what it is before anything else happens.

Missing is not zero, and zero is not missing

Systems represent "no value" in ways designed for the system, not for you:

• A blank cell.
• A zero.
• -999, 9999, 1900-01-01 — sentinel values from older systems, chosen precisely because they were implausible.
• The four-character string N/A, which is text and will quietly break a numeric column.

An average over a column containing -999 is not slightly wrong. It is arbitrary. And it will be reported to three decimal places.

Ask for the distinct values before you ask for anything else:

For each column, if there are fewer than 20 distinct values, list them with counts. Otherwise give the minimum, maximum, the count of blanks, and any value appearing more than 5 per cent of the time. Flag anything that looks like a placeholder rather than a real measurement.

Read that output yourself. It is the single highest-value thing you can do with a new dataset, and it takes a minute.

Dates are the worst offenders

03/04/2026 is 3 April in most of the world and 4 March in the United States. A file assembled from two sources can contain both conventions in the same column, and roughly two-thirds of the rows will convert without error — the ones where the day is 13 or higher fail, and the rest are silently swapped.

Spreadsheets make this worse by coercing things that look like dates. Part number SEPT2 becomes a date. 1-3 becomes January the 3rd. This is not hypothetical: in 2020 the body responsible for naming human genes formally renamed several of them because Excel kept converting the old symbols into dates, and enough published papers had been affected to make renaming the genes the easier fix.

Ask for dates in ISO form — YYYY-MM-DD — everywhere, in every prompt, in every extraction. It is unambiguous and it sorts correctly as text.

Units, scale and the same word meaning two things

• Is the revenue column in units, thousands or millions? Mixed files exist.
• Is the currency the same on every row?
• Is the percentage stored as 0.15 or 15? Both appear, sometimes in the same file.
• Does "active customer" mean the same in the CRM export and the billing export? Almost never. One means "not cancelled", the other means "billed this month".

Trailing spaces and capitalisation break joins invisibly: "Acme " and "Acme" are two customers to a computer and one to you, which shows up as a suspiciously long list of customers with one order each.

Interrogate, do not analyse

The productive use of AI here is not "what does this data show". It is "help me find out what I have got":

Here are the first 30 rows and the column headers. For each column: what do you think it means, what type is it, and what would make you suspicious about it? Do not calculate anything. Where you are guessing, say so.

Then you check the guesses against the person who produced the file. That conversation — five minutes with the person who ran the export — resolves more than any amount of analysis, and the model's list of suspicions is a good agenda for it.

Free tools that do this properly

• OpenRefine is free, runs locally, and was built for exactly this job: clustering near-duplicate values, spotting inconsistent formats, showing the distribution of every column. If you regularly receive other people's data, learn this one.
• csvkit's csvstat gives you types, nulls, minimums, maximums and distinct counts for every column in one command.
• LibreOffice Calc and Google Sheets both do the ten-second checks: COUNTA, COUNTBLANK, SUMPRODUCT with a condition, and a pivot table of distinct values.
• Python with pandas, and df.describe(include='all') plus df.isna().sum(), if you have it.

None of these will tell you what a column means. That still requires asking a person. What they do is show you where to ask.