AI Spreadsheet Cleanup: Keep IDs, Dates, and Rows Intact

AI spreadsheet cleanup should produce proposed changes you can trace back to the source. Keep the original file and define each column. Review edits in a separate ledger. Accept the working copy only after its row counts and IDs reconcile. Leave ambiguous values visible and make sure formula checks pass.

This guide uses a fictional office inventory. The method and worksheet are our own; they are not results from a test of an AI product. Use an approved tool with data your team permits it to process. Start with synthetic examples when setting up the task.

Define the columns before changing values

Save an untouched source copy and create a working copy. Give every source row a stable ID before sorting. A row number on screen can move; a stored ID such as R01 lets you find the same record after a sort or filter.

Write the field rules first. A model should not decide whether a code is a quantity or what a blank means.

The business key here is item ID plus location: the same item can appear at two locations. That rule belongs to this fictional inventory snapshot. Your export might instead have one row per shipment or movement, which needs a different key.

Keep a change ledger beside the source

The AI spreadsheet cleanup worksheet records one proposed change per row. Keep raw and proposed values in separate columns. Add the rule and its evidence. Record the review decision and value you applied. A sample sheet describes inventory; a repair ledger explains each proposed change.

Here is the six-row synthetic source. Dates use mixed formats on purpose.

Suppose the owner confirms that R04 was copied from R03 by mistake. Record that fictional confirmation before moving R04 to a duplicate quarantine. Preserve both source rows. R05 stays because its location differs. R02’s date stays on hold until the source owner confirms the date order.

The worksheet contains examples, not approvals for your data. Replace them with your own evidence. Set the ID and value columns to text when importing the CSV. Otherwise, the app may change the values you meant to inspect.

Test the four cases that can look correct

Leading zeros: 00127 identifies an item; it is not the number 127 for this task. Microsoft’s Excel guidance on leading zeros describes importing such columns as text. Changing the display format later does not recover unknown digits lost earlier. Return to the source if a code has already changed.

Ambiguous dates: 03/04/2026 could mean March 4 or April 3. Do not guess. Microsoft’s Power Query data-type guide explains that locale affects date parsing. You can set that locale when changing a column’s type. Confirm the source date order first. Then check the parsed day and month.

Duplicate rows: a repeated item ID is not enough to delete a row. Google’s Sheets cleanup guide says the tool checks selected columns. Cells with the same value can count as duplicates despite a different formula or format. Check the chosen key and range before removing rows. A match still needs review of what the row means.

Formulas: check both the expression and its result. For R05, three boxes of six units should yield 18. A pasted value of 18 can look right while losing the formula. Excel’s Trace Precedents and Dependents tools can show cell links. Check those references again after moving rows or columns.

Ask for proposed edits with a bounded prompt

Give the tool the field rules and a small approved sample. Ask for a ledger before any rewritten file. This example prompt keeps the task narrow:

Review this inventory against the supplied column rules. Return one row per proposed change with these fields: source row ID | field | raw value | proposed value | rule | reason. Do not overwrite data or delete rows. Preserve IDs as text. Flag ambiguous dates and possible duplicates for review. Do not fill gaps from general knowledge. Keep formula changes separate from value changes. Name any missing rule. Leave the affected value unchanged.

Read the suggestions using the AI output review checklist . A reason that sounds plausible is not source evidence. For a fixed rule such as trimming approved outer spaces, a normal spreadsheet transformation may be easier to inspect than an AI proposal.

Accept a result only after reconciliation

In the synthetic case, six source rows become five working rows plus one quarantined duplicate. That accounts for all six. It does not make the file ready: R02 still has an unresolved date. Keep that flag visible and block any use that needs a confirmed received date.

After the approved duplicate removal, the five rows should total 52 units: 12 + 4 + 10 + 18 + 8. These are example arithmetic checks, not observed model performance. Compare totals by location as well; a correct overall total can hide a misplaced row.

Check that each source ID maps to a working row or a recorded quarantine decision. Confirm only approved fields changed. Recalculate formulas and inspect errors, then reopen the saved output to check types survived. Row counts alone will not catch an item code that silently lost its zero.

Record the reviewed file version and unresolved items before passing it on. Use the workflow handoff record so the next owner knows which rules ran and how to restore the source.

Frequently asked questions

FAQ