How to Quickly Remove Duplicates in Excel

Jamie Read more November 5, 2023

_{^{Last updated/edited by Steve Larner on Nov. 5, 2023.}}

How to Quickly Remove Duplicates in Excel

The more complicated a spreadsheet gets, the easier it is to duplicate cells, rows, or columns. Soon, it’s difficult to see the actual data from the copies, and managing everything can become tiresome. Fortunately, spreadsheet pruning is simple, if not time-consuming, but it can be made easier with a few tricks. Here are a few simple ways to remove duplicates in Excel.

Removing Duplicate Cells, Rows, & Columns

If you edit an important or work spreadsheet, make a backup first. It could save time and heartache should something go wrong. The finding and removing duplicates part of this tutorial is safe as they use built-in tools. However, more complex spreadsheets containing formulas or filters already in place might cause you some headaches.

How to Remove Duplicates Quickly and Easily in Excel

First, we need to identify if there are duplicates within a spreadsheet. In a small spreadsheet, they may be easily identifiable. In larger ones, it may be challenging to locate those duplicates without some technological help. Afterwards, you can remove the duplicates.

Open your spreadsheet on the page you need to sort.
Press “Ctrl + A” to select all.
Select “Conditional Formatting.”
Choose “Highlight Cells Rules” followed by “Duplicate Values.”
Set a style to highlight duplicates and click “OK.”
Now, your spreadsheet will format each duplicate cell in your selected color.
If you’re using Microsoft Office 2013 or newer or Microsoft Office 365, press “Ctrl + A” to select all.
Click the “Data” tab and select “Remove Duplicates.”
Select or deselect “My data has headers” depending on whether yours has them or not.
Click “OK” to remove the duplicates.

Removing Excel Duplicates using Advanced Filters

There is also another way to remove duplicates in Excel. Use advanced filters. This comes in handy when you have hundreds of cells full of data rather than a small visible amount.

Open your spreadsheet on the page you need to sort.
Drag the mouse to include all the cells you want to filter.
Click the “Data” tab and select “Advanced.”
Check the “Unique records only” box and click “OK.”

This method removes all duplicates except those it thinks might be column headers—you need to delete them manually since there is no header option. Other than that, it does the same job as removing duplicates.

Do you have any other cool ways to remove duplicate entries? Let us know below if you do!