Data cleaning in Excel

01The problem

Kolanut's customer list, exported from its old system, is a mess: names with stray spaces and random capitals, regions spelled 23 different ways, three date formats, credit limits stored as text with ₦ signs, and 12 customers listed twice. The new system needs a clean list, and finance wants the total credit extended to customers.

02The concept

Cleaning means turning data that a person can read into data that a formula can trust. Excel treats Lagos, LAGOS and Lagos (with a space) as text, and a SUMIFS for "Lagos" will miss the one with the space. A credit limit typed as ₦2,050,000 is text too, so SUM skips it without a word.

Step 1: look before you fix

Before changing anything, find out what's wrong and how often. Here is what a quick inspection of the 102 rows of customer_list_raw.csv finds:

Counting problems first matters for two reasons: you know when you've finished, and you can prove the fix worked by counting again afterwards.

Useful checks for finding problems:

TRIM

Removes spaces at the start and end, and turns runs of spaces inside the text into one.

=TRIM(text)

TRIM only removes the ordinary space character. Text copied from websites sometimes contains a non-breaking space (character 160) that TRIM leaves alone. If TRIM seems not to work, use =TRIM(SUBSTITUTE(x, CHAR(160), " ")).

CLEAN

Removes invisible control characters, such as line breaks, that some systems put into exports.

=CLEAN(text)

You can't see what it removes, which is why it's often used together with TRIM as a matter of habit: =TRIM(CLEAN(x)).

PROPER, UPPER and LOWER

Change the capitals.

PROPER is right for names, but check the results: it turns ABC Stores into Abc Stores and McDonald into Mcdonald. LOWER is useful for matching: compare lower-case versions and capitals stop mattering.

SUBSTITUTE

Replaces every occurrence of some text with other text.

=SUBSTITUTE(text, old_text, new_text)

Replacing with "" (nothing) deletes. To remove two different things, nest one SUBSTITUTE inside another: the inner one runs first.

VALUE

Turns text that looks like a number into a real number.

=VALUE(text)

=VALUE("2050000") gives the number 2,050,000, which now sits on the right of the cell and can be summed. VALUE can't cope with a ₦ sign, so remove that first. Putting it all together for a credit limit:

=IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(TRIM([@[Credit Limit]]),"₦",""),",","")), "")

Read it from the inside out:

1. TRIM(...): "₦2,050,000" (no change here, but it handles stray spaces)
2. SUBSTITUTE(..., "₦", ""): "2,050,000"
3. SUBSTITUTE(..., ",", ""): "2050000"
4. VALUE(...): the number 2050000. "500,000.00" becomes 500000 the same way.
5. IFERROR(..., ""): a blank limit gives an error at step 4, so show a blank instead.

LEFT, RIGHT, MID and LEN

Take part of a text value.

These are perfect for the phone numbers. Every Nigerian mobile number ends in the same 10 digits, whatever the prefix: 08136236612, +234 813 623 6612 and 2348136236612 are all the same phone. So remove spaces and +, keep the last 10 digits, and put a 0 in front:

="0"&RIGHT(SUBSTITUTE(SUBSTITUTE([@Phone]," ",""),"+",""),10)

All 102 phones come out as 11 digits starting 07, 08 or 09. The & joins text, and keeping the result as text protects the leading zero.

Standardising categories with a mapping table

The Region column has 23 spellings of 6 regions. After LOWER(TRIM(...)) there are still 16: south west, south-west, sw and so on. You could write a huge nested IF, but a mapping table is easier to read, check and extend.

Make a two-column table called RegionMap with every messy spelling (lower case, trimmed) and its clean version:

Then look each row up with XLOOKUP from lesson 6:

=XLOOKUP(LOWER(TRIM([@Region])), RegionMap[raw], RegionMap[clean], "CHECK")

Anything that shows CHECK is a spelling you haven't mapped yet. Add a row to RegionMap and it fixes itself. When nothing shows CHECK, every row has one of the six clean regions.

