When you are pulling together data from various sources onto one Excel sheet, you tend to create duplicate rows of data. If you only have a small set of data to work with, you can delete them manually, but when the data is voluminous, this won’t go be a pleasant task. Imagine the hours of lost productivity, just trying to fix your datasheet. Thankfully, Excel offers you a number of ways in which you can remove duplicate rows and in this tutorial. I’ll be discussing 4 of them. You can make use of this data sheet in case you want to test it out.
Find and Remove Duplicate Rows Using Remove Duplicates Function
Excel has a straightforward command that lets you find and remove duplicate cells in a worksheet at the tap of a button. To do this:
1. Open up the worksheet with the duplicated data.
2. Select the data by pressing Ctrl+A or by clicking and dragging across the worksheet. In my example, I want to identify and remove the years that have been duplicated.
3. On the Data tab, in the Data tools group, click Remove Duplicates.
4. Since there is data in the adjacent column, you will receive a warning message to expand the selection or continue with the current selection. Select Continue with the current selection and click Remove Duplicates.
5. In the next screen, you will notice options to choose the columns where you want to detect and clear duplicate values. Since the focus here is on removing the duplicate years, I’ll uncheck Item and click Ok.
6. You will receive a message that n number of duplicate values were found and removed. You will also be notified on the number of unique values left in the column. Click Ok to close the dialog.
Method 2: Remove Duplicates Using Advanced Filter
1. Open up your worksheet with duplicated data and select all the data by pressing Ctrl+A.
2. Now on the Data Tab, in the Sort & Filter group, click Advanced.
3. In the Advanced Filter dialog box, check the Unique records only check box and click OK.
4. You won’t get a message confirmation, but your duplicates should be removed now. In this method, Excel assumes that your first row is a header, so you will have to check and remove the duplication in the first (header) row.
Method 3: Using Find and Replace Functionality
There’s yet another rather easy method to remove duplicates and that’s using the Find and Replace functionality. This one’s ideal if you are having a smaller worksheet to work on.
1. Open up your worksheet containing the duplicated data.
2. Select the cell containing the data that is duplicated and copy its contents. (Ctrl+C)
3. Press Ctrl+H on your keyboard and paste the content in the Find what text field.
4. Now press the Options button.
5. Check the Match entire cell contents checkbox to ensure that you are not accidentally removing the content that is present elsewhere n the worksheet.
6. In the Replace text box, press the Spacebar to replace the cell contents with a blank space. That will indicate to you that a duplicate from that cell has been removed. Press Replace All or Replace until you’ve removed the values in the order in which you want.
7. After replacing all the duplicate values, close the Find and Replace dialog box and in your worksheet filter out the blank cells and remove them by right-clicking and pressing Delete.
Method 4: Using Conditional Formatting
This is another quick way to identify duplicate rows in Excel and is handy when you are working with large data.
1. Open up your worksheet and select the data.
2. On the Home tab, in the Styles group, click Conditional Formatting, click Highlight Cells Rules and then select Duplicate Values.
4. In the Duplicate Values dialog, select a format option to highlight the duplicate data and click Ok.
You can instantly see the data that is duplicated and can remove them as you see fit.
In this tutorial, we showed you four different ways to remove duplicate rows in Excel so you can pick one that works best for you.
If you enjoyed this tutorial then you will also find the tutorial focused on removing spaces in excel cells.
Leave a Reply