• 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?

    1. Formula to find duplicates in Excel
    2. Conditional Formatting – Highlight duplicate values
    3. Built in option – Remove Duplicates
    4. Advanced Filter – Get unique values only
    5. Excel VBA Remove Duplicates

    Google Sheets Delete Duplicates – No Choice !!!

    1. 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?

    Find Duplicates in Excel

    Find Duplicates in Excel

    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: Create Keyboard Shortcut - to VBA Macro code - in Excel

    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.

    You can see that the Excel has removed the duplicate entries in the selected range. Only unique values are being displayed.

    Also Read: Check If Outlook is Running - then Launch Outlook app - vba code

    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.

    This code is a direct macro conversion of the suggestion 3 & 4.

    Also Read: Convert Excel to HTML Table Tags - VBA Macro Code

    6. How to Remove Duplicates in Google Sheets?

    If you want to remove duplicates entries in Google sheets easily, follow these instructions

    1. Open Google sheets from Google Drive.
    2. Enter formula ‘=UNIQUE(A:A)’  in any cell. (Considering Column ‘A’ has duplicate entries)
    3. Press enter to get only unique values.
    4. Enter formula ‘=COUNTUNIQUE(A:A) in another cell.
    5. 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:
    Next Post: