Data Cleaning in Excel

Why Data Cleaning Matters

Data analysts spend a significant portion of their time cleaning data before any analysis can begin. Real-world data from business systems, user forms, database exports, and third-party sources is rarely clean. It contains inconsistencies, errors, missing values, and formatting issues that will corrupt your analysis if not addressed.

The common saying among data professionals is that 80% of data work is cleaning and only 20% is analysis. This is an exaggeration, but the underlying point holds: the quality of your analysis depends entirely on the quality of your data.

Common Data Quality Issues

Before you can fix problems, you need to identify them. Common issues include:

Duplicates: The same record appears multiple times due to system errors, multiple imports, or manual entry.

Missing values: Blank cells where data should exist. May be truly missing or may represent zero, unknown, or not applicable -- you need to understand which.

Inconsistent formatting: "London", "london", "LONDON", and "London " are all different values in Excel but represent the same city. Same issue with dates (01/06/2024 vs 2024-06-01), phone numbers (+44 20 1234 5678 vs 02012345678), and categories.

Wrong data types: Numbers stored as text, dates formatted as strings, text in numeric columns.

Out-of-range values: Ages of -5 or 200, negative quantities, future dates in historical records.

Structural issues: Merged cells, blank rows, headers in the middle of data, multiple tables in one sheet.

Step 1: Remove Duplicates

Excel has a built-in tool for removing duplicates:

1. Click anywhere in your data
2. Go to Data > Remove Duplicates
3. Select which columns define a duplicate (usually all columns, or a unique identifier column)
4. Click OK

Before removing, always:

• Make a backup copy of the raw data
• Check how many duplicates exist (Data > Remove Duplicates shows the count before removing)
• Verify the duplicates are genuine and not legitimate repeated records

To find (but not remove) duplicates:

=COUNTIF($A$2:$A$1000, A2) > 1   -- returns TRUE if this value appears more than once

Step 2: Handle Missing Values

First, find all blanks. Select the entire dataset and use:

• Ctrl + G > Special > Blanks (highlights all empty cells)
• Or: =COUNTBLANK(A:A) to count blanks in a column

Then decide how to handle them:

• Delete rows with missing values if the row cannot be used without the data
• Fill with a value if the missing value has a known substitute (e.g., fill missing region with "Unknown")
• Fill with the column average or median for numeric fields (appropriate only in some contexts)
• Leave blank and filter them out in analysis

To fill all selected blank cells with the same value:

1. Select the column
2. Ctrl + G > Special > Blanks
3. Type the value you want (e.g., "Unknown")
4. Press Ctrl + Enter (fills all selected blanks simultaneously)

Step 3: Standardise Text

Inconsistent text is one of the most common data quality issues. Use these techniques:

Fix case inconsistencies

=UPPER(A2)      -- ALL CAPS
=LOWER(A2)      -- all lowercase
=PROPER(A2)     -- Title Case

Remove extra spaces

=TRIM(A2)       -- removes leading, trailing, and extra internal spaces

Standardise specific values using IF

=IF(OR(LOWER(A2)="uk", LOWER(A2)="united kingdom"), "UK", A2)

Replace specific characters

=SUBSTITUTE(A2, "-", "")    -- remove hyphens from phone numbers
=SUBSTITUTE(A2, ",", ".")   -- replace commas with periods (decimal formatting)

After applying cleaning formulas, convert to values

Copy the cleaned column > Paste Special > Values Only (Ctrl + Alt + V, then V, then Enter). This replaces the formulas with static values, which you then need to use to replace or delete the original column.

Step 4: Fix Data Types

Convert text to numbers

If numbers are stored as text (shown by green triangle in the corner or left-aligned):

• Select the cells > Click the warning icon > Convert to Number
• Or: multiply by 1 using Paste Special (copy a cell containing 1 > select text-numbers > Paste Special > Multiply)
• Or: use =VALUE(A2)

Convert text to dates

If dates are stored as text:

• Use =DATEVALUE(A2) to convert text dates
• For dates in non-standard formats: use Text to Columns (Data > Text to Columns > Delimited > Date) and specify the date format

Force date format

If dates display inconsistently:

• Select the column > Ctrl + 1 > Number tab > Date > select format

Step 5: Validate Data Ranges

Check that values fall within expected ranges using conditional formatting:

1. Select the data column
2. Home > Conditional Formatting > Highlight Cell Rules > Greater Than / Less Than
3. Set the threshold that defines an outlier

Or use a formula to flag out-of-range values:

=IF(OR(A2<0, A2>1000), "OUT OF RANGE", "OK")

Flash Fill: Excel's Pattern Recognition

Flash Fill (Ctrl + E) detects patterns in your data and auto-completes a column. Extremely useful for:

• Splitting "FirstName LastName" into two columns
• Extracting domain names from emails
• Reformatting phone numbers
• Standardising address formats

To use: Type the desired output for the first one or two rows, then press Ctrl + E. Excel detects the pattern and fills the rest of the column.

Power Query: The Advanced Cleaning Tool

For large datasets or repeated cleaning tasks, Power Query (Data > Get & Transform > From Table/Range) is far more powerful than manual formulas:

• Steps are recorded and replayable (click Refresh to reapply all cleaning steps when new data arrives)
• Can handle transformations that are difficult with formulas
• Does not modify source data

Power Query is a separate skill worth learning after mastering the formula-based approach covered here.

Key Takeaways

• Data cleaning typically accounts for the majority of analytical time -- clean data is the foundation of reliable analysis.
• Common issues include duplicates, missing values, inconsistent text formatting, wrong data types, and out-of-range values.
• Always work on a copy of the raw data and use formulas that generate new clean columns rather than modifying data in place.
• Flash Fill (Ctrl + E) is a powerful tool for pattern-based data transformation without writing formulas.
• After applying cleaning formulas, convert to values (Paste Special > Values) to remove formula dependencies and protect the clean data.

Practice Exercise

Download a publicly available messy dataset (try data.gov.uk, data.world, or Kaggle). Perform a full cleaning workflow:

1. Document all data quality issues you find before cleaning
2. Remove duplicates
3. Handle all missing values (document your decisions)
4. Standardise text fields (case, spaces, abbreviations)
5. Fix data type issues
6. Flag any out-of-range values
7. Document what you did and why

Compare the record count before and after cleaning.