How to Clean Data in Excel
Clean messy Excel data by removing duplicates, trimming spaces, fixing dates, standardizing labels, and checking errors before analysis.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
Cleaning data in Excel means turning an imported or messy table into something formulas, pivots, and charts can trust. Most bad reports start with small data problems: extra spaces, duplicate rows, dates stored as text, inconsistent labels, blank fields, and errors mixed into numeric columns.
The goal is not to make the sheet look nicer. The goal is to make the data consistent enough that analysis does not lie.
Step-by-step data cleaning workflow
Step 1. Make a copy of the raw data
Keep the original export untouched. Work in a copy so you can compare changes or recover fields if a cleanup step goes too far.
Step 2. Remove duplicate rows
Use Excel's Remove Duplicates tool when a full row or key field should be unique. For lead lists, email is often the best key. For transactions, use a combination of date, vendor, amount, and reference ID.
Step 3. Trim spaces
Extra spaces break lookups and grouped summaries. If a name is in A2, use:
=TRIM(A2)Fill down, then paste values back over the original column if needed.
Step 4. Standardize labels
Inconsistent labels create bad pivot totals. "Proposal", "proposal", and "Proposal Sent" may need to become one agreed stage. Use Find and Replace, dropdowns, or a mapping table.
Step 5. Convert text dates and numbers
Dates and numbers imported as text can look correct while formulas treat them incorrectly. Test with ISNUMBER:
=ISNUMBER(A2)If dates are text, use Text to Columns, DATEVALUE, or a clean re-import depending on the source.
Step 6. Check blanks and errors
Count missing required fields:
=COUNTBLANK(A:A)Wrap downstream formulas with IFERROR only after you understand the cause:
=IFERROR(VLOOKUP(E2, A:C, 3, FALSE), "")⚠ WATCH OUT
What to clean before analysis
| Field type | Common issue | Fix |
|---|---|---|
| Names | Extra spaces or inconsistent case | TRIM, PROPER, manual review |
| Dates | Stored as text | DATEVALUE or re-import |
| Categories | Duplicate labels | Mapping table or dropdowns |
| IDs | Leading zeros lost | Store as text before import |
| Amounts | Currency symbols stored as text | Convert to numeric values |
Clean structure matters for templates too. A CRM lead tracker breaks down when lead sources or stages are inconsistent. An expense tracker becomes unreliable when categories drift.
The Griddy way
Manual data cleaning is repetitive because each imported file has a slightly different mix of duplicates, spaces, bad dates, and inconsistent labels.
"Clean this table by removing duplicate emails, trimming whitespace, standardizing the Stage column, converting text dates, and flagging blank next actions."
Griddy can apply those cleanup steps directly in the sheet and leave you with data that is ready for formulas, pivots, and charts.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Clean the sheets that drive weekly decisions
CRM, pipeline, and expense templates only stay useful when categories, stages, dates, and required fields remain consistent.
CRM Lead Tracker
Track contacts, lead source, owner, next due date, and follow-up status in one lightweight CRM sheet. Keep hot opportunities and stale leads visible without paying for heavy sales software.
Sales Pipeline
Track deals by stage, owner, value, and next move in one lightweight pipeline sheet. Keep close dates, weighted forecast, and rep follow-ups visible without needing a full CRM.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
CRM Lead Tracker for Small Business
Track small business leads, referrals, owners, sources, follow-up dates, and next actions in one free CRM spreadsheet template.