How to Remove Both Duplicates in Excel (5 Easy Ways)

Removing both duplicates in Excel is a common thing while we merge or combine multiple worksheets data. As a result, identical entries exist throughout multiple worksheets. These identical or duplicates pose calculation issues in Excel. In this article, we demonstrate multiple ways to remove both duplicates in Excel.

Let’s say after merging two worksheets’ data we have a dataset that contains identical entries. We can remove those identical entries or both duplicates using a Helper Column, Formula (i.e., combining IF and COUNTIF functions), Conditional Formatting, and VBA Macro Code.

Dataset-How to Remove Both Duplicates in Excel


Download Excel Workbook


5 Easy Ways to Remove Both Duplicates in Excel

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

From the dataset, we notice the occurrences of duplicates among entries (depicted in the below screenshot). And we want both duplicates to be removed.

helper column-How to Remove Both Duplicates in Excel

To do so, we can use a helper column to identify both duplicates and sort them to display entries that have no duplicates.

Step 1: Insert a Helper Column (i.e., Column E) adjacent to the raw dataset’s columns.

Helper column insertion

Step 2: Paste the following formula in any cell (i.e., E5).

=COUNTIF($B$5:$B$17,B5)=1

The formula counts cells in the B5:B17 range that match the criteria B5 and returns TRUE (if the cell satisfies the criteria) or FALSE (if the cell does not satisfy the criteria).

Formula insertion

 Step 3: Press ENTER and Drag the Fill Handle to display TRUE or FALSE in all the cells as depicted in the following image.

Fill handle

 Step 4: Now, Go to the Home Tab > Select Sort & Filter (from the Editing section) > Choose Filter (from the options).

Applying filter-How to Remove Both Duplicates in Excel

Step 5: The Filter icon appears on all the column headers.

Click on the Filter Icon on the Helper Column header (the apply Filter window appears).

Unselect the FALSE option in the apply Filter window.

Click OK.

Selecting filter options

The consequences of unselecting the FALSE option result in a scenario similar to the picture below.

Final result-How to Remove Both Duplicates in Excel

You can modify the formula applied in the Helper Column to display your desired outcome.

Read More: How to Remove Duplicates in Excel Sheet (7 Methods)


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

Similar to Method 1, we can insert a customized formula in any adjacent column to indicate duplicates. In this case, we use IF and COUNTIF functions to develop a customized formula and display any character to showcase the duplicates.

Step 1: Add a helper column and Type the below formula in any blank cell (i.e., 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) where the COUNTIF function returns TRUE or FALSE applying the criteria (i.e., B5) in the range (i.e., B5:B17). Then the IF function returns 1 or 0 if a cell satisfies the criteria or not respectively.

Inserting formula

 Step 2: Hit ENTER afterward Drag the Fill Handle to display 1 or 0’s in the cells.

Fill handle

Step 3: Apply the Filter feature as done in Step 4 of Method 1.

Then Click on the Filter icon of the Formula Result column header.

Unselect option 0.

And Click OK.

Selecting filter option

In a moment all the 0’s get removed and only the entries that have no duplicates remain as shown in the below picture.

Final result-How to Remove Both Duplicates in Excel

 According to the formula (i.e., =IF(COUNTIF($B$5:$B$17,B5)=1,1,0)) 1 indicates entries that have no identical entries and 0 indicates entries that have identical entries.

Read More: How to Remove Duplicates Based on Criteria in Excel (4 Methods)


Method 3: Using Conditional Formatting (Highlight Duplicates)

In Excel, Conditional Formatting is one of the common ways to highlight duplicates. In this method, we demonstrate how we can use Conditional Formatting to color format cells in a dataset after that sort and remove them using any convenient options.

Step 1: Select the range (i.e., B5:B17) you want to apply Conditional Formatting.

Hover 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 appears. In the Duplicate Values window,

Choose Duplicate (under the Formats cells that contain note’s 1st box).

Select any Cell Color (i.e., Light Red Fill with Dark Red Text) in the 2nd box.

Click OK.

Duplicate values window

Step 3: Go to the Home tab > Select Sort & Filter (from the Editing section) > Choose Custom Sort (the Sort window appears).

Custom sort

Step 4: In the Sort window,

Select Product (under Column) as Sort by option.

Select Cell Color (under Sort On).

Select the Color under Order.

Choose On Bottom to display the duplicates at the bottom.

Click OK.

Sort window

All the duplicates sit at the bottom of the dataset as depicted in the below screenshot.

After sorting


