Md. Sourov Hossain Mithun
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.
Download Practice Book
Download the Excel workbook that we’ve used to prepare this article.
4 Quick Methods to Find Duplicates in Column and Delete Row
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 and remove them.
Step 1:
➤ Select the data range.
➤ Then click Data > Data Tools > Remove Duplicates.
A dialog box will open up.
Step 2:
➤ Mark the columns and press OK
The duplicate rows will be removed and a notification box will pop up the message.
Read More: How to Find Duplicates in a Column Using Excel VBA (5 Ways)
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.
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.
Step 3:
➤ Select the rows now.
➤ Then right-click your mouse and press Delete from the Context Menu.
Read More: Excel Find Duplicate Rows Based on Multiple Columns
Similar Readings
- How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods)
- How to Compare Two Excel Sheets Duplicates (4 Quick Ways)
- Excel VBA to Find Duplicate Values in Range (7 Examples)
- How to Find Duplicates in Two Different Excel Workbooks (5 Methods)
- How to Vlookup Duplicate Matches in Excel (5 Easy Ways)
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.
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
Step 3:
➤ Then click File > Close and Return to Microsoft Excel.
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: How to Use VBA Code to Find Duplicate Rows in Excel (3 Methods)
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
Step 2:
➤ Then apply Fill Handle to copy the formula for the other cells of that column.
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.
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.
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.
Read More: Finding out the number of duplicate rows using COUNTIF formula
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
- How to Highlight Duplicate Rows in Excel (3 Ways)
- How to Find Matching Values in Two Worksheets in Excel (4 Methods)
- Find Matches or Duplicate Values in Excel (8 Ways)
- How to Find, Highlight & Remove Duplicates in Excel
- How to Compare Rows in Excel for Duplicates
- Excel Find Similar Text in Two Columns (3 Ways)