Remove Duplicates

Data → Remove Duplicates deletes rows that repeat in the columns you choose, keeping the first copy it meets.

Two things decide whether it works:

1. Clean first. Excel ignores capitals when comparing, but not spaces: Ada Stores and Ada Stores look like two customers. Trim before removing duplicates.
2. Choose the columns. Tick only the columns that define "the same customer", here the cleaned name. With every column ticked, two copies count as duplicates only if every column matches exactly.

Because it keeps the first copy, sort first if one copy is better. To keep the copy that has a credit limit, sort the limit column largest to smallest before removing duplicates.

To find duplicates without deleting anything, count each name: =COUNTIF([Name], [@Name]). Anything above 1 appears more than once.

Dates: the trap

The export mixes 2023-07-11, 22/10/2023 and 15-May-2024. On a computer set to US format, Excel reads 01/09/2022 as 9 January and leaves 22/10/2023 as text, because there is no 22nd month. Half your dates are wrong and the other half aren't dates.

You can spot the problem: real dates sit on the right of the cell, text dates on the left, and =ISNUMBER([@[Date Joined]]) is FALSE for text.

The reliable fix is to import through Power Query and tell it the dates are day-first:

1. Data → From Text/CSV → choose the file → Transform Data.
2. Right-click the Date Joined column → Change Type → Using Locale…
3. Data type Date, locale English (United Kingdom) (day-first), OK.
4. Home → Close & Load.

Check a few rows against the raw file afterwards: 01/09/2022 should now be 1 September 2022, and 15-May-2024 should be 15 May 2024.

Import it properly first

Opened with Data → From Text/CSV, the messy file comes in far better than by double-clicking:

1. File Origin: 65001 Unicode (UTF-8), so ₦ is read correctly.
2. Phone stays as text, leading zeros intact.
3. Date Joined is recognised as dates. It read them day-first because this computer uses a UK date format. On a US-format computer, change the type with a locale in Power Query, as described above.
4. Credit Limit is recognised as numbers; the blank one shows as null.

Import gives the cleanest starting point, but names, regions and duplicates still need fixing, and that's what the formulas do.

03Example

Three raw rows, and what the cleaning formulas make of them:

Each cleaned value is one formula from this lesson. The name is PROPER(TRIM(...)), the region is the RegionMap lookup, the date is the Power Query locale, and the limit is the nested SUBSTITUTE/VALUE.

04Walkthrough

A clean, repeatable workflow:

1. Keep the raw sheet untouched. Load the file (with the Power Query date fix above) into a sheet called Raw. If something goes wrong later, you can always start again from it.

2. Count the problems with the checks from step 1 of this lesson, and write the counts down.

3. Add helper columns next to the data, one per cleaned field:

   • Name: =PROPER(TRIM(CLEAN([@[Customer Name]])))
   • Region clean: the XLOOKUP on RegionMap
   • Phone clean: the RIGHT/SUBSTITUTE formula
   • Limit: the nested SUBSTITUTE/VALUE formula

   Here are the helper columns on Kolanut's export, next to the raw data:

   

4. Filter each helper column for CHECK, errors and blanks, and fix the mapping until none are left.

5. Copy the helper columns and paste them into a new sheet Clean with Paste Special → Values (Ctrl + Alt + V, then V). They're now fixed values, not formulas.

6. On Clean, sort by Limit largest to smallest, then Data → Remove Duplicates (Alt, A, M) on the name column:

   

   1. Columns: untick everything except the cleaned name.
   2. My data has headers keeps the header row out of the comparison.
   3. OK reports how many duplicates were removed (12 here) and how many unique rows remain (90).

7. Count again. Six distinct regions, 90 customers, 90 distinct phones, every limit a number or blank.

8. Log it: on a Notes sheet, write what you did and the row counts before and after (102 → 90). Someone will ask.

Summary

05Practice

06Challenge

07More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

08Check your understanding