The Excel Data Cleaning Checklist (20 Steps)

Cleaning data in Excel means making it accurate, consistent and structured before you analyze it: removing duplicates and blank rows, fixing text and number formats, standardizing dates and categories, and checking totals against the source. The 20-step checklist below works in that order, so each step makes the next one easier, and it ends with ways to automate the whole routine.

Before you start: protect the original

Cleaning changes data, and mistakes are easy to make. A few minutes of setup saves hours of rework:

• Keep the raw file untouched. Work on a copy or on a new sheet called Clean.
• Write down the row count and one or two key totals (for example total revenue) from the raw data. You will compare against them at the end.
• If this is a recurring file (weekly export, monthly report), consider doing the cleaning in Power Query from the start, so every step can be replayed with one click.

Phase 1: Structure (steps 1–5)

Analysis tools such as PivotTables, Power Query and Power BI expect a simple shape: one header row, one record per row, one type of value per column.

1. Remove report clutter. Delete title rows, logos, notes and subtotal rows above or inside the data. Only the header row and data rows should remain.
2. Unmerge cells. Select all, then Home > Merge & Center > Unmerge Cells. If merged cells were hiding repeated values (for example a region written once for several rows), fill them down: select the column, press F5 > Special > Blanks, type = and the cell above, then press Ctrl+Enter. Paste as values afterward.
3. Fix headers. Each column needs one unique, short, descriptive name with no line breaks, such as Order_Date or Revenue_USD.
4. Delete fully blank rows and columns. Filter a key column for blanks, check that the whole row is empty, and delete it.
5. Convert to an Excel Table. Press Ctrl+T and name the table. Tables expand automatically and make formulas readable.

Phase 2: Text cleanup (steps 6–10)

Text problems are invisible but break lookups and grouping. "Miami" and "Miami " look identical yet count as two different cities.

6. Trim extra spaces. TRIM removes leading, trailing and repeated spaces.
7. Remove non-printing characters. CLEAN removes line breaks and other control characters common in system exports.
8. Replace non-breaking spaces. Data copied from web pages often contains character 160, which TRIM does not remove. Substitute it with a normal space first.
9. Standardize capitalization. Use UPPER, LOWER or PROPER so "NORTH", "north" and "North" become one value.
10. Split or combine columns. Separate "City, State" into two columns with Data > Text to Columns or TEXTSPLIT, or combine first and last names with TEXTJOIN.

One helper-column formula handles steps 6 to 8 together:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Copy the results and paste them back as values (Ctrl+Alt+V, then V) over the original column, then delete the helper column.

Phase 3: Numbers and dates (steps 11–14)

11. Convert numbers stored as text. Green triangles in the corner of cells or numbers aligned to the left are the warning signs. Select the column and use Data > Text to Columns > Finish, or a helper column with =VALUE(A2).
12. Remove currency symbols and thousands separators that came in as text, for example with =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")).
13. Convert text dates to real dates. Real dates are numbers underneath. Test with =ISNUMBER(A2): FALSE means it is text. Text to Columns with the Date option (choosing MDY or DMY) or DATEVALUE usually fixes it. Watch for day/month confusion when files come from other countries.
14. Apply consistent formats. One date format, one currency format and a fixed number of decimals per column.

A quick check that flags values that are still text in a numeric column:

=SUMPRODUCT(--ISTEXT(tblData[Revenue]))

If the result is greater than zero, some cells are still text.

Phase 4: Consistency and duplicates (steps 15–18)

15. Standardize categories. List distinct values with =SORT(UNIQUE(tblData[Region])) and look for variants such as "FL", "Fla." and "Florida". Fix them with Find & Replace or a mapping table plus XLOOKUP.
16. Find and remove duplicates. First decide what a duplicate means: the same order ID, or the same order ID and product? Then use Data > Remove Duplicates with exactly those columns checked. To review before deleting, flag them with =COUNTIFS(tblData[OrderID],[@OrderID])>1.
17. Handle blanks on purpose. Decide whether a blank means zero, unknown or not applicable, and treat it consistently. Do not fill blanks with zero if zero has a different meaning.
18. Check ranges and outliers. Sort each numeric column both ways, or use conditional formatting, to spot negative quantities, prices of 0.01 or dates in the future. Investigate before deleting: an outlier may be a real large order.

Phase 5: Validate and automate (steps 19–20)

19. Reconcile with the source. Compare your row count and key totals with the numbers you wrote down at the start. If rows were removed on purpose (duplicates, test orders), you should be able to explain the exact difference.
20. Document and automate. Write a short note of what you changed. If the file arrives again next week, rebuild the steps in Power Query so cleaning becomes a refresh, not a project.

Power Query covers most of this checklist with buttons: Trim, Clean, Change Type, Replace Values, Remove Duplicates, Split Column and Fill Down, all recorded as repeatable steps. Our Power Query in Excel guide shows how to set it up, and the Power Query M language introduction helps when you want to edit the steps directly.

Common cleaning mistakes

• Cleaning the only copy. If something goes wrong there is nothing to compare against. Always keep the raw file.
• Sorting part of the data. Sorting one selected column instead of the whole table scrambles your records. Converting to a Table first prevents this.
• Leaving helper formulas behind. Paste cleaned values over the original column and delete helper columns, or the next refresh may break.
• Deleting outliers without asking. A huge order or a negative quantity may be a real event, such as a bulk sale or a return. Confirm with the data owner first.

Quick reference table

Next steps

Save this checklist and run it on your next dataset, step by step. Once the data is clean, you are ready to summarize it: continue with our Excel pivot tables guide. If you clean the same file every week, make Power Query your next skill to learn, because it turns this whole checklist into a single Refresh button.

Watch: video lessons

▶

▶

Frequently asked questions