Excel Find Duplicates & Google Sheets Remove Duplicates
To find & eliminate duplicate values in Excel or Google Spreadsheets using these steps.
Excel Remove Duplicates – Which option to 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
“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 in Excel spreadsheets. We will learn how to go about each problem.
1.What is formula in Excel to Find Duplicates?
To find duplicate entries in range, use 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.
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.
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 MS office is not of latest version.
3. How to use Excel Remove Duplicates option?
To eliminate duplicate values in Excel do these steps. It is just one click away. Excel 2007 or above has built in option.
12341. 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.
4. Advanced Filter – Get Unique Values
To copy Unique values to 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 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.12345678910Sub 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?
This is a quick video that explains how to use “countif” formula to remove duplicates. This is just 1 minute video to learn new technique.
Previous Post: Learn Excel VBA – For Beginners – Hello World Program
Next Post: Insert Calendar Control in Worksheet
Find Duplicates in Excel – Google Sheets – Remove Duplicates
Top5 Trending Posts