4+ Ways to Find Duplicates in a Column and Delete Rows in Excel

Duplicate data are disturbing. They somehow sneak in several times in your worksheet when you have to merge two or more sheets. The problem gets bigger when the data is lengthy. To tackle this problem, let’s show you 4 ways to find duplicate values in a column and then delete rows in Excel.

Introduction to the Dataset

The dataset I have used throughout the entire article contains 4 columns about the details of different holidays in the USA.

Introduction: Find Duplicates in Column and Delete Row in Excel

Easiest Way to Filter and Remove Duplicates Values in Excel

I am going to use the Remove Duplicates command and I think it is the easiest method to find duplicate values in columns and then delete the rows.

Now Follow The Guide ↓ 

Step_1: Select the entire data table (press CTRL+A).

Step_2: Click on the Data tab.

Step_3: Under the Data Tools group, click on the Remove Duplicates command.

Remove Duplicates command: Find Duplicates in Column and Delete Row in Excel

You will see the Remove Duplicates dialog box will pop up.

Step_4: Tick mark the checkboxes of columns.

Step_5: Check that My data has headers checkbox is selected.

Step_6: Hit OK.

Remove Duplicates dialog box: Find Duplicates in Column and Delete Row in Excel

Final Result ↓ 

The rows with duplicate values are gone now. And a dialog box with the summary of results will show you the count of values.

Final Result: Find Duplicates in a Column and Delete Rows in Excel

🔗 How to Find Duplicates in Two Columns in Excel (7 Methods)

Alternative Method #1: Use CONDITIONAL FORMATTING to Find Duplicates in Columns and Delete Rows in Excel

You can take the help of Conditional Formatting to detect the same values and then delete the rows. Conditional Formatting will make the identical valued rows highlighted, and later, remove them. So before removing the similar values, you can take a look at the duplicates and recheck them if you have to keep any of the values. 

Now Follow The Guide ↓ 

Step_1: Select the entire data table. 

Step_2: Now click on the Home tab from the ribbon. 

Step_3: Under the Styles group, hit on the Conditional Formatting drop-down list. 

Step_4: Put the mouse cursor on the Highlight Cells Rules drop-down menu. 

Step_5: Select the Duplicate Values command.  

Conditional Formatting: Find Duplicates in Column and Delete Row in Excel

The Duplicate Values dialog box will pop up on the screen.

Step_6: Pick a preferable color from the “Values with” drop-down menu.

I chose the Light Red Fill with Dark Red Text option.  

Step_7: Hit the OK button.

Duplicate values dialog box: Find Duplicates in Column and Delete Row in Excel

Now all the duplicate values are highlighted with red-colored boxes. But not every row has all its cells redly marked. Find the rows which are full-length duplicates. In my worksheet, I have 8 repeated rows.

Output: Usage of CONDITIONAL FORMATTING to Find Duplicates in Columns and Delete Rows in Excel

Step_8: Right-click on the red-marked rows with your mouse cursor and select the Delete command.

The Delete dialog box will open up.

Step_9: Mark on the Entire Row checkbox.

Delete dialog box: Find Duplicates in Column and Delete Row in Excel

Step_10: Hit OK.  

Final Result ↓ 

One by one the pairs of matched values will remove by repeating this process. The rest of the detached duplicated values will remain in their rows. You can take a look at them and arrange them manually further.

🔗 How to Compare Rows in Excel for Duplicates (7 Ways)

🔗 5 Ways to Find Matching Values in Two Worksheets in Excel

Alternative Method #2: Use VBA MACROS to Find Duplicates in Columns and Delete Rows in Excel

Step_1: Select the entire data table. 

Step_2: Hit on the Developer tab from the ribbon.

Step_3: Click on the Visual Basic command, under the Code group.  

Visual basic command: Use VBA MACROS to Find Duplicates in Columns and Delete Rows in Excel

The Visual Basic Editor will open.

Step_4: Double click on the worksheet name you are working on currently from the left menu.

I am working on Sheet4 (Method 2) now.

Step_5: Insert the Macro in the Visual Basic Editor.

Sub ClearDupRows() 
Dim pRng1 As Range 
Set pRng1 = Selection pRng1.RemoveDuplicates Columns:=Array(1), Header:=xlYes 
End Sub

Use VBA MACROS to Find Duplicates in Columns and Delete Rows in Excel

Step_6: From the top-left corner, click on the File drop-down list.  

Step_7: Hit the Close and Return to Microsoft Excel option.  

Step_8: Again select the Developer tab.

Step_9: Select the Macros command under the Code group.

Macros: Use VBA MACROS to Find Duplicates in Columns and Delete Rows in Excel

The Macro dialog box will come along on the screen.

Step_10: Hit the Run button.

Macros dialog box: Use VBA MACROS to Find Duplicates in Columns and Delete Rows in Excel

Final Result ↓ 

Now, the duplicated values are gone from the list. 

Output: Use VBA MACROS to Find Duplicates in Columns and Delete Rows in Excel

