When you’re working with massive piles of data or dealing with an aggregation of spreadsheets, identifying and removing duplicate rows can turn into back-breaking manual labor. However, with the right knowledge and tools, you can automate this process in excel.
In this tutorial, I’ll teach you different methods, gimmicks, and shortcuts to remove duplicate rows in excel. If you don’t have data with duplicate rows and simply want to improve your Excel skills, you can practice on our duplicate resource sample. We’ll use it as a template in this tutorial.
How to Remove Duplicates in Excel:Â Step-by-Step
Method 1: In-Built Remove Duplicates Button
The newer versions of the Microsoft Excel and Microsoft office suite provide a built-in feature to remove duplicate rows in excel. Here’s how it works.
1. Open your Excel Spreadsheet.
2. Select all the rows you want the duplicates searched and removed from. In this case, we’re going to select the entire data. 
3. At the top of your window, locate and click the Data tab.
4. In the far-left corner, tap the Remove Duplicates option.
5. When the Remove Duplicates dialog box appears, untick the “My data has headers” option”. We’re working with data that doesn’t have headers. If you don’t deselect this option, Excel will not search for duplicates in your first row. It will automatically assume that the first row is the header.
6. Once you’ve unticked this option, Excel will highlight the entire data, including the first row. The column portion will transform from duplicates to Column A, B, and C.
7. Hit Okay.
8. Your selected data is now duplicate free.
Method 2: Advanced Filtering
If you’re using older versions of Excel, and don’t have the Remove duplicates option built-in, you can opt for the Advanced filtering feature.
1. Open the Excel Spreadsheet.
2. Select entire data or data from which you want duplicates removed.
3. Locate and tap the Data Tab.
4. Head to the section that has a funnel sign and reads “Filter”. Select Advanced underneath it.
5. Check the Unique Records only Option and Press OK.
Your excel will remove all duplicates, except the ones in the first row. This is because, in this method, Excel makes the assumption that the first row is your header. Unfortunately, there’s no way around this and to remove duplicates in the first row, you will have to remove them yourself.
Note: If you’re used to working on Google spreadsheets rather than Microsoft Excel, here’s how you can manage, add and delete rows and columns in Google Spreadsheets.
Frequently Asked Questions
How do I remove duplicate records in Excel 2007?
If you’re one of those guys who’s not all that into updated stuff. Here’s a tutorial tailored for removing duplicates from the Microsoft Excel 2007 version.
Still confused as to how to remove duplicates in Excel? Leave us a comment below or refer to these related tech-recipes; they will surely provide some perspective.
How to find duplicates records in SQL server.
You can alternatively check the visual breakdown of how to remove duplicates in excel from this youtube video. It’s going to make the entire process easier to follow.
