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

Text functions for cleaning

Functions can be nested. A clean credit limit from text like ₦1,200,000.00:

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

(Inside the brackets of a Table reference, a column name with a space needs its own brackets: [@[Credit Limit]].)

Standardising categories with a mapping table. Don't write a giant nested IF for 23 region spellings. Make a two-column table, RegionMap, with every messy spelling (in lower case) and its clean version, then look it up:

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

Anything that shows CHECK is a spelling you haven't mapped yet.

Remove duplicates (Data → Remove Duplicates) deletes rows that repeat in the columns you choose. Excel ignores capital letters when comparing, but not spaces, so trim first.

Dates: the trap. The export mixes 2023-07-11, 22/10/2023 and 5-Mar-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. 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.

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 below do.

03Example

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.

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

   • Name: =PROPER(TRIM(CLEAN([@[Customer Name]])))
   • Region clean: the XLOOKUP on RegionMap
   • Limit: the nested SUBSTITUTE/VALUE formula Here are the helper columns on Kolanut's export, next to the raw data:

   

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

4. 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.

5. On Clean, Data → Remove Duplicates (Alt, A, M) on the name column:

   

   1. Columns: untick everything except the cleaned name. With every column ticked, two copies count as duplicates only if every column matches, and these copies have different phone formats.
   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).

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

05Practice

06Challenge

07Check your understanding