5 Ways to Find Duplicates in Column and Delete Row in Excel

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.


Approach

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.

find duplicates in column using remove duplicates from excel ribbon

  • 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!

find duplicates in column applying remove duplicates from excel ribbon


Approach

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.

how to find duplicates in column implementing advanced filter from excel ribbon

  • 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.

find duplicates in column and delete row in excel applying advanced filter from excel ribbon

📕 Read More: Find Duplicate Rows Based on Multiple Columns in Excel


Approach

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”)

Filter duplicates in column and delete row in excel implementing if and countifs functions

  • 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.

filter duplicates in column and delete row employing if and countifs functions

  • 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.

filter duplicates in column and delete row administering if and countifs functions

  • 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.

filter duplicates in column and delete row using if and countifs functions

🔨 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


Approach

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.

how to find duplicates in column executing vba 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.

how to find duplicates in column and delete row embedding vba code


Approach

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.

find duplicates in column utilizing 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.

how to find duplicates in column and delete row in excel employing conditional formatting

📕 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.

How to Remove Duplicates But Keep First Instance in Excel

  • The Power Query Editor will open momentarily.

  • From the Home tab, click Remove Duplicates under the Remove Rows drop-down menu.

How to Remove Duplicates but Keep First Instance in Excel applying power query

  • Now, click Close & Load to load the table to a new worksheet.

How to Remove Duplicates but Keep First Instance in Excel using power query

  • 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.

(Visited 40 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo