How to Remove Both Duplicates in Excel – 5 Easy Methods

After merging data from two worksheets, the dataset contains identical entries or both duplicates:

Method 1- Using a Helper Column to Remove Both Duplicates in Excel

Step 1

• Â Insert a Helper Column (Column E).

Step 2

• Enter the following formula in any cell (here, E5).
`=COUNTIF(\$B\$5:\$B\$17,B5)=1`

The formula counts cells in B5:B17 that match the criteria (B5)Â and returns TRUE (if the cell meets the criteria). Otherwise, FALSE.

Â Step 3

• Press ENTER and Drag the Fill Handle to display TRUE or FALSE in the rest of the cells.

Â Step 4

• Go to the Home Tab > Select Sort & Filter (in Editing) > Choose Filter.

Step 5

Â The Filter icon is displayed in all column headers.

• Click the Filter Icon in the Helper Column header.
• UncheckÂ  FALSE.
• Click OK.

This is the output.

Method 2 – Using a Formula to Remove Both Duplicates in Excel

Step 1

• Â Add a helper column and enter the below formula in any blank cell (here, E5).
`=IF(COUNTIF(\$B\$5:\$B\$17,B5)=1,1,0)`

The formula performs a logical test (i.e., COUNTIF(\$B\$5:\$B\$17,B5)=1). The COUNTIF function returns TRUE or FALSE applying the criteria (iB5) in B5:B17). The IF function returns 1 if a cell meets the criteria. Otherwise, 0.

Â Step 2

• Â Press ENTER and drag the Fill Handle.

Step 3

• Apply the Filter feature: follow the steps described in Step 4 of Method 1.
• Click the Filter icon in the Formula Result column header.
• Uncheck 0.
• Click OK.

This is the output.

Â In the formula ( `=IF(COUNTIF(\$B\$5:\$B\$17,B5)=1,1,0``)`) 1 are no identical entries and 0 identical entries.

Read More: How to Remove Duplicate Names in Excel

Method 3 – Using the Conditional Formatting (Highlighting Duplicates)

Step 1

• Select the range (here, B5:B17) to apply Conditional Formatting.
• Go to the Home tab > Select Conditional Formatting > Select Highlight Cells Rules > Choose Duplicate Values.

Step 2

• The Duplicate Values window opens.
• Choose Duplicate (in Formats cells that contain).
• Select a Cell Color (here, Light Red Fill with Dark Red Text).
• Click OK.

Step 3

• Go to the Home tab > Select Sort & Filter (in Editing) > Choose Custom Sort (in Sort).

Step 4

• In the Sort window, select Product (in Column) as Sort by.
• Select a Cell Color (in Sort On).
• Select a Color in Order.
• Choose On Bottom to display the duplicates at the bottom.
• Click OK.

This is the output.

To remove the color formatted duplicates:

Â a) Delete feature

• Select the color formatted cells.
• Go to the Home tab > Select Delete (in Cells) > Click Delete Sheet Rows.

This is the output.

b) Keyboard Shortcuts (CTRL+-)

• Select the color formatted rows and press CTRL+- .
• In the Delete window, select Entire Row.
• Click OK.

This is the output.

• Select the cell range and right-click it.
• In the Context Menu, select Delete.

• In the Delete window, select Entire row.
• Click OK.

This is the output.

Method 4 – Using the Conditional Formatting (A New Rule to Apply Color Format)

Step 1

Go to the Home tab > Select Conditional Formatting > Choose New Rule.

Step 2

• Â In the New Formatting Rule window: select Use a formula to determine which cells to format in Select a Rule Type.
• Enter the following formula in Format values where this formula is true.
`Â =IF(COUNTIF(\$C\$5:\$C\$17,C5)=1,0,1)`

This formula declares the same arguments the one used in Step 2 of Method 1, but it returns 0 if entries meet the argument. Otherwise,Â 1.

• Click Format.

Step 3

• Â In the Format Cells window, select the Font color (here, Dark Red).
• Click OK.

Step 4

• Â InÂ the New Formatting Rule window, click OK.

Step 4 applies the formula to the selection and color format to the cells.

Step 5

• Select the entire dataset.
• Go to the Home tab > Select Sort & Filter (in Editing) > Choose Custom Sort. The Sort window is displayed.

Step 6

• Enter Column C (in Column >Â Sort by).
• Choose a Font Color (in Sort On).
• Choose the Color in Order.
• Choose On Bottom to make the duplicates appear at the bottom.
• Click OK.

This is the output.

Step 7

Follow the steps described in Method 3 to delete cells.

This is the output.

Method 5 – Using a VBA Macro Code

This is the sample dataset.

Step 1

• Press ALT+F11 to open the Microsoft Visual Basic window.
• Select Insert > Choose Module.

Step 2

• Enter the following code in the Module.
``````Sub Remove_Both_Duplicates()
Dim Cell As Range
With ActiveSheet.UsedRange
For Each Cell In .Cells
If Cell.Value <> "" And Application.CountIf(.Cells, Cell.Value) > 1 Then
.Replace Cell.Value, "", xlWhole, , , , False, False
End If
Next
On Error GoTo NoBlanks
End With
NoBlanks:
End Sub``````

The COUNTIF function counts cells that haveÂ  more than 1 occurrences. The REPLACE function replaces the cells with blank strings.

Â Step 3

• Press F5 to run the macro.
• Go back to the worksheet.

This is the output.

Related Articles

<< Go Back to Remove Duplicates in Excel | Duplicates in ExcelÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

1. Thank you very much for the explanation. im sorry but i have question. I would like to remove both duplicate but in 600000 cells. When i used the VBA module, the job freeze because its looping on so many data.

Could you give some advice or other module how to do it?
im sorry but thankyou for your help

• Greetings Indra,

The probable causes behind the freezing of Visual window or Excel are:

1. VBA Macros use device’s single core to run or execute macros.
2. Large data normally take several minutes to complete any macro-driven outcomes. Therefore, sudden freezing of visual window or Excel is common among users.

You can use the already used macro (in the article), if it works fine after couple of minutes of freezing or unresponsiveness. Otherwise, email us your Data to get custom macro that may solve the issue (As we don’t have such huge data to test our code with). Also, you can go through:

1. Divide your data into worksheets then execute the macro individually. Then merge the worksheets into one.
2. Use other means such as formulas to accomplish the desired outcome.

Hope, it helps you. Comment if further issues arise.

Advanced Excel Exercises with Solutions PDF