How To Find And Remove Duplicate Values In Excel And Google Sheets
You can find and eliminate duplicate values in Excel or Google spreadsheets using these simple steps.
Which option should you choose?
- 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 Delete Duplicates – No Choice !!!
Formula to Remove Duplicates in Google Sheets
It is not always about identifying if the value is an original or a duplicate. This is a common word that we hear often in Excel sheets & Google spreadsheets. Sometimes we have to delete, highlight and find duplicate values in Excel spreadsheets and we will learn how to go about each problem.
What is the formula used in Excel to Find Duplicates?
To find duplicate entries in a range, you can use the excel formula “Countif” as explained.
- Open Excel workbook.
- Enter duplicates values in some cells. (Ex: Range A2:A11)
- Insert formula “=COUNTIF(A:A,A2)” in cell B2
- Copy paste this formula to remaining rows in Column B.
- Col B will have count of values in Col A.
- Unique values will have count 1.
- Duplicate values will have count greater than 1.
You can filter the values greater than 1 to get duplicate entries. To see an illustration on how it works, watch the video at end of this page. Most times this is solved through any VBA macro or the above explained method can be used.
Also Read: Excel VBA - Merge Cells - Unmerge Range
2. Conditional Formatting: Highlight & Find Duplicates in Excel
Excel 2007 and above has this option enabled already. You can find duplicate values in Excel files 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.
All the cells with duplicates entries will be highlighted with the color format that you have chosen above. With this method you can only review the values. You will not be able to filter them based on the color, if your Microsoft office is not of latest version.
3. How To Use Excel Remove Duplicates Option
To eliminate duplicate values in Excel, follow these steps. It is just one click away. Excel 2007 or above has built in option.Visual Basic12341. Open the Excel Workbook.2. Select the Range of values that has duplicates values.3. Select "Data" from Menu.4. Click "Remove Duplicates" in Data Tools tab. (Shortcut - 'Alt + A + M')
You can see that the Excel has removed the duplicate entries in the selected range. Only unique values are being displayed.
Also Read: Excel VBA - Convert Column Letter to Number
4. Advanced Filter – Get Unique Values
If you want to copy unique values to a new location by deleting duplicate values, these are the steps.
- 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 function explained in example 2 in VBA to remove duplicate entries. This is the vba remove duplicates excel macro code that processes data in Column A. You only have to 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?
If you want to remove duplicates entries in Google sheets easily, 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.
These are the various methods of how we can eliminate duplicate values in an Excel and Google Sheet. If you want to see a illustration of this tricky formula, watch the below video and leave your comments.
YouTube Tutorial – Remove Duplicate Values in Excel File
This is a quick video that explains how to use “Countif” formula to remove duplicate values in excel. It is just a minute long video and will help you to learn the new technique.
Find Duplicates in Excel – Google Sheets – Remove Duplicates