Nazmus Sakib
We can manually identify and delete the duplicate rows in Excel. However, if we deal with a large dataset, deleting duplicates manually is cumbersome. In this article, we will show how to find duplicates in column and delete row in Excel step by step.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Find Duplicates in Column and Delete Row in Excel with These 5 Easy Approaches
Today we will learn how to find duplicates in column and delete row in Excel using the following sample dataset.
1. Using Remove Duplicates from Data Tools
In our first approach, we will use the Remove Duplicates from Excel Ribbon to find duplicates and delete those rows from our dataset.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cells B5 to E13. Navigate to the Data tab and click Remove Duplicates from the Data Tools group.
- A Remove Duplicates dialog box will appear on the screen. Click OK.
- A new dialog box will state how many duplicate values were found and removed and how many unique values remain. Click OK.
- You’re all set!
2. Utilizing Advanced Filter
Now, in our second approach, we will utilize the Advanced Filter from the Excel Ribbon to find and remove duplicate rows. Let’s see.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cells B5 to E13. Then, just like the previous method, navigate to the Data tab and click Advanced under the Sort & Filter group.
- The Advanced Filter dialog box will open. Check the Copy to another location button.
- Next, in the Copy to box, select cell G5.
- Afterward, tick Unique records only. Click OK.
- Done! All the unique values are in cells G5:J11.
📕 Read More: Find Duplicate Rows Based on Multiple Columns in Excel
3. Applying IF and COUNTIFS Functions
This time we will use the IF and the COUNTIFS functions to filter and remove duplicates.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell F6 and Enter the formula below:
=IF(COUNTIFS($B$6:$B6,$B6,$C$6:$C6,$C6,$D$6:$D6,$D6,$E$6:$E6,$E6)=1,”Yes”,”No”)
- Later, use the Fill Handle icon from the bottom right corner of cell F6 and double-click.
- The Unique column shows if the rows are unique or not.
- Select cells B5:E13. After that, go to the Data tab and click Filter from the Sort & Filter group.
- All the headers now have a drop-down icon. Click the drop-down menu from the Unique Uncheck Yes and click OK.
- Consequently, only the duplicate rows will remain.
- Select the rows and right-click. From the menus, click Delete Row.
- Next, click Filter again from the Excel Ribbon and you will find the unique values remaining.
🔨 Formula Breakdown
IF(COUNTIFS($B$6:$B6,$B6,$C$6:$C6,$C6,$D$6:$D6,$D6,$E$6:$E6,$E6)=1,”Yes”,”No”)
👉 In cell F6, the COUNTIFS function will search for the value stored in cells B6, C6, D6, and E6 in their respective column. The IF function will apply the condition that if the value returned by the COUNTIFS function is not 1, it will return No else it will return Yes.
👉 In cell F13, the COUNTIFS function will search for the value stored in cell B13 from cells B6 to B13 and will do that for every other column. If the value returned by the COUNTIFS function is 1 the IF function will return Yes.
📕 Read More: 9 Unique Cases to Compare Rows for Duplicates in Excel
4. Executing VBA Code
Now, we will execute a simple VBA code to find duplicates in column and delete row.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cells B5:E13. Then, right-click on the sheet name. Select View Code.
- A code window will appear and type in the following code and press F5 to Run the code.
Sub Find_Duplicates_Delete_Rows()
Dim rg As Range: Set rg = Selection
rg.RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes
End Sub
- Go back to the sheet and only the unique values are present in your dataset.
5. Implementing Conditional Formatting
In this approach, we will implement Conditional Formatting to find duplicates in columns and delete those rows.
⬇️⬇️ STEPS ⬇️⬇️
- First of all, select cells B5:E13. Go to the Home tab and click Conditional Formatting.
- From the drop-down menu, under the Highlight Cells Rules, select Duplicate Values.
- A dialog box will appear and click OK.
- All the duplicate values are now in light red fill with dark red text.
- Now, select the duplicate rows and right-click to select Delete from the menus.
- Done! You can clear the formatting from the Conditional Formatting drop-down menu. Select Clear Rules and then click Clear Rules from Entire Sheet.
📕 Read More: 7 Ways to Find Similar Text in Two Columns in Excel
How to Remove Duplicates But Keep First Instance in Excel
Here, we will learn how to remove duplicates but we will keep the first instance using the Power Query.
⬇️⬇️ STEPS ⬇️⬇️
- Select cells B5 to E13 and navigate to the Insert tab and click Table.
- On the Create Table dialog box, check My table has headers and click OK.
- We have created a table to use the Power Query Editor.
- Next, from the Data tab click From Table/Range.
- The Power Query Editor will open momentarily.
- From the Home tab, click Remove Duplicates under the Remove Rows drop-down menu.
- Now, click Close & Load to load the table to a new worksheet.
- We have kept the first instance and removed the duplicates.
📕 Read More: 5 Ways to Create a Top 10 List with Duplicates in Excel
📝 Takeaways from This Article
📌 Firstly, we learned how to use Remove Duplicates from the Excel Ribbon to find duplicates in column and delete those rows.
📌 Secondly, we showed how to utilize the Advanced Filter to find and remove duplicate rows.
📌 Thirdly, we demonstrated how to apply the IF and COUNTIFS functions to filter duplicates.
📌 Later, we ran a VBA code to find and delete duplicate rows.
📌 Next, we implemented Conditional Formatting to find duplicates in column and delete row.
📌 Finally, we used the Power Query to find duplicates and keep the first instance.
Conclusion
That concludes the discussion for today. These are some convenient methods to find duplicates in column and delete row. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
