How to Clean Messy Excel Data: A Step-by-Step Guide

Messy spreadsheets are rarely messy in one way. A customer list exported from a web form, a CRM and a colleague's laptop will contain extra spaces, mixed capitalization, numbers stored as text, five date formats, phone numbers with and without country codes, and the same person twice. Fixing these one at a time, in a random order, is how clean-looking files end up with new errors.

This guide gives you the order that works, the exact Excel formulas and menu paths for each step, the mistakes that cause silent damage, and a clear rule for when it is time to stop doing this by hand.

The order that works

1. Protect the original. Work on a copy, never on the only file.
2. Look before you touch. Find out what is actually in each column.
3. Remove invisible junk. Leading, trailing, double and non-breaking spaces.
4. Fix capitalization. Emails lowercase, names title case, codes untouched.
5. Split and combine. One fact per column.
6. Fix text versus numbers. Real numbers where you calculate, text where you identify.
7. Standardize dates and phone numbers. See the dedicated guides below.
8. Remove duplicates. Only after steps 3 to 7.
9. Handle blanks and placeholders. "N/A", "-" and "TBD" are not data.
10. Validate and document. Check the result against rules and keep a change log.

1. Protect the original

Save a copy of the file before you change anything, and keep the untouched data on its own sheet (call it Raw). Do all cleaning on a second sheet or a second copy of the file. Add a column named Notes where you record anything you changed by judgment rather than by rule, for example "name corrected from the signature in the email". If a fix turns out to be wrong, the original is the only thing that can save you.

2. Look before you touch

You cannot fix what you have not seen. Spend five minutes profiling each column:

• Filter dropdowns. Select the header row and press Ctrl+Shift+L. The list at the bottom of each dropdown shows the distinct values (Excel displays up to 10,000 items), including blanks, "N/A" and variants such as "USA", "U.S.A." and "United States". For longer lists, use a PivotTable.
• Alignment. By default, numbers align right and text aligns left. A "number" that sits on the left is text.
• Counts. =COUNTA(A2:A5000) counts filled cells and =COUNTBLANK(A2:A5000) counts empty ones. A column that is 3% blank has a problem worth understanding before you start.
• Sort a copy of the column. Odd values rise to the top or sink to the bottom: leading spaces, symbols, very long entries.

3. Remove invisible junk

Spaces are the most common cause of "identical" values that do not match. TRIM removes leading and trailing spaces and reduces runs of spaces between words to one:

=TRIM(A2)

There is a catch. Microsoft documents that TRIM was designed to remove the 7-bit ASCII space (character code 32). Text copied from web pages and many exports contains a different character, the non-breaking space (code 160), which TRIM leaves in place. Replace it first, and strip non-printing characters with CLEAN, which removes the first 32 ASCII control characters:

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

Put the formula in a helper column, fill it down, then copy the result and use Paste Special, Values over the original column. Delete the helper column only after you have checked a few rows.

4. Fix capitalization

Use LOWER, UPPER and PROPER deliberately, column by column:

• Email addresses: =LOWER(TRIM(A2)). The domain part of an email is not case sensitive, and in practice nearly every provider treats the whole address as case-insensitive, so lowercase is the safe standard form for matching. Strictly, the part before the @ may be case-sensitive, so keep the original in a separate column if exactness matters.
• Personal names: =PROPER(TRIM(A2)) turns "john SMITH" into "John Smith". Review the exceptions afterwards.
• Codes, SKUs, IDs: leave them alone. Changing the case of an identifier can break a match with another system.

5. Split and combine

A spreadsheet works best with one fact per column. A "Name" column holding "Smith, John (Sales)" cannot be sorted by surname or filtered by team.

• Text to Columns. Select the column, then Data, Text to Columns. Choose Delimited, pick the separator (comma, space, semicolon) and finish. Always make sure there are empty columns to the right first, because the result overwrites whatever is there.
• Flash Fill. Type the first result by hand next to your data, start the second, and press Ctrl+E. Excel infers the pattern. It is fast and sometimes wrong, so scan the output.
• Text functions in Microsoft 365. =TEXTBEFORE(A2,", "), =TEXTAFTER(A2,", ") and =TEXTSPLIT(A2,",") split text without wizards and update when the data changes.
• Combining. =B2&" "&C2 joins first and last name, or =TEXTJOIN(", ",TRUE,B2:E2) joins a range and skips blanks.

