How to Remove Duplicates in Excel Without Losing Your Data

Duplicate data in Excel can creep in surprisingly easily. Maybe you combined two exports, copied a list twice, imported customer records from different systems, or inherited a spreadsheet that has been passed around for years.

Excel can remove duplicates in a few clicks. The dangerous bit is making sure it removes the right duplicates.

Before you remove anything, make a copy

This is the boring step that can save a very un-boring disaster. Duplicate removal deletes data from the selected range, so keep an untouched copy of the original worksheet or workbook first.

If you are not yet sure which records should go, highlight or filter the duplicates before deleting them. That lets you inspect what Excel has identified.

How to find duplicates in Excel first

Select the cells you want to check.
Go to Home.
Select Conditional Formatting.
Choose Highlight Cells Rules, then Duplicate Values.
Choose the formatting you want and select OK.

Excel will highlight the repeated values without deleting anything. This is useful when you want to see what is going on before reaching for the axe.

How to remove duplicates in Excel

Select the range containing your data, or click inside your Excel table.
Go to the Data tab.
Select Remove Duplicates.
Choose which columns Excel should use to decide whether two records are duplicates.
Select OK.

Excel keeps the first occurrence and removes later matching records. It will then tell you how many duplicate values were removed and how many unique values remain.

The column selection is the important bit

This is where a simple job can turn into spreadsheet archaeology.

Imagine your data contains Name, Email, Order Number and Order Value. Two rows might contain the same customer name and email but represent two completely different orders.

If you tell Excel to judge duplicates using only the customer columns, it could treat one of those legitimate orders as a duplicate and remove the entire row.

Choose the columns that genuinely define a duplicate in your data. Do not automatically tick everything or untick everything without thinking about what each row represents.

What if you only want a list of unique values?

You do not always need to delete anything. Excel can filter for unique records, and newer versions of Excel also include the UNIQUE function, which can return a separate list of unique values while leaving the original data alone.

That is often the safer choice when the original dataset needs to remain intact.

Why duplicates are sometimes harder than they look

Real-world data rarely behaves itself. Records that look duplicated to a human might contain tiny differences that stop Excel treating them as identical.

You might have extra spaces, inconsistent capitalisation, differently formatted dates, slightly different company names, missing fields or reference numbers entered in different formats.

And sometimes the opposite problem occurs: two rows look similar but are actually separate transactions that both need to stay.

When doing it yourself stops making sense

Removing duplicates from one tidy list is easy. Removing them from 14 spreadsheets containing thousands of rows, inconsistent fields and overlapping records is a different animal.

That sort of job may also involve combining files, standardising columns, cleaning values, deciding what constitutes a genuine duplicate and checking that nothing useful disappeared along the way.

Can’t be faffed?

If your spreadsheet has become a duplicate-infested swamp, you do not have to spend your afternoon picking through it row by row.

Faff It Off. Send us what you’ve got, tell us what you need at the end, and we’ll quote for cleaning and sorting it for you.