Clean Spreadsheets with AI Without Losing Workbook Logic

Worked example

Separate empty inputs from zero values

Illustrative example written for this guide. Model outputs vary.

A blank can mean missing information. Replacing every blank with zero may conceal incomplete records and change summaries.

Prompt to adapt

Review this workbook for inconsistent category labels, missing inputs and formula anomalies. Separate input cells from calculated cells. Propose changes by sheet and cell range. Do not replace blanks with zero unless the column rules explicitly allow it.

Map the workbook before editing

Identify input tables, calculation sheets and presentation sheets. Note formulas, named ranges, validation rules and links to other sheets or files. Ask which parts of the workbook the tool can inspect and preserve.

Save a version you can return to. Record the key totals and a few representative formula results so you can compare them after cleanup.

Fix categories with an explicit mapping

Create a small mapping table for variants such as “North”, “NORTH” and “North ”. Confirm that each pair means the same thing; similar-looking labels may represent different teams or products.

Apply the agreed mapping to inputs, then review downstream summaries. Avoid replacing text across the whole workbook if it could affect formulas, notes or deliberate labels.

Check formulas and reconciliation

Look for inconsistent formulas within a calculated column, references to the wrong sheet and formulas replaced by typed values. An unusual formula can be intentional, so review exceptions before standardizing them.

Recalculate in your spreadsheet application and compare the recorded totals. Add a new row to check whether formulas and summaries expand correctly. Keep a brief record of edits and unresolved issues.