It is often said that data analysts spend most of their time cleaning data and only a small part analyzing it. Even if you are not an analyst, if you use spreadsheets you have probably been there: a report that does not add up because of an extra space, a filter that cannot find a customer because the name is in capitals or an import that fails because of a badly formatted date. This ten-step checklist will help you clean any file systematically.
Step 0: keep a copy of the original
Before touching anything, save the original file unchanged. Every cleaning job involves decisions, and if something goes wrong you will need to get back to the starting point. At the end, you will also be able to compare the original with the result.
Step 1: check the structure
A well-structured table has:
- A single header row, in the first row.
- One record per row.
- One data type per column.
- No merged cells.
- No total or subtotal rows inside the data.
If your file has decorative titles above the table, merged cells or subtotals in between, fix that first. Everything else will be easier.
Step 2: remove blank rows and columns
Blank rows break the continuity of the table. Remove them with a filter based on COUNTA or with the blank row remover on our Excel tools page. Do the same with columns that hold no data.
Step 3: trim extra spaces
Leading, trailing or double spaces between words are invisible but prevent matches. Apply =TRIM(A2) in a helper column, then copy and paste as values over the original column.
Step 4: remove non-printable characters
Data copied from websites, PDFs or old systems may contain line breaks, tabs or control characters. The CLEAN function removes them. A special case is the non-breaking space (character 160), which TRIM does not remove; use =SUBSTITUTE(A2,CHAR(160)," ").
Step 5: standardize upper and lower case
Choose a rule for each column:
- Proper names:
PROPER(“ana garcia” becomes “Ana Garcia”). - Email addresses:
LOWER. - Codes:
UPPER.
Step 6: fix data types
Check that numbers are numbers and dates are dates:
- Numbers stored as text are left-aligned and show a green triangle.
- Dates stored as text cannot be sorted chronologically.
- Codes with leading zeros should be text, not numbers.
Use “Convert to Number”, VALUE, DATEVALUE or the Text to Columns tool to fix them.
Step 7: standardize formats
The same things should be written the same way:
- Dates: one format for the whole column.
- Phone numbers: with or without country code, but always the same.
- Countries and states: “United States”, “US” and “USA” should be unified.
- Yes/no values: “Yes”, “Y”, “y”, “1” and “True” should be a single value.
Find and Replace, together with a filter to see each column’s unique values, is the fastest way to spot variants.
Step 8: remove duplicates
Once the data is standardized, duplicates are much easier to detect. Define which columns identify a record and remove repeats with Data > Remove Duplicates or with our online duplicate remover.
Step 9: validate values
Look for impossible or suspicious values:
- Dates in the future or before the company existed.
- Negative amounts where there should be none.
- Ages of 0 or 150 years.
- Email addresses without an @ sign.
Sort each numeric column from smallest to largest and check the extremes. Anomalies are usually at the top or bottom.
Step 10: compare the result with the original
Before signing off, compare the file with the copy you saved in step 0 using the Excel comparison tool on our tools page. You will see exactly which rows were removed and which cells changed, which helps you catch cleaning mistakes, such as a valid row deleted by accident.
Quick summary
| Step | Action | Tool |
|---|---|---|
| 1 | Check structure | Manual |
| 2 | Blank rows and columns | Filter or online tool |
| 3 | Spaces | TRIM |
| 4 | Odd characters | CLEAN, SUBSTITUTE |
| 5 | Case | PROPER, LOWER, UPPER |
| 6 | Data types | VALUE, Text to Columns |
| 7 | Formats | Find and Replace |
| 8 | Duplicates | Remove Duplicates |
| 9 | Validation | Sort and filter |
| 10 | Verification | File comparison tool |
Frequently asked questions
How often should I clean my data?
Every time you are about to use it for something important: a report, an import or a campaign. If you receive the same file regularly, document the steps so you repeat them the same way every time.
Can I automate the cleaning?
Yes. Power Query, included in Excel, lets you record cleaning steps and repeat them with one click when new data arrives.
What if I do not know the correct value?
Highlight the cell or add a notes column and check with whoever produced the data. A documented question is better than an invented value.
Which order of steps matters most?
Structure first, then normalization and finally duplicates. If you remove duplicates before normalizing, many repeated records will slip through because they are written slightly differently.
Conclusion
Data cleaning is not glamorous, but it is what separates a reliable report from one nobody believes. With these ten steps you will have a repeatable method for any file. Start with the structure, continue with formatting details and always finish by verifying the result. Our free tools help you with several of these steps without any formulas.
Try our free tools
Compare, convert, merge and clean your spreadsheets right in your browser, with nothing to install.
