Excel data cleaning practice: a messy file with tasks and answers

Real data is messy. The export from the old order system has a space after half the city names, the amounts came out as text, and somebody typed every customer name in capitals. None of it gives an error message. It just gives the wrong answer: a SUM that is too low, a COUNTIF that misses rows, a lookup that returns #N/A for a customer you can see on the screen.

Cleaning data is the part of Excel work nobody shows you in a course, and it is often half the job. So here is a file that is messy on purpose: 60 orders exported from an old system, with every problem you are likely to meet. Each task asks you to find or fix one of them, and the answers are on this page.

Messy data: 60 orders from an old system

The Orders sheet has one row per order line and seven columns. Here is what each column should contain, and what is wrong with it:

The data is made up, but every problem in it comes from real exports. Look at the file before you start. Some problems are easy to spot, like a name in capitals. Others are invisible: a trailing space looks exactly like no space, and a number stored as text looks like a number. Excel gives a hint, though. Text sits on the left of the cell and numbers on the right, and a number stored as text gets a small green triangle in the corner.

Why exports get messy

Almost no one types messy data on purpose. It happens on the way between systems. A web form lets people type their city freely, so you get chicago, Chicago and CHICAGO. An old system pads every field with spaces to a fixed width, and the export keeps them. A CSV file has no idea what a number or a date is, so Excel guesses, and the guess depends on the settings of the computer that opens it. Copy a table from a web page or a PDF and you often get invisible characters along with it.

The problems are the same everywhere, which is good news. Once you have cleaned this file, you know the moves for most exports you will meet: from the bank, from the webshop, from the CRM or from a colleague who built a sheet ten years ago.

The golden rule: fix it next to the data

Never clean the original column directly. Add a helper column next to it, write a formula that cleans the first row, check it, and fill it down. When the whole column looks right, copy it and use Paste Special, Values to put the clean text back, or simply keep working with the helper column.

That way you can always see the before and after side by side, and you can start over if a formula was wrong. Save a copy of the file before you start, because the answers on this page only match the data as it is in the file. The data runs from row 2 to row 61.

Cleanup tasks on the messy data

The first tasks fix a single cell, so you can see what each function does. The later ones work on whole columns and find problems you can't see. The tasks are in the same order as in the file.

Extra spaces with TRIM

TRIM removes spaces at the start and end of a text, and turns two or more spaces in a row into one. =TRIM(B2) gives the name in B2 with clean spacing. Fill it down a helper column and every name is fixed.

The spaces you can't see are the dangerous ones. "Chicago " with a trailing space is a different value from "Chicago" to Excel, so COUNTIF, SUMIF and every lookup miss it. Practice it in Remove extra spaces with TRIM.

Fix the case with PROPER

PROPER gives each word a capital first letter and makes the rest lower case. Wrap it around TRIM to fix both problems at once:

=PROPER(TRIM(B3))

UPPER and LOWER do the opposite: everything in capitals or everything in lower case. PROPER is not perfect for names. It turns "mcdonald" into "Mcdonald", so names like that need a manual check. Practice it in Fix the case of names with PROPER and Fix messy names with PROPER and TRIM.

Count what is really there

COUNTIF does not care about case, but it does care about spaces. Compare these two answers:

=COUNTIF(C2:C61,"Chicago")

=SUMPRODUCT(--(TRIM(C2:C61)="Chicago"))

The first one misses every cell with a space before or after the city. The second trims every cell before it compares, and counts the matches. The difference between the two is the number of rows a report built on the raw data would have left out. The double minus turns TRUE and FALSE into 1 and 0 so SUMPRODUCT can add them. The comparison with = ignores case, just like COUNTIF. Practice the plain version in COUNTIF with a text condition.

The same trick works on the status column. =SUMPRODUCT(--(TRIM(G2:G61)="Paid")) counts the paid orders however they were typed.

