Skip to content
Get a free demo
Guide

How to clean a messy spreadsheet

Work on a copy, go in this order, and compare your totals at the end so you know nothing was lost.

Before you change anything

Save a copy and work on that. Then write down three numbers from the original: how many rows it has, the total of its main money column, and how many distinct customers or items it holds. At the end those numbers tell you whether anything went missing.

If the sheet came from a CSV, don’t just double-click it. Excel guesses at every column as it opens, and that is when ZIP codes lose their leading zeros. Use Data > From Text/CSV instead, and set ID and ZIP columns to Text before loading.

Step 1: get one heading row at the top

Exports often start with a report title, a date and a blank line. Delete those rows so the headings sit in row 1. Unmerge any merged cells with Home > Merge & Center, then fill in the gaps that leaves behind.

Give every column its own short name. Two columns both called Amount will cause trouble later, and a column with no heading is invisible to any tool that reads the file.

Step 2: remove stray spaces and invisible characters

A trailing space makes “Smith ” and “Smith” two different customers, and you can’t see it. In a helper column, use:

=TRIM(A2)

TRIM removes spaces at both ends and reduces runs of spaces to one. It does not remove the non-breaking spaces that come with text copied from a web page, or line breaks. For those, wrap it:

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

Copy the results, use Paste Special > Values over the original column, and delete the helper. Skip this on columns holding real numbers, since the formula returns text.

Step 3: delete blank rows and columns

Blank rows break tables, sorts and filters, because Excel treats them as the end of your data. Select a column that should always be filled, then use Home > Find & Select > Go To Special > Blanks to see them.

Be careful with the common trick of deleting every row that has a blank in one column. That also removes real records that happen to be missing that one value. Filter first and look at what you are about to delete.

Step 4: deal with duplicates

Decide what makes two rows the same record before you delete anything. Our guide to removing duplicates in Excel covers how to review them first, and why identical rows are sometimes both genuine.

Step 5: make each column consistent

Sort or filter each text column and read down it. You will spot NY, ny and New York quickly that way. Pick one form and use Find and Replace, with Match entire cell contents turned on so you don’t change text inside longer words.

For columns that should hold numbers, look for cells aligned to the left, which Excel is treating as text. Words such as n/a or TBD in a number column will break a sum or an import, so decide whether they should simply be empty.

Step 6: check dates and long numbers by eye

These two cause the most silent damage, and no formula can fix them with certainty.

Values that are easy to damage
What you seeWhat may be wrongWhat to do
03/04/2025It could be March 4 or April 3, depending on who typed it.Find out the source’s format. Look for a value above 12 in the same column to settle it.
1.23457E+11Excel is showing a long number in scientific notation.Widen the column or format it as Number. If the digits are gone from the file itself, go back to the source.
7302 in a ZIP columnA leading zero was dropped, since the real code is 07302.Format as Text and restore it, or use =TEXT(A2,"00000") in a helper column.
A 16-digit ID ending in 0Excel keeps only 15 significant digits in a number, so the last digit was replaced.That digit can’t be recovered from this file. Export again with the column set to Text.

Microsoft documents the 15-digit limit: past the fifteenth digit, Excel changes digits to zeros in a cell formatted as a number. Formatting the cell as Text afterwards does not bring them back.

Step 7: compare your totals

Go back to the three numbers from the start. The row count should drop by exactly what you removed. The money total should match, unless you removed true duplicates or total rows, and you should be able to explain any difference to the cent.

Rows labelled Total are worth a second look at this stage, because they count twice if the column gets summed later on.

Questions

Questions about cleaning spreadsheets

What is the fastest way to clean a messy CSV?

Run it through a cleaner that handles the mechanical parts, then spend your own time on the judgement calls. The file cleaner on this site trims spaces, removes blanks and exact duplicates, finds the heading row and lists what it left for you.

Why did Excel change my numbers when I opened the file?

Opening a CSV by double-clicking lets Excel convert every column it recognizes. Long numbers, codes with leading zeros and anything resembling a date are the usual victims. Importing through Data > From Text/CSV lets you set those columns to Text first.

Can a formula tell which format a date is in?

Only partly. A value such as 03/14/2025 can only be month first, which is good evidence for the rest of that column. A column where every value is 12 or below stays ambiguous, so you need to ask whoever produced the file.

Tired of cleaning the same file?

Messy data usually comes from how it gets typed in. A custom app with proper fields and checks stops most of it at the door.

Get a free demo