Excel Find Duplicates in Column and Delete Row (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

In a short dataset, we can easily remove duplicates manually but if the dataset becomes large then we need some other quick methods to do it. So today I’ll show how to find duplicates in column and delete row in Excel.


Excel Find Duplicates in Column and Delete Row: 4 Quick Ways

Method 1: Use Data Command to Duplicates and Delete Row

Let’s get introduced to our worksheet first. I have used 3 columns and 8 rows to represent the price and quantity of some fruits. Here some items have their duplicates. Now we’ll find the duplicates and remove them.

Excel Find Duplicates in Column and Delete Row

Step 1:

➤ Select the data range.

➤ Then click Data > Data Tools > Remove Duplicates.

A dialog box will open up.

Method 1: Use Data Command to Remove Duplicates and Delete Row.

Step 2: 

➤ Mark the columns and press OK

Use Data Command to Duplicates and Delete Row

The duplicate rows will be removed and a notification box will pop up the message.

Read More: How to Find Duplicates without Deleting in Excel


Method 2: Use Conditional Formatting to Find Duplicates and Delete Row

In this method, we’ll use Conditional Formatting to find duplicates in a column and then delete row(s) in Excel.

Step 1: 

➤ Select the data range.

➤ Then click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

A dialog box will appear.

Method 2: Use Conditional Formatting to Find Duplicates

Step 2:

➤ Select the color choice from the “Values with” drop-down bar.

➤ Press OK

Then the duplicate rows will be shown with your selected color.

Use Conditional Formatting to Find Duplicates and Delete Row

Step 3:

➤ Select the rows now.

➤ Then right-click your mouse and press Delete from the Context Menu.


Method 3: Use Excel VBA to Find Matches in Column and Delete Row

We can use VBA to do the operation. Let’s see how to run it.

Step 1: 

➤ Click Devoloper > Visual Basic

A VBA window will open up.

Method 3: Use Excel VBA to Find Matches in Column and Delete Row

Step 2: 

➤ Then type the code given below:

Option Explicit
Sub Delete_duplicate_rows()
Dim RngX As Range
Set RngX = Selection
RngX.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

Use Excel VBA to Find Matches in Column and Delete Row

Step 3:

➤ Then click File > Close and Return to Microsoft Excel.

Use Excel VBA to Find Matches in Column and Delete Row

Step 4: 

➤ Now click Developer > Macros

A Macro dialog box will appear.

Step 5: 

➤ Now just press Run

See the duplicate rows are removed now.

Read More: Find and Highlight Duplicates in Excel


Method 4: Use Filter with COUNTIF Function to Find Duplicates in Column and Delete Row

Now in this last method, I’ll show how to use Filter and the COUNTIF Function to remove duplicates in a column and delete rows. Here I have added a new column to count the duplicate numbers.

Step 1:

➤ Select Cell E5

➤ Type the formula given below:

=COUNTIF($B$5:$B5,B5)

➤ Then press the Enter button

Method 4: Use Filter with COUNTIF Function to Find Duplicates in Column and Delete Row

Step 2:

➤ Then apply Fill Handle to copy the formula for the other cells of that column.

Use Filter with COUNTIF Function to Find Duplicates in Column and Delete Row

Step 3:

➤ Now click Data > Sort & Filter > Filter

The Filter option is now activated in the Header.

Step 4:

➤ Click the Filter icon in the Count header.

➤ Then click Number Filters > Greater Than

A dialog box will open up.

Use Filter with COUNTIF Function to Find Duplicates in Column and Delete Row

Step 5:

➤ Type 1 in the bar that is shown in the image below.

➤ Press OK

Now the dataset is filtered with only duplicate values.

Step 6:

➤ Select the rows.

➤ Now right-click your mouse and select Delete Row from the Context Menu.

Use Filter with COUNTIF Function to Find Duplicates in Column and Delete Row

Step 7:

➤ Now deactivate the Filter menu by pressing again it and you will get back your unique rows.

Here we can see the duplicates are removed.

Use Filter with COUNTIF Function to Find Duplicates in Column and Delete Row

Read More: How to Find Duplicates in Excel Workbook


Download Practice Book

Download the Excel workbook that we’ve used to prepare this article.


Conclusion

I hope all of the methods described above will be well enough to remove duplicates in a column and delete rows in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles


<< Go Back to Find Duplicates in Excel Column | Find Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo