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.
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.
Usage 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.
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.
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.
🔗 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.
Usage 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.
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.
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.
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.
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.
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
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.
The Macro dialog box will come along on the screen.
Step_10: Hit the Run button.
Final Result
Now, the duplicated values are gone from the list.
🔗 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 Columns and Delete Rows 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.
Syntax
COUNTIFS(criteria_range1, criteria1,..)
Usage 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)
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.
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.
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.
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.
Usage 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.
Step_11: Click on the filter drop-down icon.
Step_12: Unmark the TRUE checkbox from there.
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.
