Excel Remove Duplicates and Data Cleanup

Raw data from exports, forms, or databases often comes with duplicates, extra spaces, inconsistent capitalisation, and unwanted characters. Excel provides built-in tools and formulas to clean this data quickly so it is ready for analysis, reporting, or further use.

Remove Duplicates Tool

The Remove Duplicates tool finds and deletes rows where specified columns have identical values.

How to Use Remove Duplicates

Click any cell inside your data range. Go to Data → Remove Duplicates. A dialog lists all columns. Select which columns to check for duplicates — if all columns match, a row is a duplicate. Click OK. Excel reports how many duplicates it removed and how many unique rows remain.

Remove Duplicates Diagram

BEFORE:                         AFTER (checking all columns):
+-------+--------+--------+     +-------+--------+--------+
| Name  | Region | Score  |     | Name  | Region | Score  |
+-------+--------+--------+     +-------+--------+--------+
| Alice | North  | 90     |     | Alice | North  | 90     |
| Bob   | South  | 75     |     | Bob   | South  | 75     |
| Alice | North  | 90     | ←── | Carol | East   | 88     |  duplicate removed
| Carol | East   | 88     |     +-------+--------+--------+
+-------+--------+--------+

Warning: Remove Duplicates permanently deletes rows. Always work on a copy of your data or use Ctrl+Z immediately if the result is wrong.

TRIM — Remove Extra Spaces

Spaces before, after, or between words cause lookup failures and sort problems. TRIM removes all extra spaces except single spaces between words.

=TRIM(text)

A1: "  Rahul  Sharma  "

=TRIM(A1)  →  "Rahul Sharma"

TRIM removed:
- Leading spaces (before "Rahul")
- Trailing spaces (after "Sharma")
- Extra internal spaces (left only one space between first and last name)

CLEAN — Remove Non-Printable Characters

Data from external systems sometimes contains invisible characters (line breaks, null bytes) that TRIM does not remove. CLEAN handles these.

=CLEAN(text)

Removes line breaks, carriage returns, and ASCII control characters.
Often used together with TRIM:

=TRIM(CLEAN(A1))  →  removes both extra spaces and hidden characters

UPPER, LOWER, PROPER — Fix Capitalisation

A1: "aLICE mEHTA"

=UPPER(A1)   →  "ALICE MEHTA"
=LOWER(A1)   →  "alice mehta"
=PROPER(A1)  →  "Alice Mehta"

SUBSTITUTE — Replace Specific Text

SUBSTITUTE finds a specific piece of text inside a cell and replaces it with something else.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

A1: "2024-01-15"

=SUBSTITUTE(A1, "-", "/")  →  "2024/01/15"

A2: "aaa bbb aaa"
=SUBSTITUTE(A2, "aaa", "xxx")          →  "xxx bbb xxx"  (replaces all)
=SUBSTITUTE(A2, "aaa", "xxx", 2)       →  "aaa bbb xxx"  (replaces only 2nd instance)

TEXT to Columns — Split One Column into Many

Text to Columns splits the content of one column into multiple columns based on a delimiter (a character that separates the parts).

Using Text to Columns

Select the column to split. Go to Data → Text to Columns. Choose Delimited (split by a character) or Fixed Width (split at a set position). Select the delimiter — comma, space, semicolon, or a custom character. Choose the destination cell. Click Finish.

Text to Columns Diagram

BEFORE (one column):          AFTER (split by comma):
+----------------------+      +----------+----------+
| "Alice, North"       |      | Alice    | North    |
| "Bob, South"         |      | Bob      | South    |
| "Carol, East"        |      | Carol    | East     |
+----------------------+      +----------+----------+

Find and Replace for Bulk Cleanup

Press Ctrl + H to open Find and Replace. Type what to find in the "Find what" box. Type the replacement in the "Replace with" box. Click Replace All to change every instance at once.

Find:    "N/A"      Replace with: (blank)  → removes all N/A entries
Find:    "  "       Replace with: " "      → reduces double spaces to single
Find:    "$"        Replace with: (blank)  → removes dollar signs from numbers

Flash Fill for Pattern-Based Cleanup

Flash Fill (Ctrl+E) detects the pattern of what you type in one cell and applies it to all other cells in the column. It excels at extracting parts of text without formulas.

A1: "Alice Mehta (North)"
B1: Alice Mehta           ← type this manually

Press Ctrl+E in B2:
B2: Bob Singh             ← Flash Fill extracts the name from "Bob Singh (South)"
B3: Carol Rao             ← extracted from "Carol Rao (East)"

Data Cleanup Checklist

Clean data is the foundation of accurate analysis. A VLOOKUP fails because of a trailing space. A chart shows wrong totals because of duplicates. A sort puts names out of order because of inconsistent capitalisation. Running through this cleanup checklist before analysis prevents hours of confusion later.