Delete Duplicate Values from Excel & Google Sheets
You can find & eliminate duplicate values in Excel or Google spreadsheets using one of the 6 methods.
Here are the 5 options for Excel & 1 for Google Spreadsheets.
1. Using Excel’s Remove Duplicates Built in Option
To remove duplicate values in Excel, follow 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’)
Note: This option is applicable to Excel 2007 or above.
You can see that the Excel has removed the duplicate entries in the selected range.
It also displays how many entries have been removed. Only unique values are being displayed.
2. Using Formula to Find Duplicates in Excel?
To find duplicate entries in a range, you can use the excel formula “Countif” as explained below. (Also refer the video at the end of this page)
- 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.
3. 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.
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 Macro
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.
Previous Post: VBA Hello World – Excel Macro – For Beginners
Next Post: Insert Calendar Control – In Excel Worksheet – Date Picker
How To Remove Duplicate Values in Excel, Google Sheets?