6. Fix text versus numbers

This is the problem behind totals that "do not add up". A value that looks like 1,250 but is stored as text is ignored by SUM. Signs: left alignment, a small green triangle in the corner of the cell ("Number stored as text"), or a leading apostrophe in the formula bar.

1. Clean first. =TRIM(SUBSTITUTE(A2,CHAR(160),"")) removes the spaces that stop conversion.
2. Convert. =VALUE(A2), or select the column and use Data, Text to Columns, Finish, which re-reads every cell and converts it in place.
3. Strip symbols and separators. =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) handles "$1,250". Be careful with data from countries where a comma is the decimal mark: "1,250" can mean one point two five.

The reverse also matters. Phone numbers, ZIP and postal codes, account numbers and anything with leading zeros must stay text. Excel stores numbers with 15 significant digits (Microsoft documents this limit), so a 16-digit identifier typed or pasted as a number silently loses its last digit. Format those columns as Text before you paste data into them.

7. Standardize dates and phone numbers

These two deserve their own steps because they fail in ways formulas cannot fully solve. A date like 03/04/2024 means 3 April in most of the world and 4 March in the United States, and nothing inside the cell says which. A phone number needs a country to be complete. Follow the dedicated guides:

• How to fix dates in Excel and CSV files
• How to fix phone numbers in Excel

8. Remove duplicates

Do this late, after trimming, case and format fixes, so that "John@Gmail.com " and "john@gmail.com" are recognized as the same person. The safe method (mark, review, then delete) is covered in how to remove duplicates in Excel without losing data.

9. Handle blanks and placeholders

People type "N/A", "n.a.", "-", "none", "TBD" and "0" when they mean "unknown". To a formula those are values. Normalize them to a true blank, or to one agreed label:

=IF(OR(TRIM(A2)="",UPPER(TRIM(A2))="N/A",TRIM(A2)="-"),"",A2)

To find real blanks fast, select the column, press F5, choose Special, Blanks, and Excel selects every empty cell. You can then highlight them or fill them from a rule. Do not fill a blank with a guess, an average or "the same as above" unless you are certain it is true. A visible gap is honest; an invented value is a hidden error.

10. Validate and document

Cleaning is only finished when you can show that the result is right. Four checks catch most problems:

• Reconcile counts and totals. Row count and the sum of key numeric columns should match the original, apart from rows you deliberately removed.
• Check each field against a rule. A basic email shape test: =AND(ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",A2,FIND("@",A2))),ISERROR(FIND(" ",A2))). Age between 0 and 120. Country in your list of allowed countries (=COUNTIF(Countries,A2)>0).
• Look for outliers. Conditional Formatting, Top/Bottom Rules, highlights the largest and smallest values. A price of 10000 in a list of 100s is usually a typo.
• Fix the cause. Use Data, Data Validation on the entry columns (drop-down lists, whole-number limits, date ranges) so the same errors cannot return.

Finally, write down what you did: the steps, the row counts before and after, and anything flagged for follow-up. Six months later, that note is the difference between a trustworthy file and a mystery.

A repeatable alternative: Power Query

If you clean the same kind of file regularly, formulas are the wrong tool, because you rebuild them each time. Power Query (Data, Get Data, From Table/Range) records each step. You apply Trim, Clean, Capitalize Each Word, Replace Values, Change Type and Remove Duplicates once, close and load, and the next month you paste new data and press Refresh. Two things to know: its Remove Duplicates is case-sensitive (Excel's own Remove Duplicates button is not), and when you change a column to a date type you can choose the locale used to read it, which is exactly where most date mistakes are made.

Mistakes that cause silent damage

When to stop doing this by hand

Manual cleaning is fine for a few hundred rows once. Move to Power Query, a script or a dedicated tool when any of these is true: you repeat the same clean-up every week or month; the file has more than a few thousand rows; several people touch it; or a mistake would cost real money (invoices, mailing lists, customer records, compliance data). The point is not speed alone. A rule applied by a program is applied identically to row 1 and row 90,000, which a tired person cannot do.