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 real data from the copies and managing everything can become tiresome. Fortunately, spreadsheet pruning is simple if not time consuming but is 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 are editing an important or work spreadsheet, make a backup first. It could save time and heartache should something go wrong. Both the finding and removing parts of this tutorial are fairly safe for normal use as they use built-in tools. However, more complex spreadsheets containing formulas or filters already in place might cause you some headaches.

Quickly and easily remove duplicates 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 spreadsheets it may be difficult to identify without a little help. Here’s how to find them.

1. Open your spreadsheet on the page you need to sort.
   
2. Press Ctrl + A to select all.
   
3. Click Conditional Formatting.
   
4. Select 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 the color you selected. It is a fast, simple way to see just how many duplicates you have within a sheet.

Once you know how many dupes you have, you can remove them in two simple ways. If you’re using Microsoft Office 2013/6 or Office 365, you have something of an advantage. Microsoft kindly added a remove duplicate function into Excel for just this occasion.

1. 500
2. Open your spreadsheet on the page you need to sort.
   
3. Press Ctrl + A to select all.
   
4. Click the Data tab and select Remove Duplicates.
   
5. Select or deselect ‘My data has headers’ depending on whether yours has them or not.
   
6. Click OK to remove the duplicates.
   

There is also another way to remove duplicates in Excel using advanced filters.

1. Open your spreadsheet on the page you need to sort.
   
2. Drag the mouse to include all the cells you want to filter.
   
3. Click the Data tab and select Advanced.
   
4. Check the ‘Unique records only’ checkbox and click OK.
   

This method removes all duplicates except those it thinks might be column headers. These you will need to remove manually. Other than that, it does the same job as remove duplicates.

There are other ways of easily removing duplicates in Excel using formulas but given how simple these two operations are, there really is no point using them. Do you have any other cool ways to remove duplicate entries? Let us know below if you do!