
Tutorials
How to Clean Data in Excel: 6 Quick Fixes for Messy Spreadsheets
Messy spreadsheet? Learn how to clean data in Excel with TRIM, Remove Duplicates, Text to Columns, PROPER and fixes for numbers stored as text.
By ExcelPanda Team · Published · 5 min read
Remove extra spaces with TRIM
Extra spaces make "North" and "North " look identical but behave differently. TRIM strips them:
=TRIM(A2)Copy it down, then copy the results and use Paste Special, Values to replace the originals.
Remove duplicates
Select your data and choose Data, Remove Duplicates. Tick the columns that define a duplicate, for example just the email column, and click OK. Excel tells you how many rows were removed.
Split text into columns
If a column holds "Smith, Jane" or "2026-11-14 09:30", split it with Data, Text to Columns. Pick Delimited and choose the separator, such as a comma or space. In Microsoft 365 you can also use TEXTSPLIT.
Fix capitalisation
Capitalisation functions
| Function | Result for "jane SMITH" |
|---|---|
| =PROPER(A2) | Jane Smith |
| =UPPER(A2) | JANE SMITH |
| =LOWER(A2) | jane smith |
Fix numbers stored as text
Numbers that sit on the left of a cell, or show a small green triangle, are probably stored as text. Select them and use the warning icon to convert to numbers, or multiply by 1 in a helper column: =A2*1. Formulas like SUMIF will skip text numbers, so this matters.
Find and replace
Press Ctrl+H (it works on Mac too) to replace one value with another across a selection. Use it to standardise labels like "N." and "North" into one name.
A cleaning checklist
- One header row, with no merged cells.
- One kind of data per column.
- No blank rows inside the data.
- Dates are real dates, numbers are real numbers.
- Convert the range to a Table (Ctrl+T) before analysing.
Clean data is what makes a PivotTable work first time.
Frequently asked questions
Why clean data before analysing it?
Stray spaces, duplicates and numbers stored as text make formulas, filters and PivotTables return wrong or missing results.
Does Remove Duplicates delete data permanently?
It removes the duplicate rows from the range, though you can undo it straight away. Work on a copy if the data matters.
How do I get rid of a leading apostrophe?
Convert the cells to numbers with Text to Columns, or use VALUE. The apostrophe marks the cell as text.
What is Flash Fill?
A feature (Ctrl+E) that spots a pattern in examples you type and fills the rest of the column, handy for splitting or joining names.
Sources and disclaimers
ExcelPanda is not affiliated with or endorsed by Microsoft. Microsoft and Excel are trademarks of the Microsoft group of companies.
.png)