AI Spreadsheet Cleanup Without Changing Its Meaning

Before an AI agent cleans a spreadsheet, define what each column means and which transformations it may apply. Preserve the original and ask for uncertain cells separately so the owner can resolve exceptions before accepting the cleaned copy. A sheet that looks consistent afterwards has proved only that it is consistent; whether the values still mean what they meant is a separate question the agent cannot answer from the cells alone.

This guide sets out the difference between a formatting rule and a meaning change, why the standards treat a cell’s text and its value as two things, how to write the column record that lets an agent clean safely, where uncertain rows should go instead of being fixed, the cleanups that quietly changed the data, and the comparison that decides whether the cleaned file can be accepted.

“Clean this up” is a request about appearance: consistent formats, no stray whitespace, dates that all look alike, duplicates gone. An agent can satisfy every one of those by changing what the cells mean. A leading zero removed from an identifier makes the column look numeric and breaks the key. A blank filled with zero makes the column look complete and turns “unknown” into “none”. Two similar rows merged make the sheet look deduplicated and may have merged two customers.

The distinction the agent needs is between a rule that changes how a value is written and a rule that changes which value it is. The first kind is safe to automate once the column’s meaning is known. The second kind is a decision about the data, and it needs the owner. Everything in this guide is a way of keeping the two apart before the agent starts, because afterwards they look identical in the output.

The following examples are hypothetical. A semantic change alters what a value represents, rather than only how it is displayed. Use the table to decide which column rules the agent can apply automatically and which cases should enter an exception record. The middle column is the safe rule; the right column is what goes wrong when the convenient rule is applied instead.

Every row describes a cell pattern that looks like a formatting problem and is actually a meaning question. That is why the table is organized by what the cell looks like: the agent will encounter the pattern first and reach for the obvious fix, and the row is there to say what the obvious fix costs.

W3C’s tabular data model distinguishes a cell’s string representation from its semantic value, which can depend on annotations such as datatype. This supports recording interpretation rules alongside the cells rather than assuming the text is self-explanatory. The standard does not define the business meaning of your spreadsheet; it establishes that the text in the cell and the thing the text stands for are two different objects, and that the second needs information the cell does not carry.

Python’s CSV documentation provides a concrete loss example: its writer converts None to an empty string and notes that the transformation is not reversible. That is a specific library behavior, not a claim that every export treats missing data the same way. It is cited because it is the smallest possible demonstration of the whole problem: a value that meant “absent” and a value that meant “empty string” become the same characters, and nothing in the file can tell you which it was.

Together the two sources justify the column meaning record. If the text does not carry its own meaning, and a routine export can erase a distinction, then the meaning has to be written down somewhere the agent reads before it touches the cells.

Record each column’s type, unit or locale, missing-value convention and allowed transformations. A customer identifier remains text even when all its examples contain digits; numeric conversion can discard meaningful leading zeros. The record is short, one line per column, and it is the brief the agent works from: anything not permitted by the line is not a cleanup, it is a question.

For a hypothetical date column containing “03/04/2026,” neither the agent nor a spreadsheet’s default locale can establish the intended order from that cell alone. Use the source specification or ask the owner. If evidence remains absent, keep the original text and flag the cell rather than choosing the more familiar interpretation. A column of dates that are all in one format and some of them wrong is worse than a column that is visibly mixed, because the mixed column still carries the warning.

For units, distinguish a display label from a conversion. Adding a currency symbol does not perform an exchange-rate conversion. A conversion needs an explicit rule and provenance; this article does not recommend rates or financial assumptions.

An exception record should identify the source row, original cell, proposed change, reason for uncertainty and owner decision. Stable row identifiers are important because sorting can change row positions. If the sheet lacks a suitable identifier, preserve a source-row reference before rearranging it; a row number that was true before the sort is a pointer to the wrong record after it.

For duplicates, define the entity and the evidence required to merge rows. Two identical names can describe different people; two different spellings can describe the same organization. Treat the matching decision separately from removing repeated whitespace. The agent may propose a match and show the evidence; the owner decides whether the two rows are one thing.

The ask-instead-of-guessing guide helps set escalation thresholds. A known, authorized column rule can run without repeated questions. Missing evidence about the meaning of a cell belongs in the exception record, where one owner decision can settle many cells at once rather than interrupting the run for each.

Each illustrative case below produced a sheet that looked better than the original and meant something different. In each, the cleanup was a reasonable response to the cell pattern and an unreasonable response to the column.

The numeric identifier: a postcode or account column is converted to numbers so it sorts properly, and every leading zero disappears; the keys no longer match the system they came from. The helpful fill: a blank “units sold” column is filled with zeros to make the totals work, and the stores that did not report are now stores that sold nothing. The confident date: a mixed day-month column is normalized to one order, and the cells that were already in the other order are now silently wrong by as much as several months. The tidy merge: rows with the same name and city are collapsed as duplicates, and two customers at the same address are now one customer with one order history.

None of these is reliably detectable from the output alone, and all four pass a totals check or a row-count check in at least one direction. They are caught by the comparison in the next section, and prevented by the column record, which would have forbidden each transformation before it ran.

Compare the original and output by stable row reference. Check which cells changed, why they changed and whether the rule was authorized. Row counts and totals can catch some mistakes, but equal totals do not prove that values stayed attached to the right entities; a column can be shuffled against its keys and sum to the same number.

Where formulas matter, inspect both the expression and the result in the intended application. Replacing a formula with its displayed value can preserve today’s appearance while removing future recalculation. Our file-output acceptance reference covers checking the actual delivered copy, and the same discipline that governs a corrected fact in an article governs a corrected cell in a sheet; our guide to changing facts without losing context treats the dependents of a value the same way.

Use the test-data reference to build a small controlled example with leading zeros, ambiguous dates and explicit missing values. Passing that example checks the specified cases. It does not prove the full workbook is clean; retain the output comparison and unresolved exceptions for the real acceptance decision. Teams that want the column record and the comparison built into their data operations rather than remembered per sheet can work with our AI transformation practice.