Messy data is the silent killer of Excel reporting. You build a beautiful dashboard, but the data feeding it has duplicate SKUs, categories spelled three different ways, and dates formatted as text. AI tools and Power Query together make cleaning this data faster than ever.
The Most Common Data Problems in Operations Spreadsheets
| Problem | Example | Impact |
|---|---|---|
| Inconsistent text values | Parts, PARTS, parts, Part | SUMIFS by category returns wrong totals |
| Dates as text | "01/15/2025" stored as text | Date math and filters break |
| Leading/trailing spaces | "SKU-001 " with a space | VLOOKUP fails to find matches |
| Duplicate rows | Same SKU entered twice | Inventory counts are inflated |
| Numbers as text | Qty "100" that won't sum | SUM and SUMIFS return 0 |
| Mixed formats | Some SKUs are SKU-001, others are 001 | Lookups fail across sources |
Method 1 — Use AI to Write Your Cleaning Formulas
Describe your data problem to ChatGPT or Claude and ask for the fix:
"My Category column has values like Parts, PARTS, parts, and Part. I need a formula that standardizes all of these to just Parts. Column D contains the categories."
AI response will suggest: =PROPER(TRIM(D2)) or a lookup table approach for more complex standardization.
Method 2 — Power Query for Systematic Cleaning
Power Query is better than formulas for cleaning because it records steps and re-applies them every refresh.
Fix inconsistent text values
In Power Query Editor:
Select the Category column
Transform tab → Format → Trim (removes spaces)
Transform tab → Format → Uppercase (or Capitalize Each Word)
Use Replace Values to fix any remaining inconsistencies
Fix dates stored as text
Click the column type icon (ABC) next to the date column header
Select Date from the dropdown
Power Query converts all text dates to proper date format
Remove duplicates
Select the SKU column (or all columns for full-row deduplication)
Home tab → Remove Rows → Remove Duplicates
Power Query keeps the first occurrence and removes the rest
Standardize SKU formats
Add Column → Custom Column
Name: Clean SKU
Formula: "SKU-" & Text.PadStart(Text.From([SKU Number]), 3, "0")
This converts 1 to SKU-001, 12 to SKU-012, etc.
Method 3 — AI-Assisted Bulk Reclassification
If you have hundreds of inconsistent category values and need to standardize them, AI can help map them in bulk:
- Export your unique category values to a text file
- Paste them into ChatGPT with: "Standardize these category names into consistent groups. I want the final categories to be: Parts, Packaging, Components, Fasteners"
- AI returns a mapping table — paste it into Excel as a lookup reference
- Use VLOOKUP or Power Query Merge to apply the mapping to your full dataset
💡 Clean at the source when possible. Data cleaning in Excel is treating symptoms. If your ERP exports inconsistent data, work with your IT team to fix the export query. Cleaning in Excel every time is a permanent maintenance burden.
FAQ
How do I find all the unique values in a column to audit for inconsistencies? ▼
Three ways: (1) Select the column → Insert → PivotTable → drag the field to Rows — shows all unique values with counts. (2) Data → Remove Duplicates on a copy of the column. (3) In Power Query, Group By on the column to see all unique values and their frequency.
My numbers are stored as text and TRIM and VALUE are not fixing them. What else can I try? ▼
Try multiplying by 1: =A2*1. Or paste 1 into an empty cell, copy it, select your problem column, Paste Special → Multiply. This forces Excel to treat the values as numbers. In Power Query, changing the column type to Number usually resolves it.
Can I use Power Query to clean data from multiple sheets at once? ▼
Yes. In Power Query, use Get Data → From Workbook and select multiple sheets, or use Append Queries to stack them. Apply your cleaning steps to each table, then append into one clean dataset. This is much faster than cleaning each sheet manually.
How do I stop bad data from coming back after I clean it? ▼
Use Data Validation on your input columns — dropdown lists for categories, date pickers for date fields, and whole-number restrictions for quantity fields. This prevents the problem at entry rather than fixing it after the fact. For ERP exports, the only reliable fix is correcting the export query upstream.
📦 Get the Free Inventory Dashboard Template
Free download — no sign-up required.
⬇ Download Free Template