Data Cleaning Hub
Excel Data Cleaning: Complete Guide
Dirty data breaks formulas silently, produces wrong totals, and fails imports without warning. This hub covers every cleaning task — in the order you should do them.
The 8-Step Data Cleaning Process
Work through these in order — structure problems must be fixed before data quality issues, or your cleaning steps will produce inconsistent results.
Back up the original file
Before changing anything, save a copy with a date stamp. Data cleaning operations can be irreversible — a backup takes 10 seconds and has saved countless work.
Fix the structure
Ensure data has one header row, no merged cells in the data area, no blank rows or columns splitting the data, and no totals rows embedded in the middle of the table.
Remove duplicates
Identify and remove duplicate rows using Data → Remove Duplicates, the UNIQUE formula, or the free duplicate finder tool. Always check which columns define a "duplicate" before deleting.
Fix numbers stored as text
Numbers stored as text look normal but break SUM, AVERAGE, and VLOOKUP silently. Identify with =ISNUMBER() and convert using the warning icon or VALUE().
Remove extra spaces
Leading and trailing spaces make two identical-looking values different — breaking lookups and groupings. Use =TRIM() in a helper column, paste as values, then delete the originals.
Standardise formats
Ensure date columns contain real Excel dates (not text), phone numbers follow one format, and text case is consistent. Use PROPER(), UPPER(), LOWER(), and TEXT() to standardise.
Audit formulas and errors
Run an error check to find #REF!, #N/A, #VALUE!, and other formula errors. Check for hardcoded values in formulas and inconsistent formula patterns across columns.
Final review before sharing
Remove hidden sheets containing sensitive data, check for external links, verify the file size is reasonable, and confirm nothing was accidentally overwritten.
Data Cleaning Guides
Remove Duplicates in Excel
4 methods — built-in tool, UNIQUE formula, conditional formatting, Power Query.
Read guide →
Remove Blank Rows Without Losing Data
Filter, Go To Special, and a free tool — safely without deleting the wrong rows.
Read guide →
Fix Numbers Stored as Text
Detect and bulk-convert text-formatted numbers that silently break SUM and VLOOKUP.
Read guide →
Remove Spaces in Excel
TRIM, CLEAN, SUBSTITUTE — leading, trailing, extra, and non-breaking spaces.
Read guide →
Data Cleaning Checklist
A full pre-sharing checklist covering every common data quality problem.
Read guide →
Audit Before Sending
The 10-point check before sharing any Excel file externally.
Read guide →
Find Duplicates Across Two Columns
COUNTIF-based detection when duplicates span two separate columns.
Read guide →
Case-Sensitive Duplicate Detection
When "WIDGET" and "widget" are different — SKUs, IDs, and system codes.
Read guide →
Fix Date Format Problems
Dates showing as numbers, wrong year, refusing to format — every cause fixed.
Read guide →
Combine Two Columns
Merge first/last name, address fields, or any text columns with or without a separator.
Read guide →
Free Data Cleaning Tools
All tools run in your browser — your files never leave your device.
Find Duplicates Tool
Upload a file, pick a column, get all duplicate rows highlighted and downloadable.
Remove Blank Rows Tool
Strip all blank rows from a sheet in one click — no risk of losing surrounding data.
Spreadsheet Auditor
Find formula errors, hidden sheets, hardcoded values, and inconsistent formulas across all sheets.
Extract Unique Values Tool
Get a clean list of unique values from any column without formulas or filters.
Find & Replace Tool
Replace values across a whole workbook safely — preview before applying.
Fill Blank Cells Tool
Fill empty cells in a column by carrying the value above downward.
Frequently Asked Questions
What is the first step in cleaning Excel data?
The first step is making a backup copy of the original file before touching anything. Then audit the scope of the problem: use Ctrl+End to find the true extent of the used range, check for blank rows or columns that break the data structure, and run an error check (F5 → Special → Errors) to spot formula problems.
How do I remove duplicates in Excel without losing data?
Use Data → Remove Duplicates and select only the columns that define a duplicate. Review the preview before confirming. For safety, work on a copy of the data or use conditional formatting to highlight duplicates first so you can review them before deletion.
Why do SUMIF and VLOOKUP return wrong results on clean-looking data?
Numbers stored as text are the most common cause. A cell containing '123 looks identical to 123 on screen but behaves differently in formulas — SUMIF ignores it, VLOOKUP won't match it. Test with =ISNUMBER(A1): if it returns FALSE, the cell contains a text-formatted number.