However, to remove the color formatted duplicates we can follow three ways. They are a) Delete feature, b) Keyboard Shortcuts (CTRL+-), and c) Context Menu Delete option.

 a) Delete feature

Select the color formatted cells’ range. Then, Go to the Home tab > Select Delete (from Cells section) > Select Delete Sheet Rows.

Delete sheet rows-How to Remove Both Duplicates in Excel

The Delete Sheet Rows option deletes all the selected rows as depicted in the following image.

After deleting

Read More: Excel Formula to Automatically Remove Duplicates (3 Quick Methods)


b) Keyboard Shortcuts (CTRL+-)

After selecting the color formatted rows, press CTRL+- altogether. It brings out the Delete window as you see in the latter screenshot.

Select the Entire Row option.

Click OK.

Delete window

Clicking OK deletes all the color-formatted cells as shown in the image below.

After deleting


c) Context Menu Delete Option

Select the formatted cells’ range then, right-click on them. The Context Menu appears.

In the Context Menu, Select Delete.

Context menu

The Delete window pops up. In the Delete window,

Select the Entire row option.

Click OK.

Delete window

By Clicking OK you get all the color formatted cells removed similar to the picture below.

After deleting

Read More: How to Delete Duplicates in Excel but Keep One (7 Methods)


Similar Readings


Method 4: Using Conditional Formatting (New Rule to Apply Color Format)

Identical to Method 3, in this method, we apply the color formatting to cell entries using Conditional Formatting. However, we apply a formula to color format the cells in the Conditional Formatting option. The Conditional Formatting colors format the cells if the formula returns TRUE for the cells.

Step 1: Go to the Home tab > Select Conditional Formatting > Choose New Rule (from the options).

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

Step 2: The New Formatting Rule window appears. In the New Formatting Rule window,

Select Use a formula to determine which cells to format as Select a Rule Type.

Paste the following formula under Format values where this formula is true.

 =IF(COUNTIF($C$5:$C$17,C5)=1,0,1)

This formula declares the same arguments as it does in Step 2 of Method 1 except returns 0 or 1 if entries satisfy the argument or not respectively.

Click on Format (the Format Cells window appears).

New formatting rule window

Step 3: In the Format Cells window, Select the Font color (i.e., Dark Red).

Click OK.

Formatting cells window

Step 4: By clicking OK in Step 3, you get back to the New Formatting Rule window again.

Click OK.

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

Step 4 applies the formula to the selection and colors format of the cells as you can see in the below depiction.

Result

Step 5: To apply Sorting, Select the entire dataset then Go to the Home tab > Select Sort & Filter(from the Editing section) > Choose Custom Sort (from the options). The Sort window appears.

Custom sort-How to Remove Both Duplicates in Excel

Step 6: Inside the Sort window,

Pick Column C (under Column) as Sort by option.

Choose Font Color (under Sort On).

Take the Color under Order.

Choose On Bottom to make the duplicates appear at the bottom.

Afterward, Click OK.

Sort window

Font color formatted cells are placed at the bottom as shown in the below picture.

Result

Step 7: Follow any Delete ways described in Method 3 and you’ll get the same result as depicted below.

Removing both duplicates-How to Remove Both Duplicates in Excel

Read More: How to Remove Duplicate Rows Based on One Column in Excel


Method 5: Using VBA Macro Code

Excel VBA Macro Code also removes both duplicates if we instruct it to do so. Let’s say, we consider only two columns (i.e., Product and Quantity) from where we remove all the duplicates.

So, we have a dataset something like the below image.

Dataset-How to Remove Both Duplicates in Excel

Step 1: Press ALT+F11 to open the Microsoft Visual Basic window. In the Microsoft Visual Basic Window, Select Insert > Choose Module to insert a Module in the Microsoft Visual Basic window.

Inserting module

Step 2: Paste the following code in the Module to remove all the duplicates from the dataset.

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

In the macro code, the COUNTIF function counts cells that have occurrences of more than 1. And the REPLACE function replaces the cells with blank strings.

 Step 3: Hit F5 to run the macro. Return to the worksheet and you see both duplicates get removed as displayed in the below picture.

Final result-How to Remove Both Duplicates in Excel

Read More: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)


Conclusion

In this article, we use Customized Formula, Conditional Formatting, and VBA Macro Code to remove both duplicates in Excel. As all the methods remove both duplicates; it’s quite risky to apply these methods in a dataset if you want to maintain the ingenuity of the dataset. Otherwise, all described methods are super handy in removing both duplicates. I hope this article helps you to tidy your dataset by removing both duplicates. Comment if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

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

ExcelDemy
Logo