Alok Paul
Duplicates in Excel worksheets are a big issue for users. It is very tiresome work to find duplicates and remove them. In this tutorial, we will show some easy methods for how to remove duplicates in Excel sheet.
To illustrate, I’m going to use a dataset as an example. For instance, the following dataset represents the Device of several Brands, Model, and Price of those Devices.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
How to Remove Duplicates in Excel Sheet: 8 Practical Examples
1. Highlight Duplicate Values to Remove Duplicate Rows Using Conditional Formatting
In this example, we will highlight the duplicate values first using conditional formatting and then remove duplicate rows in the Excel sheet. Let’s follow the instructions below to learn!
- Select your entire dataset >> Select Home tab >> Choose Styles group >> Conditional Formatting >> Highlight Cells Rules >> Duplicate Values.
- A Duplicate Values dialog box appears. Choose Duplicate values in the box >> change the highlighting color >> press OK.
- We find duplicate values in our dataset.
- Now, select cell B4 and press Ctrl+Shift+L to enable the filter option >> Press the right-click on the Price filtering option >> Choose the Filter by Color option.
- The identical rows are showing only.
- To remove duplicate rows easily, we will sort our filtering dataset alphabetically. Press right-click on the filter sign >> Choose Sort A to Z.
- As a result, we will be able to sort our dataset alphabetically.
- Then we select all the duplicate rows leaving each type of single row >> press the right button of the mouse >> Select Delete Row.
- A pop-up will show for permission. Press OK.
- You can remove the duplicate rows.
- Now, disable the Filter feature again by pressing Ctrl+Shift+L. We removed all the duplicate rows from our dataset.
Read More: How to Remove Duplicates Based on Criteria in Excel (4 Methods)
2. Use of Remove Duplicates Feature to Remove Duplicate Rows in Excel Sheet
Using the Remove Duplicates command, we can remove duplicate rows easily. Hence, learn the process to do the operation.
- Select data range B5 to E22 >> Press Data tab >> Choose Data Tools group >> Hit Remove Duplicates.
- A dialog box named Remove Duplicates pops up >> Check all the columns heading >> Press OK.
- A pop-up will show mentioning the duplicate value removed and unique values remaining. Press OK.
- Finally, you will be able to remove duplicate rows in Excel.
Read More: Fix: Excel Remove Duplicates Not Working (3 Solutions)
3. Remove Duplicate Rows Using an Excel Advanced Filters Option
The Advanced filter is another cool path to remove duplicate rows or values from Excel sheets. In this example, we will remove duplicate rows using the Advanced filter feature. Let’s follow the instructions below to learn!
- Select data range B4 to E22 >> Press Data tab >> Choose Advanced from the Sort & Filter group.
- A new dialog box will appear. In the List Range, our selected range is shown. We have two options. One is to remove the duplicate and show it in the present list. And the other is copying the unique data to a new location.
- Choose Filter the list, in-place option >> Check the Unique records only >> Press OK.
- The duplicate data is not showing here. It removes the duplicate rows.
- But when we copy data in a new location, the duplicate data is purely removed from the list.
Read More: Excel Formula to Automatically Remove Duplicates (3 Quick Methods)
4. Use of UNIQUE Function to Get Unique Rows in Excel Sheet
In this section, we will use the dynamic UNIQUE function to remove duplicate rows in the Excel sheet. Let’s see how to do it.
- Copy the heading with the formatting of the dataset, and paste it to another location (Cell G5) in the same sheet.
- Hence, write down the following formula in cell G5 and press Enter.
=UNIQUE(B5:E22)- Now you will get the unique rows.
Read More: How to Delete Duplicates in Excel but Keep One (7 Methods)
Similar Readings
- How to Remove Duplicate Rows in Excel (3 Ways)
- Remove duplicate rows based on two columns in Excel [4 ways]
- Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)
- Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)
- How to Remove Both Duplicates in Excel (5 Easy Ways)
5. Remove Duplicates in Excel Sheet Using COUNTIF Function
Now we will use the COUNTIF function and then filter the dataset to remove duplicate rows in the Excel sheet. Let’s follow the instructions below to learn!
- Select cell F5 and write down the following formula. Hence, press Enter, and AutoFill the formula to the rest of the cells in column F.
=COUNTIF($B$5:B5,B5)- In the count column, we counted the object how many times found the Count If the value is 1 means that is unique. But that is 2 or more means that the object is repeated.
- Now, press Ctrl+Shift+L and enable the filter mood.
- After that, click on the down arrow of the Count column >> Check 1 from the filter list >> Press OK.
- Finally, you will get the unique rows which means removing the duplicate rows.
6. Use Pivot Table to Remove Duplicates in Excel Sheet
In this example, we will get to know how to remove duplicates in Excel sheet by inserting the Pivot Table. A pivot table is an interesting tool. We can delete duplicates using the Pivot Table also. Hence, learn the process to do the operation.
- Select data range B4 to E22 >> Go to Insert tab >> Select PivotTable from the Table group >> Click on From Table/Range option.
- A new dialog box of PivotTable input options will appear. We can place the newly formed PivotTable in a new sheet or within this sheet. We select Existing Worksheet and put cell G4 in the location box >> Hit OK.
- We can see PivotTable1 with its properties. Now Put a tick on the Brand, Device, and Mode options and set them on the Rows field.
- Pick the Price in the Value field.
- After that, select Design tab >> Click on Report Layout from the Layout group >> Select Show in Tabular Form.
- Again repeat this process and click on Repeat All Item Labels.
- Now, go to Subtotals in the Layout group >> Choose the Do Not Show Subtotals.
- In PivotTable1 no duplicate values were presented.
Read More: How to Remove Duplicate Rows in Excel Table
7. Remove Duplicate Values Using Power Query from Excel Sheet
Now, I will explain how to remove duplicates in Excel sheet by using Power Query. It is a Data tool by which we can remove duplicates. Power Query has a default option to remove duplicates. Let’s follow the instructions below to learn!
- Select cells B4 to E22 >> Go to the Data tab >> Choose From Table/Range from the Get and Transform Data group.
- A Create Table window pops up. Now, Check My table has headers option >> Press OK.
- Now, a new window will appear with the data in the Power Query mood.
- After that, select the Home tab of the Power Query >> Click on the Remove Rows tool >> Select Remove Duplicates from the list.
- We have the facility to remove duplicates from the whole table in Power Query.
Notes: You can also remove duplicate values in a column using the Remove Columns feature from the Home tab in the Power Query Editor.
8. Run an Excel VBA Code to Remove Duplicate Rows
Now, I will discuss how to remove the duplicate rows in an Excel sheet using a VBA Macro. VBA stands for Visual Basic Application. This is the programming language for Microsoft Excel. Using the VBA code, we will remove the duplicate rows. Let’s see how to do it.
- Select Developer tab >> Choose the Visual Basic feature to open the Visual Basic Application You can do this by pressing the keyboard shortcut ALT + F11.
- After that, Select Insert >> Module.
- Now, write down the following code in that Module, and press the Run button or press the F5 key to run the VBA code.
Sub Remove_Duplicate()
Range("B4:E22").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
- As a result, you will be able to remove duplicate rows in an Excel sheet.
Read More: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)
Things to Remember
- When applying the Advanced Filter method, carefully input the cell references.
- In PivotTable take the decision about where to place the table. Two options: Existing worksheet or new worksheet.
- You can make the formula with other functions too in removing duplicates. We showed two functions here.
Frequently Asked Questions
1. What if I have duplicate values within the same cell in Excel?
If you have duplicate values within the same cell in Excel (e.g., “John, John”), Excel will treat them as a single value and won’t remove them as duplicates. In this case, you’ll need to use a formula or TEXT function to split the values into separate cells before removing duplicates.
2. Can I automatically remove duplicates in Excel as new data is added?
Yes, you can set up a data validation rule in Excel to automatically remove duplicates as new data is added to a specific range of cells. This is done by creating a custom data validation formula that checks for duplicates.
3. Can I remove duplicates in Excel without deleting the duplicate rows?
Yes, you can remove duplicates in Excel without deleting the duplicate rows. In the Remove Duplicates dialog box, simply uncheck the box next to the columns you want to keep duplicates for. This will remove duplicates from the selected columns but keep all the rows in the data range.
Conclusion
In this article, we described how to remove duplicates in Excel sheet. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
