Excel Find Duplicates & Google Sheets Remove Duplicates
Here are a list of tricks to eliminate duplicate values in Excel and Google Spreadsheets.
- Formula to find duplicates in Excel
- Conditional Formatting – Highlight duplicate values
- Built in option – Remove Duplicates
- Advanced Filter – Get unique values only
- Excel VBA Remove Duplicates
- Google Sheets:
- Formula to remove duplicates in Google Sheets
“Duplicate Values” – No. It is not about identifying whether something is Original or Duplicate. This is a well known word that we hear often in Excel sheets & Google spreadsheets.
Sometimes, we have to delete, highlight or find duplicate values. We will learn how to go about each problem.
1.Formula to Find Duplicates in Excel
This is tricky. Most times this is solved through any VBA macro Or the below explained method can be used. This method uses the Excel Formula ‘Countif’.
- Consider Range A2 to A11 have some duplicates values.
- In column B2, insert the formula as “=COUNTIF(A:A,A2)” and drag this formula to remaining rows in Column B. (For details about advanced options of CountIF refer MSDN.)
- Now Column B will have the count of Values getting repeated in Column A.
- Values that are unique will have count as ‘1’ in Column B. Anything which has count greater than 2 occurs more than once.
To see an illustration on how it works, watch the video at end of this page.
2. Conditional Formatting: Highlight & Find Duplicates in Excel
Excel 2007 or above has build in option to find duplicate values in Excel under “Conditional Formatting” as explained below.
- Select the Range of values that has Duplicates.
- Go to “Conditional Formatting” in ‘Home’ Menu
- Select “Highlight Cell Rules” & click “Duplicate Values”.
- Now choose the color to be highlighted for Duplicate Values.
- Click Ok.
3. Excel Find Duplicates & Remove
It is just one click away. Excel 2007 or above has built in option. To eliminate duplicate values in Excel do these steps.
- Open the Excel Workbook.
- Select the Range of values that has duplicates values.
- Select “Data” from Menu.
- Click “Remove Duplicates” in Data Tools tab. (Shortcut – ‘Alt + A + M’)
You can see that the duplicate entries in the selected range has been removed and only unique values are being displayed.
4. Advanced Filter – Get Unique Values
Follow these steps to copy only Unique values by deleting duplicate values and paste it in a new location.
- Choose the Range of values that has duplicates entries.
- Click on ‘Data’ from Menu.
- Select ‘Advanced’ in ‘Sort & Filter’ tab.
- Click the check box near “Unique Records Only” to Omit Duplicates.
- Choose a destination cell for output and Click OK.
5. Excel VBA Remove Duplicates
We are going to use the above explained 2 function in VBA to remove duplicate entries.
The below is the vba remove duplicates Excel macro code that process data in Column A. Change the Input and Output Range for these functions as per your requirement.
Visual Basic12345678910Sub Excel_VBA_Remove_Duplicates()'Option 1: Using RemoveDuplicates Function in VBA'Single Column + With No Header RowActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo'Combining Multiple Column Data + With Header RowActiveSheet.Range("$A$1:$C$20").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes'Option 2: Using Advanced Filter in VBA to Remove Duplicate valuesRange("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=TrueEnd Sub
This code is a direct macro conversion of the suggestion 3 & 4.
6. How to Remove Duplicates in Google Sheets?
In Google sheets to remove duplicates entries, follow these instructions
- Open Google sheets from Google Drive.
- Enter formula ‘=UNIQUE(A:A)’ in any cell. (Considering Column ‘A’ has duplicate entries)
- Press enter to get only unique values.
- Enter formula ‘=COUNTUNIQUE(A:A) in another cell.
- Spreadsheet will return the count of unique values.
This is how we can eliminate duplicate values in Excel and Google Sheets using different methods. To see a illustration of the tricky formula in Excel, watch the below video and leave your comments.
Youtube Tutorial – Remove Duplicate Values in Excel?
Find Duplicates in Excel & Google Sheets Remove Duplicates