How to Remove Duplicates in Excel (5 Steps With Pictures)

Duplicate data is costly, it skews reports and leads to wasted marketing resources. Here’s how to remove duplicates in Excel spreadsheets:

Aug 10, 2022 - 21:02

0 7

Duplicate data is costly, it skews reports and leads to wasted marketing resources. Here’s how to remove duplicates in Excel spreadsheets:

Remove Duplicates in Excel

Select the cells you wish to remove duplicates from. Click on the “Data” tab at the top. Click “Remove Duplicates” to reveal a pop-up. Uncheck any columns with data you want to keep. Click OK to delete the duplicates.

Download 10 Excel Templates for Marketers [Free Kit]

Let’s run through it with an example!

Note: If you want to see duplicate data before removing them, scroll down to learn how to find and highlight duplicates in Excel. Also, make a copy of your worksheet so you still have your original data if you delete something important.

How to Remove Duplicates in Excel

1. Select the cells you wish to remove duplicates from.

Click on a cell and hold down the left mouse button. Then drag the cursor over the other cells you want to select.

to remove duplicates in excel, select all the cells you want

Or, use the Shift + arrow keyboard shortcut to select the range you want.

2. Click on the “Data” tab at the top.

t, step 2 to remove duplicates in excel

3. Click “Remove Duplicates” to reveal a pop-up.

tstep 3 to remove duplicates in excel, click on "remove duplicates" You should see a pop-up that looks like this:

ta popup to remove duplicates in excel

4. Uncheck any columns with data you want to keep.

tuncheck colums with data you don't want to delete in excel

5. Click OK to delete the duplicates.

tclick ok to delete duplicates

Excel will keep the first occurrence of the value by default.

How to Highlight Duplicates in Excel

Sometimes, you want to see the duplicate data before you delete it. Here’s how to find duplicate data in Excel:

1. Select the cells you wish to check for duplicates.

Click on a cell and hold down the left mouse button. Then drag the cursor over the other cells you want to select.

How to Highlight Duplicates in Excelto highlight duplicates in excel, select all the cells you want

Or, use the Shift + arrow keyboard shortcut to select the range you want.

2. Click “Conditional Formatting” from the home tab to reveal a drop-down.

First, navigate to the home tab if you’re on a different tab.

How to Highlight Duplicates in Excelclick conditional formatting to find duplicates in excel

Click on “Conditional Formatting” to reveal a dropdown.

click on Conditional Formatting to find duplicates in excel

3. Hover over “Highlight Cell Rules”

How to Highlight Duplicates in Excelhover over highligh cell rules to find duplicates in excel

4. Choose “Duplicate Values” from the options.

choose duplicate values to highlight duplicate values

5. Pick the formatting option you want and click OK.

How to Highlight Duplicates in Excelchoose the formatting style you want to highlight your duplicate excel data with

You’ll find all duplicates highlighted in the formatting style you choose.

Now you know how to find and remove duplicates in Excel!

Want more Excel automation hacks? Excel templates are a good place to start. They come pre-formatted with formulas that help you automate projections and more.

Editor's note: This post was originally published in [Month Year] and has been updated for comprehensiveness.