How to Remove Both Duplicates in Excel – 5 Easy Methods

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

Dataset-How to Remove Both Duplicates in Excel


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

helper column-How to Remove Both Duplicates in Excel

Step 1

  •  Insert a Helper Column (Column E).

Helper column insertion

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.

Formula insertion

 Step 3

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

Fill handle

 Step 4

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

Applying filter-How to Remove Both Duplicates in Excel

Step 5

 The Filter icon is displayed in all column headers.

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

Selecting filter options

This is the output.

Final result-How to Remove Both Duplicates in Excel

 


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.

Inserting formula

 Step 2

  •  Press ENTER and drag the Fill Handle.

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.

Selecting filter option

This is the output.

Final result-How to Remove Both Duplicates in Excel

 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.

conditional formatting duplicates-How to Remove Both Duplicates in Excel

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.

Duplicate values window

Step 3

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

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

Sort window

This is the output.

After sorting


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.

Delete sheet rows-How to Remove Both Duplicates in Excel

This is the output.

After deleting


b) Keyboard Shortcuts (CTRL+-)

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

Delete window

This is the output.

After deleting


c) Context Menu Delete Option

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

Context menu

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

Delete window

This is the output.

After deleting


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.

New rule conditional formatting-How to Remove Both Duplicates in Excel

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.

New formatting rule window

Step 3

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

Formatting cells window

Step 4

  •  In the New Formatting Rule window, click OK.

New formatting rule window-How to Remove Both Duplicates in Excel

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

Result

Step 5

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

Custom sort-How to Remove Both Duplicates in Excel

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.

Sort window

This is the output.

Result

Step 7

Follow the steps described in Method 3 to delete cells.

This is the output.

Removing both duplicates-How to Remove Both Duplicates in Excel


Method 5 – Using a VBA Macro Code

This is the sample dataset.

Dataset-How to Remove Both Duplicates in Excel

Step 1

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

Inserting 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

Macro code

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.

Final result-How to Remove Both Duplicates in Excel


Download Excel Workbook


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

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

2 Comments
  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,

      Thanks for your appreciation.

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo