How to clean data in Excel: a 12-step checklist

1. Keep the original

Copy the raw data to a new sheet (or save a copy of the file) before you change anything. Cleaning involves deleting and overwriting, and you’ll want the original to check against.

2. Make it a proper table

Excel’s tools work best when the data has exactly one header row, one record per row, no merged cells and no blank rows or columns in the middle. Unmerge cells (Home → Merge & Center → Unmerge Cells), delete title rows above the headers, and then press Ctrl+T to turn the range into an Excel table. Tables expand automatically and let formulas use column names.

3. Remove extra spaces

Leading, trailing and double spaces make identical values look different to Excel, which breaks lookups, filters and duplicate checks. In a helper column:

TRIM removes spaces at the start and end and reduces runs of spaces inside the text to one. It doesn’t touch non-breaking spaces (character 160), which are common in data copied from websites, so use this version when in doubt:

Copy the helper column and paste it over the original with Paste Special → Values, then delete the helper.

4. Remove hidden characters

Line breaks and other non-printing characters sneak in from other systems. CLEAN removes the first 32 non-printing ASCII characters:

5. Fix capitalisation

Pick one style per column and apply it:

• =PROPER(A2) — Acorn Hardware (names and places)
• =UPPER(A2) — SO-10093 (codes)
• =LOWER(A2) — name@company.com (email addresses)

PROPER capitalises after every non-letter, so check results for names like McDonald or O’Neil and acronyms like USA.

6. Standardise spellings

“North”, “Nth” and “north” are three values to a PivotTable. For a few variants, use Find & Replace (Ctrl+H) with Match entire cell contents ticked so you don’t change “North” inside “North West”. For many variants, build a two-column mapping table (Variant, Standard) and look each value up:

The last argument returns the original value when it isn’t in the map, so unmapped values pass through unchanged.

7. Convert numbers stored as text

Numbers stored as text sit on the left of the cell, often with a small green triangle, and SUM ignores them. Quick fixes:

• Select the cells, click the warning icon and choose Convert to Number.
• Or select the column, choose Data → Text to Columns → Finish.
• Or use a formula, removing currency symbols and thousands separators first: =VALUE(SUBSTITUTE(SUBSTITUTE(D2, "$", ""), ",", ""))

Leave identifiers such as product codes, account numbers and phone numbers as text, so leading zeros survive. Our guide to converting text to numbers covers every case, including European formats.

8. Fix dates

A column that mixes 2026-03-14, 14/03/2026 and Mar 14, 2026 can’t be sorted or grouped by month reliably. Check which cells are real dates with =ISNUMBER(B2) — real dates are numbers underneath. To convert text dates:

• Data → Text to Columns, click Next twice, choose Date and the order the text uses (for example DMY), then Finish.
• Or build the date from its parts. For text in dd/mm/yyyy form: =DATE(RIGHT(B2, 4), MID(B2, 4, 2), LEFT(B2, 2))

Then apply one number format to the whole column (Ctrl+1 → Date). The ISO format yyyy-mm-dd is unambiguous for international teams.

9. Remove duplicates

Decide which columns identify a record, then use Data → Remove Duplicates. Clean spaces and capitals first (steps 3 and 5), or near-duplicates will survive. Our guide to removing duplicates compares five methods.

10. Deal with blanks

Find blanks with Home → Find & Select → Go To Special → Blanks. Then decide column by column:

• Categories (region, product type): fill with a placeholder such as “Unknown”, so they show up in PivotTables instead of disappearing.
• Amounts and quantities: don’t invent values. Leave them blank, so averages ignore them, and fix them at the source.
• Completely empty rows: delete them; they break tables and sorting.

To fill a selection of blanks with the value above (common in exports that only show a category once), select the column, use Go To Special → Blanks, type = and press the up arrow, then Ctrl+Enter.

11. Split or combine columns

• Split “Lastname, Firstname” or “SKU-Warehouse” with Data → Text to Columns, or in Excel 365 with =TEXTSPLIT(A2, ", ").
• Let Flash Fill (Ctrl+E) learn a pattern from one or two examples you type.
• Combine columns with =TEXTJOIN(" ", TRUE, B2, C2), which skips blanks.

12. Validate before you trust it

A quick sanity check catches most remaining problems:

• =COUNTBLANK(D2:D2000) for each important column.
• Sort each number column both ways to spot impossible values (negative quantities, a 289,000 order in a column of hundreds).
• Compare =COUNT(D:D) with =COUNTA(D:D)-1: if they differ, some values aren’t numbers.
• Add Data Validation (Data → Data Validation) to stop the same mess being typed in again, for example a list of allowed regions.

Make it repeatable with Power Query

If the same messy export arrives every week, do the cleaning in Power Query (Data → From Table/Range). Trim, change case, replace values, change types and remove duplicates from the ribbon; each step is recorded. Next time, drop in the new file and choose Data → Refresh All.

The fast way

MindySheets runs this checklist automatically. Open the file and every column is checked for extra spaces, spelling variants, text numbers, mixed dates, blanks, duplicates and unusual values, with a count and an example for each. Quick fixes handle the mechanical problems; Mindy’s cleaning plan handles the judgement calls, like deciding that “Nth” means “North”. You approve every change, and the download includes a sheet listing each one.

Frequently asked questions