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.

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

Filter and Remove Duplicates Values in Excel with Remove Duplicates Command

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. To do that, follow these steps below:

1. Select the entire data table (press CTRL+A).
   
   
2. Click on the Data tab.
3. Under the Data Tools group, click on the Remove Duplicates command.
   You will see the Remove Duplicates dialog box will pop up.
4. Tick mark the checkboxes of columns.
5. Check that My data has headers checkbox is selected and hit OK.

After removing duplicate values, you’ll see summary of results in the popped-up dialog box with the count of values.

Find Duplicates in Columns and Delete Rows in Excel with Conditional Formatting

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. 

Use Conditional Formatting to find duplicates in columns and delete rows in Excel, go through these steps:

1. Select the entire data table.
2. Now, click on the Home tab from the ribbon.
   
   
3. Under the Styles group, hit the Conditional Formatting drop-down list.
4. Put the cursor on the Highlight Cells Rules option.
5. Select the Duplicate Values command.
   The Duplicate Values dialog box will pop up.
6. Pick a preferable color from the “Values with” drop-down menu.
   I chose the Light Red Fill with Dark Red Text option.  
7. Hit OK.
   Now, you will see the red-colored boxes. However, not every row has all its cells redly marked.
8. Find the rows with full-length duplicates.
9. Right-click on the red-marked rows with and click Delete command.
   The Delete dialog box will open up.
10. Mark on the Entire Row checkbox and hit OK.

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

Find Duplicates in Columns and Delete Rows in Excel Using VBA Macros

Apply VBA Macros to find duplicates in columns and delete rows in Excel. So, here are the steps below:

1. Select the entire data table.
   
   
2. Hit on the Developer tab from the ribbon.
3. Click on the Visual Basic command, under the Code group.
   The Visual Basic Editor will open.
4. Double-click on the worksheet name you are working on currently from the left menu.
   I am working on Sheet4 (Method 2) now.
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

   

6. From the top-left corner, click on the File drop-down list.  
7. Hit the Close and Return to Microsoft Excel option.
   
   
8. Again select the Developer tab and select the Macros command under the Code group.
   Or, press ALT+F8 to open Macro dialog box.
   The Macro dialog box will come along on the screen.
9. Hit Run button.

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]

Find Duplicates in Columns and Delete Rows with COUNTITFS Function & FILTER Command

Use the COUNTIFS function to detect similar values in Excel.  

Syntax

COUNTIFS(criteria_range1, criteria1,..)

Formula

=COUNTIFS(E2:E16,E2)

Formula Explanation

The formula counts the number of occurrences of the value in cell E2 within the range E2:E16.

Formula

=A2&B2&C2&D2

Formula Explanation

This formula will link up the top cells of each column together.

To find duplicates in columns and delete rows with COUNTIFS function, follow these steps below:

1. Create two new columns beside the data table (Column E & F). 
2. Type this formula inside cell E2: =A2&B2&C2&D2
   
   
3. Press ENTER to join the cells together.
4. Double-click on the Fill Handle.
   This will copy the formula toward the entire column.
5. Copy this formula and paste it into cell F2: =COUNTIFS(E2:E16,E2)
   
   
6. Press ENTER.
7. Double-click on the Fill Handle.
   The total count of repetitions will show up in column F.I will filter the duplicates now.
8. Select the Data tab from the ribbon.
9. Under the Sort & Filter group, click on the Filter command.
   
10. Right-click on the filter dropdown icon.
    
11. Select Number Filters > Equals command.
    
12. In Custom AutoFilter dialog box, select Equals and type ‘1’ in the empty box beside Equals under the Repeats dropdown. Then, hit OK.
    
13. Now, select column E and column F.
14. Click on the Home tab > Editing group > Clear drop-down menu > Clear All command.   

I have removed column E and column F from my dataset to keep it neat and clean. 

• 4+ Methods to Filter Duplicate Values in Excel
• How to Remove Duplicates in Excel [13 + Different Methods]
• How to Find Duplicates in Two Columns in Excel (7 Methods)
• Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]

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. 

Formula

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

To remove duplicates but keep rest of the row values in Excel, go through the steps below:

1. Select the entire data range.
2. Click on the Data tab > Sort & Filter group > A-Z command.
   Now, I rearranged all the columns to perform our next formula.
3. 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. 
4. Type this formula in cell E2: =C2=C3
   
   
5. Press ENTER.
   
   
6. Click on the Fill Handle icon.
   Fill Handle will copy-paste the formula through the entire column.
   
   
7. Click on the Data tab > Sort & Filter group > Filter command.
   A small filter drop-down icon will come along upon the Matches (column E) header.
8. Click on the filter drop-down icon and unmark the TRUE checkbox from there.
   
   
9. Select the cells from C2 to C16.
   
   
10. Go to the Home tab > Editing group > Clear drop-down > Clear Contents command.
    Make sure to keep the column header (Holiday Name).
11. Select the cells of column Matches and go Home tab> Editing group> Clear drop-down> Clear All command.

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

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. 

Frequently Asked Questions

What is the formula for finding duplicates in Excel?

The formula for finding duplicates in Excel is =COUNTIF(A:A, A1)>1
Here A:A is the column containing data, and A1 is the first cell in that column; this formula checks if the value in a cell appears more than once in the specified column.

Why should we remove duplicates?

Removing duplicates in data is essential to enhance accuracy and streamline analysis by eliminating redundant information, ensuring data integrity, and preventing errors in statistical calculations or decision-making processes.

How do I delete non duplicates in Excel?

To delete non-duplicates in Excel, use the Remove Duplicates feature:

1. Select the data range.
2. Go to the Data tab.
3. Click Remove Duplicates.
4. Choose the relevant columns.

By following above steps, Excel will retain only unique values, removing non-duplicates.