Excel Advanced
Intermediate 13 min read Excel Advanced

Excel Data Cleaning

From spreadsheet garbage to analysis-ready

Spreadsheets arrive messy: spaces, mixed types, duplicates, inconsistent casing and broken dates. A set of core functions and checks turns that into analysis-ready tables.

data cleaningTRIMclean textduplicatesExcel functionsdata validation
Prerequisites: Excel Essential Skills
Before we start

What you will learn

  • Normalize text, numbers and dates with core functions
  • Find and remove duplicates safely
  • Validate entries at entry time with data validation
Introduction

What is this?

Excel data cleaning is applying a repeatable set of transformations: TRIM/SUBSTITUTE for text, ISNUMBER/VALUE for type forcing, TEXT/date functions for dates, Remove Duplicates, and Data Validation to prevent recontamination. The goal is one clean, type-consistent table that downstream pivots and lookups can trust.

Motivation

Why do you need it?

  • Most analyzes fail at the trust layer, not the math: a stray space breaks a lookup.
  • Duplicate rows silently double counts in aggregates.
  • Text dates and numbers-as-text block grouping, sorting and joining.
  • Cleaning steps recorded in Power Query become a reusable pipeline.
Think about it

Mental model

Imagine

Washing and sorting produce before cooking

Think of

Cleaning spreadsheet data

You don't cook dirty, mixed, same-looking crates. You wash (types), sort (dedupe), and trim before the dish — the recipe stays the same, the ingredients just behave.

Mechanics

How it works

  • TRIM() removes extra spaces; CLEAN() strips control characters; SUBSTITUTE() replaces tokens.
  • VALUE()/-- coerces numbers stored as text; TEXT()/formatting displays dates properly.
  • Remove Duplicates uses the selected columns to collapse exact-match repeat rows.
  • Data Validation drops a whitelist (List) or rules on a column so bad entries are rejected at entry.
  • Conditional formatting highlights blanks, negatives and anomalies for review before you commit.

The cleaning pass order.

Inspect
Normalize text
Fix types
Dedupe
Revalidate
Hands-on

Examples

A standard normalization recipe

A standard normalization recipe
// Text hygiene
=TRIM(CLEAN(A2))
=UPPER(N3) / =PROPER(N3)        // casing control
=SUBSTITUTE(A2, "USD", "")      // token removal
 
// Type forcing
=VALUE(A2)                       // text-number to real number
=ISNUMBER(A2)                    // detect non-numeric values
=TEXT(D2, "yyyy-mm-dd")          // canonical date string
 
// Blanks + duplicates
=COUNTIF(A$2:A$100, A2)          // flag repeat keys
=IF(COUNTIF(B$2:B$100, B2)>1,"DUP","OK")

This chain normalizes text noise, coerces types and flags duplicates before any aggregation touches the data.

Real world

In production

  • A monthly member export gets TRIMmed, deduped on member ID, then pivoted — without touching the source export.
  • Amounts imported as text from a legacy system get =-- coercion so SUM works.
  • An entry form enforces Data Validation lists so operators can't enter nonstandard region codes.
Guidelines

Best practices

  • Never clean in place: work on a transform column or a copy so you can trace every change.
  • Audit first: freeze a snapshot of counts (rows, blanks, dups) before and after cleaning.
  • Standardize casing and date format to one convention across the workbook.
  • Move repeatable cleaning into Power Query so it replays on the next export.
Watch out

Common mistakes

  • Removing duplicates on ALL columns when identity is one key — destroys legitimate repeat records.
  • Cleaning in place then losing the ability to explain the change to reviewers.
  • Coercing 'N/A' text with VALUE() producing #VALUE! that then spreads.
  • Not validating at entry, so the same mess returns next week.
When things break

Debugging tips

  • A lookup returns #N/A though values look identical — check TRIM and number-vs-text now.
  • SUM ignores part of a column — those cells are text; convert with VALUE.
  • Grouping by month fails — the 'date' is text; convert with DATEVALUE or TEXT.
  • Count duplicates with COUNTIF on the identity key before trusting Dedupe.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which function removes accidental leading/trailing spaces?

2

Removing duplicates on every column is usually correct.

Go deeper

Official documentation

Microsoft — TRIM function
docs
Microsoft — data validation
docs