One of the main functions of a Microsoft Excel spreadsheet is that it organizes any data set into manageable rows and columns which can be easily viewed, searched, and arranged. Here we will show you how to delete duplicates in excel the easy way.
Because of the way Excel organizes this data, it should be easy to manipulate, whether this means changing your ordering principles (alphabetical, according to date, according to amount), searching for particular entries, and adding or deleting information.
This tutorial will focus on how to identify and delete duplicate entries which might exist within an Excel spreadsheet that you’ve created.
Reasons For Deleting Duplicates
Anyone familiar with Microsoft Excel knows that there are a number of reasons you might want to search for and delete duplicates. Perhaps you’ve accidentally copied a row when working within an Excel spreadsheet. It’s also possible that you or someone else may have accidentally entered the same information twice on the same spreadsheet.
In any case, where you suspect there may be duplicate information, there is a better option than searching through the entire document using only your eyes and trusting you’ll find your duplicates. Microsoft Excel has made it easy to press a series of buttons which will tell the program to automatically search for, identify, and delete any duplicates you may have added by mistake.
In this tutorial, we’ll cover:
- How to check for duplicates
- How to automatically delete duplicates
- Advanced filtering
- An alternate method for older versions of Excel
Checking for Duplicates
The first step to deleting duplicates is to identify them. Microsoft Excel can do this relatively easily. To identify duplicates, follow the steps listed below:
- Once you’re in the folder which contains your file, double-click on your Excel document file. If you’re looking for a document you’ve used recently, you can also open the document from the “Recent” section of the Open tab.
- Once your file is open, select the range of cells you wish to test for duplicates. You can do this easily by clicking on any cell and then pressing Ctrl-A (Select All).
- Once you’ve selected the range, click on the Home tab. Within the Home tab, select Conditional Formatting. Once you’ve done this, highlight Cells Rules, and then within that, select Duplicate Values.
- Once you’ve selected Duplicate Values, a dialog box should open in the middle of your screen. Within this dialog box, click OK. This box will also inform you as to what color the program will use to identify your duplicate values (eg. Light red fill with dark red text).
- The duplicate values within your list should now be identified in the color stated.
How to Delete Duplicates in Excel
- Open your Excel document file by double-clicking on the file name. Alternately, if you’ve used the document recently, you can open an existing document from the “Recent” section of the Open tab.
- Once your file is open, select the range that you want to delete duplicates from. You can do this by clicking the entry that is in the top left corner of your chosen range. Once you’ve clicked on this, hold down the up arrow and Shift button. While you’re holding these buttons, click on the entry that is in the bottom right corner of your chosen range.
- Your chosen range should now be highlighted.
- Click the Data tab, which is a tab on the left side of the green toolbar at the top of the Excel window.
- Within the Data tab, choose Data Tools, and then Remove Duplicates. Once you’ve chosen Remove Duplicates, a dialog box will appear.
- Within this dialog box, leave all check-boxes checked and click OK. If you do not want to remove duplicates from all of your columns, deselect the columns you’d like to the program to leave alone before clicking OK.
- All of your duplicates should now be deleted from your table.
Note: The Remove Duplicates function will remove every instance of the information starting with the 2nd. Excel will automatically remove all identical rows (blue) except for the first identical row found (in yellow).
Another Option: Advanced Filtering
Another option for filtering out duplicates in Microsoft Excel is using the Advanced Filter option.
- Once again, you’ll begin this process by opening your Excel file.
- 2. Once the file has been opened, you can select all of the cells in the table by pressing Ctrl + A (Select All).
- Once your entire table has been selected, click the Data tab.
- From the Data tab, choose Sort and Filter, then click on the Advanced button.
- Once you’ve entered the Advanced Filter dialog box, check the box that says “Unique Records Only.”
- Once you’ve clicked “OK,” all duplicates except for the original should have been removed.
Deleting Duplicates in Microsoft Excel 2003 or earlier
If you’re using a version of Microsoft Excel from 2003 or earlier, the method for deleting duplicates will be a little different than those we’ve mentioned above.
- Click on cell A1, selecting it.
- Choose Data, Filter, and then AutoFilter.
- Click the Filter arrow in cell C1 and then choose Custom.
- Where it says Equals, change this to Greater Than. Enter 1 and then click OK.
- Once the duplicate values have been identified, you can delete them individually.
- Once a duplicate value has been deleted, its partner value will lose its highlight.
Microsoft Excel is an incredibly useful tool for anyone who has a set of data which needs to be organized. Once you’ve input your data into your Excel spreadsheet, you’ll want to check it for errors. One of the most common errors will be duplicate values that have been entered.
If you’ve followed our instructions, duplicate errors should be easy to identify and delete, regardless of which version of Microsoft Excel you’re using.