To clean the status column itself, =PROPER(TRIM(G2)) turns paid, PAID and Paid with a trailing space into the same Paid. Once every row says either Paid or Unpaid, a plain COUNTIF works again, and so do filters and pivot tables. A column with a fixed list of values is also a good candidate for a drop-down list, so the next person who types in it can't make the same mess.

Find the blanks

=COUNTBLANK(C2:C61) counts empty cells in the city column. Blank cells are not always an error, but you need to know about them before you count, because a blank city disappears from every city total. Decide whether to fill them in, mark them as Unknown or leave them out, and write down which you chose.

Go To Special is the quick way to see them: select the column, press F5, click Special and choose Blanks. Excel selects every empty cell, and you can type Unknown and press Ctrl+Enter to fill them all at once.

Numbers stored as text

This is the most expensive problem in the file, because it gives the wrong total without a single error. SUM skips text, so =SUM(E2:E61) only adds up the amounts that happen to be real numbers.

First find out how big the problem is. ISTEXT returns TRUE for a text cell, and SUMPRODUCT counts them:

=SUMPRODUCT(--ISTEXT(E2:E61))

Then add everything up the right way. VALUE turns text that looks like a number into a number, and leaves real numbers alone:

=SUMPRODUCT(VALUE(E2:E61))

Compare the two totals. The gap between them is money a report would have missed. To fix the column for good, add a helper column with =VALUE(E2), fill it down and paste it back as values. Two other quick fixes: select the column and use Data, Text to Columns, Finish, or type 1 in an empty cell, copy it and use Paste Special, Multiply on the amounts.

Dates stored as text

The order dates came out as text like 2025-03-14. They look like dates, but you can't subtract them, group them by month or compare them with DATE. Build a real date from the pieces with LEFT, MID and RIGHT:

=DATE(LEFT(D2,4),MID(D2,6,2),RIGHT(D2,2))

LEFT takes the first four characters, the year. MID takes two characters from position 6, the month. RIGHT takes the last two, the day. DATE puts them together into a real date that you can format however you like. Practice taking text apart in Take the prefix with LEFT.

While the dates are still text, you can count them with a wildcard. =COUNTIF(D2:D61,"2025-03*") counts every date that starts with 2025-03, which is every order in March. It is a handy trick, but convert the dates as soon as you can. Text dates sort correctly only because this format puts the year first.

Phone numbers in three formats

The phone column mixes 312 555 0147, 312-555-0147 and 3125550147. To get digits only, remove the spaces and the dashes. SUBSTITUTE replaces one piece of text with another, and replacing with "" removes it. Nest two of them to remove both:

=SUBSTITUTE(SUBSTITUTE(F2," ",""),"-","")

The inner SUBSTITUTE removes the spaces, and the outer one removes the dashes from what is left. The result is text, which is what you want for a phone number. Stored as a number, a phone number that starts with 0 loses its first digit.

Find the duplicates

Some orders were exported twice. To find them, count how many times each order number appears. In a helper column, =COUNTIF($A$2:$A$61,A2) gives 1 for a unique order and 2 for one that appears twice. Filter on 2 and you see every duplicate.

To count the rows that are duplicated in one formula, give COUNTIF the whole column as its criterion. It then returns one count per row, and SUMPRODUCT counts the ones above 1:

=SUMPRODUCT(--(COUNTIF(A2:A61,A2:A61)>1))

And to count the different orders, add up 1 divided by each count. An order that appears twice counts as a half twice, which makes one:

=SUMPRODUCT(1/COUNTIF(A2:A61,A2:A61))

In Excel 365 you can also write =COUNTA(UNIQUE(A2:A61)). UNIQUE lists each value once, and COUNTA counts the list. Practice it in List each customer once with UNIQUE.

To remove the duplicates for good, use Data, Remove Duplicates. Pick the columns that have to match for two rows to count as the same. Make a copy first: Remove Duplicates deletes rows, and Undo is the only way back.

In which order to clean

When a file has several problems at once, the order matters. Fix the things that change values before the things that compare them:

