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.
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.
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.
Step 2: Paste the following formula in any cell (i.e., E5).
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).
Step 3: Press ENTER and Drag the Fill Handle to display TRUE or FALSE in all the cells as depicted in the following image.
Step 4: Now, Go to the Home Tab > Select Sort & Filter (from the Editing section) > Choose Filter (from the options).
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.
The consequences of unselecting the FALSE option result in a scenario similar to the picture below.
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).
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.
Step 2: Hit ENTER afterward Drag the Fill Handle to display 1 or 0’s in the cells.
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.
In a moment all the 0’s get removed and only the entries that have no duplicates remain as shown in the below picture.
According to the formula (i.e.,
)) 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.
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.
Step 3: Go to the Home tab > Select Sort & Filter (from the Editing section) > Choose Custom Sort (the Sort window appears).
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.
All the duplicates sit at the bottom of the dataset as depicted in the below screenshot.
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.
The Delete Sheet Rows option deletes all the selected rows as depicted in the following image.
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.
Clicking OK deletes all the color-formatted cells as shown in the image below.
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.
➤ The Delete window pops up. In the Delete window,
Select the Entire row option.
By Clicking OK you get all the color formatted cells removed similar to the picture below.
Read More: How to Delete Duplicates in Excel but Keep One (7 Methods)
- How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)
- Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)
- How to Remove Duplicate Names in Excel (7 Simple Methods)
- Fix: Excel Remove Duplicates Not Working (3 Solutions)
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).
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.
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).
Step 3: In the Format Cells window, Select the Font color (i.e., Dark Red).
Step 4: By clicking OK in Step 3, you get back to the New Formatting Rule window again.
Step 4 applies the formula to the selection and colors format of the cells as you can see in the below depiction.
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.
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.
Font color formatted cells are placed at the bottom as shown in the below picture.
Step 7: Follow any Delete ways described in Method 3 and you’ll get the same result as depicted below.
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.
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.
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
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.
Read More: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)
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.
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
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.