At month’s end, a product usage survey has 100 responses, including the question “How many times did you use this feature this month?” Asked to summarize, AI reports: “62% of people didn’t use this feature this month.” The number is neat. The problem is that only 24 of those 62 explicitly answered 0; the other 38 were blank, and AI counted them all as 0. Of those 38 blanks, 28 came from people who had never used the product, so the survey skipped the question for them, and 10 came from people who simply hadn’t responded yet.
A blank describes the state of the data; a zero is a meaningful observation. Before AI summarizes a spreadsheet, sort each column’s blanks into three kinds: confirmed zero, unknown, and not applicable. When reporting a ratio, say which denominator was used and how many values are missing.
The formula is right; the question is wrong
This isn’t an error you fix by asking AI to recalculate. As long as “what a blank means” isn’t written down, AI, spreadsheet formulas, and analysis programs may each apply different defaults.
The documentation for pandas, a widely used data analysis tool, says so explicitly: when summing, missing values or empty data are treated as zero. A column of all blanks sums to 0. The calculation is completely correct, yet it answers the wrong question.
The UK Government Analysis Function’s guidance on releasing statistics in spreadsheets also warns that a blank cell doesn’t tell readers why there’s no data, and they may assume it means zero or that something went wrong.
A blank has at least three meanings
Confirmed zero: a shop confirms zero returns this week, or a respondent explicitly chooses “never used.” This is a real observation; it can count in the numerator and in the denominator of valid responses.
Unknown: not yet answered, a sync failure, a missing source field. This only means there’s no value right now. It can’t silently become zero, and it can’t quietly disappear from the report. At minimum, record how many, where they came from, and who will confirm them.
Not applicable: people who have never used the product shouldn’t answer “how many times did you use it this month”; employees who have left shouldn’t be counted in this month’s staff satisfaction. Not applicable isn’t a smaller number; it means the record doesn’t belong to this question.
The same symbol can’t be read the same way across columns. A 0 in a sales column, a blank not yet filled in, an “N/A” exported by a system, and a “-” added for layout all need to be interpreted by column and source. The evidence comes from field descriptions, the survey’s skip logic, system documentation, or the data owner. If no evidence can be found, stop at “unknown,” and don’t fill values based on what nearby rows “look like.” The web standards body W3C created a metadata standard for tabular data, letting each column state how empty values should be read, for this very reason: a CSV file contains only the text in its cells, not enough meaning for a program to guess correctly on its own.
Report the denominator and missing values with every number
Back to the survey: of 100 responses, 62 gave an explicit count, 24 of them 0; 28 were not applicable because they had never used the product; 10 hadn’t responded yet.
“Zero-time users are 24%” uses all 100 responses as the denominator; “38.7% of valid respondents didn’t use it this month” looks only at the 62 valid answers. Both can be calculated, but they answer different questions. The 62% AI gave at first counted not-applicable and unknown as zero, and answered neither question correctly.
So an AI summary fit to hand over should show, side by side: the original number of rows, valid observations, confirmed zeros, unknowns, not-applicables, the denominator used, and the rule behind each status. If months are compared, also confirm that the rules didn’t change partway through.
Even after deciding to fill blanks, keep the original column
Even if the data owner eventually decides a certain kind of blank can be treated as zero, don’t fill it in directly in the original file. Keep the original column and add a converted column that records the rule, who changed it, and when. That way you can always compare “what was there” with “what it became under which rule.”
Be more cautious with high-risk data. Missing values in medical, credit, insurance, hiring, or performance records can affect people’s rights. Don’t let a general-purpose AI impute values on its own, and don’t upload whole files containing personal data to tools that haven’t been approved. Confirm the rules on a small de-identified sample first, and have the data and business owners approve the scope of analysis.
The next time you ask AI to work through a spreadsheet, have it inventory missing values first rather than calculating ratios straight away. It’s the same principle as naming official sources for company numbers, just at the easily overlooked column level; see Whether AI Gets Company Numbers Right Depends on Someone Maintaining the Definitions. If the resulting table goes to someone else, include notes on the denominator and missing values; see A Tidy AI Summary Does Not Mean a Teammate Can Take Over.
AI handoff card
I want AI to summarize a spreadsheet, but I'm worried it will treat blanks as zero.
The method comes from this article: https://boosterminiclass.com/en/posts/ai-spreadsheet-blank-cell-meaning/
Its view: a blank is not zero. Before summarizing, sort each column's blanks into confirmed zero, unknown, and not applicable; when reporting a ratio, state the denominator used and how many values are missing. Without evidence, treat a blank as unknown, and don't infer values from nearby data.
Read only the spreadsheet I've authorized: don't fill blanks, delete rows, change formulas, or overwrite the original, and don't produce final ratios or trend conclusions yet.
1. Identify the sheets, data range, column names, and any blanks, zeros, N/A, dashes, or other special codes that might represent missing values;
2. For each column, list: data type, total rows, non-empty count, explicit zeros, each special code and its count, blank count, and any field descriptions or skip rules you found;
3. Classify each candidate missing value as "confirmed zero," "unknown," "not applicable," or "meaning to confirm," and explain how it changes the numerator and denominator;
4. List at most five questions to ask the data owner, and propose a plan that adds converted columns without modifying the original file.
Stop at this step until I confirm the rules.
References
- pandas documentation: Working with missing data — https://pandas.pydata.org/docs/user_guide/missing_data.html [accessed: 2026-10-11]
- UK Government Analysis Function (official guidance): Releasing statistics in spreadsheets — https://analysisfunction.civilservice.gov.uk/policy-store/releasing-statistics-in-spreadsheets [accessed: 2026-10-11]
- W3C Recommendation: Metadata Vocabulary for Tabular Data — https://www.w3.org/TR/tabular-metadata/ (2015-12-17)