1. Save a copy of the original export.
2. Trim every text column. Spaces break everything else, so they go first.
3. Fix the case of names and categories with PROPER, UPPER or LOWER.
4. Convert numbers and dates from text to real values.
5. Standardize formats like phone numbers and codes.
6. Find and remove duplicates. Do it last, because two rows that differ only by a trailing space don't look like duplicates until the spaces are gone.
7. Check the totals against the source: the number of rows and the sum of the amounts.

Write down what you changed. When someone asks why your total differs from the old report, you can show them.

Check that the cleanup worked

A clean column should pass a few quick tests. Run them before you build anything on top of the data:

• List the values. Click the filter button on the city column. A clean column shows four cities and maybe Unknown. If you still see Chicago twice, one of them has a character TRIM did not remove, such as a non-breaking space from a web page. =SUBSTITUTE(C2,CHAR(160)," ") turns it into a normal space that TRIM can handle.
• Compare the totals. The sum of the clean amounts should equal the SUMPRODUCT with VALUE on the raw column. If it is lower, some amounts are still text.
• Count the rows. After removing duplicates, the row count should equal the number of different order numbers.
• Sort the dates. A real date column sorts from oldest to newest. If a few dates end up at the top or bottom, they are still text.
• Check the alignment. Select the clean amounts and look at the status bar. If it shows Sum and Average, they are numbers. If it only shows Count, they are still text.

Cleanup tasks also show up in Excel tests for jobs that work with data, often as "this file has errors, find them". Interviewers like them because they show whether you check your numbers or trust the first total you see. More in the guide to Excel for job interviews.

Practice without an answer key

Once you have solved the tasks, try cleaning the whole file into a new sheet:

• Build a clean copy with one formula per column: TRIM and PROPER for names, TRIM for cities, VALUE for amounts, DATE for dates, SUBSTITUTE for phone numbers. Fill down and paste as values.
• Remove the duplicates from the clean copy with Remove Duplicates, and check the row count.
• Replace the blank cities with Unknown using Go To Special.
• Build a summary of the clean data with the amount per city and per status. Compare it with the same summary on the raw data.
• Use Flash Fill: type the first clean name by hand in a new column, start typing the second and press Ctrl+E. Excel guesses the pattern for the rest. Check the result, because Flash Fill guesses.

When an answer doesn't match

• TRIM in the formula but not in the count. Clean the column first, then count the clean column, or trim inside the formula.
• A VALUE error. VALUE returns #VALUE! when the text is not a number, for example when it has letters in it. Find the cell and fix it by hand.
• Too few duplicates. If you removed spaces after you looked for duplicates, look again.
• Date and month swapped. LEFT, MID and RIGHT only work when every date has the same layout. Check a few rows before you fill down.

Messy data in Google Sheets

The file works in Google Sheets. Upload it to Google Drive and open it with Google Sheets. TRIM, PROPER, VALUE and SUBSTITUTE work the same way, and Data, Data cleanup has Trim whitespace and Remove duplicates as menu items. See the other differences in Google Sheets vs. Excel.

More practice data

When the data is clean, the fun starts. These files are clean on purpose, so you can focus on the formulas:

• Sales and employee data: 240 orders and 60 employees, for SUMIF, COUNTIFS, averages and dates.
• Inventory data: 80 products with stock and prices, for XLOOKUP and SUMPRODUCT.
• Customer data: 150 customers, for lookups, text functions and counting with two conditions.
• Finance data: a year of bank transactions, for income, expenses, results and percentages.

The full walkthrough of every cleanup tool, with examples, is in the guide to cleaning data in Excel.

In short

Messy data does not give errors. It gives wrong answers. TRIM removes spaces, PROPER fixes case, VALUE turns text into numbers, DATE with LEFT, MID and RIGHT builds real dates, SUBSTITUTE removes characters, and COUNTIF finds duplicates. Clean in a helper column, in the right order, and check the totals when you are done.

To drill one cleanup function at a time on a small sheet, start with the exercises below. They check your answer right away and show a hint when the formula is wrong. The first exercise in each topic is free.