🔗 5+ Formulas to Find Duplicates in One Column in Excel

🔗 Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]

Alternative Method #3:  Use COUNTITFS Function and FILTER Command to Find Duplicates in Column and Delete Row in Excel

You can go for using functions to detect similar values from your spreadsheet. I am going to show the use of a technic with the COUNTIFS function. For this method, we need to add two new columns beside our dataset.

Formula Syntax ↓ 

COUNTIFS(criteria_range1, criteria1,..)

Now Follow The Guide ↓ 

Step_1: Create two new columns beside the data table.

I named the columns Concatenates(column E) and Repeats (column F)

Step_2: Type this formula inside cell E2:

=A2&B2&C2&D2
Formula Explanation
This formula will link up the top cells of each column together.

Step_3: Press ENTER to join the cells together. 

Step_4: Take your mouse cursor in the lower bottom corner of cell E2, and the cursor will turn into Fill Handle.

Step_5: Double-click on the Fill Handle.   

The Fill Handle will copy the formula toward the entire column.

Step_6: Copy this formula and paste it into cell F2:

 =COUNTIFS(E2:E16,E2)

Use of COUNTITFS Function to Find Duplicates in Column and Delete Row in Excel

Formula Explanation

  • E2:E16 is the range of cells that is from E2 to E16.
  • E2 is the value of the top cell of column E.

Step_7: Press ENTER.

Step_8: Double click on the Fill Handle.

The total count of repetitions will show up in column F.

Filter the Duplicates Now

Step_9: Select the Data tab from the ribbon.

Step_10: Under the Sort & Filter group, click on the Filter command.  

Use of FILTER Command to Find Duplicates in Column and Delete Row in Excel

A small icon of filter will come along on every column’s headers.

Step_11: Right-click on the filter drop-down icon.

Step_12: Select the Number Filters Equals command.

Using FILTER Command to Find Duplicates in Column and Delete Row in Excel

The Custom AutoFilter dialog box will pop up.

Step_13: Under the Repeats drop-down, select Equals.

Step_14: Type ‘1’ in the empty box beside Equals.  

Custom AutoFilter dialog box: Usage of FILTER Command to Find Duplicates in Column and Delete Row in Excel

Step_15: Hit the OK button.

Step_16: Now select column E and column F.

Step_17: Click on the Home tab.

Step_18: Under the Editing group, hit the Clear drop-down menu. 

Step_19: Select the Clear All command.   

Final Result ↓ 

I have removed column E and column F from my dataset to keep it neat and clean. You can skip from step_16 to step_19 if you prefer the additional columns.  

🔗 4+ Methods to Filter Duplicate Values in Excel

🔗 How to Remove Duplicates in Excel [13 + Different Methods]

How to Remove Duplicates But Keep Rest of the Row Values

Suppose you have a messy dataset. Now you want to remove the identical values from one particular column but keep the rest of the row, rather than deleting the entire row. But before removing the duplicates, first, you need to find out the duplicates. After that, you can proceed with the further steps.

Now Follow The Guide ↓ 

Step_1: Select the entire data range.

Step_2: Click on the Data tab.

Step_3:  Under the Sort & Filter group, hit on the A-Z command.

Now all the columns are alphabetically rearranged. The arrangement will help us to perform our next formula.

Step_4: Add a new column header beside the data table and give it a name.

I created a new column in column E named Matches. 

I want to find the duplicates from the Holiday Name (column C) of my dataset. 

Step_5: Type this formula in cell E2:

=C2=C3
Formula Explanation
As the column is alphabetically rearranged, the repeated texts are set one after another. So now the duplicates will be referred to as TRUE and the unique values will be referred to as FALSE.

Step_6: Press ENTER.

Step_7: Put your mouse cursor on the right-bottom corner of E2.

Step_8: Click on the Fill Handle icon.  

Fill Handle will copy-paste the formula through the entire column.

Step_9: Click on the Data tab.

Step_10: Hit on the Filter command located under Sort & Filter group.

A small filter drop-down icon will come along upon the Matches (column E) header.

Filter command: Remove Duplicates But Keep Rest of the Row Values

Step_11: Click on the filter drop-down icon.

Step_12: Unmark the TRUE checkbox from there.  

Remove Duplicates But Keep Rest of the Row Values Using Filter

Step_13: Select the cells from C2 to C16. 

Step_14: Go to the Home → Editing group → Clear drop-down.

Step_15: Hit the Clear Contents command. 

Make sure to keep the column header (Holiday Name). 

Step_16: Select the cells of column Matches.

Step_17: Now go Home → Editing group → Clear drop-down → Clear All command.

Final Result ↓ 

Now rest of the cells of rows are intact, but the similar cells of column C are gone. 

🔗 Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]

Conclusion

I have shown you 4 ways to find duplicates in columns and then delete the rows. I hope, you’ve found your required solution from this blog. Tell us how you like the solutions in the comment section. 

(Visited 76 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *