How to Find Duplicates in Google Sheets?

Finding duplicates in Google Sheets is essential for data cleaning and ensuring accuracy. The most common and visually effective method involves using conditional formatting, which highlights duplicate entries directly in your sheet. Other methods include using formulas to identify or count duplicates, and a built-in feature to remove them entirely.

1. Highlight Duplicates Using Conditional Formatting

This method allows you to visually identify duplicate values in your spreadsheet, making them easy to spot.

Steps to Highlight Duplicates:

1. Select Your Data: Highlight the column or range of cells where you want to find duplicates. For example, if you're looking for duplicate names in column A, select column A.
2. Open Conditional Formatting:
   • Click on Format in the top menu.
   • Select Conditional formatting from the dropdown. This will open the Conditional format rules sidebar on the right.
3. Choose Custom Formula:
   • Under the "Format rules" section, locate the "Format cells if..." dropdown.
   • Select Custom formula is from the list.
4. Enter the COUNTIF Formula:
   • In the "Value or formula" box, enter the COUNTIF formula. This formula checks how many times each value appears in your selected range. If a value appears more than once, it's a duplicate.
   • Formula Example (for column A):

     =COUNTIF(A:A,A1)>1

     • Explanation:
       • A:A specifies the entire column A as the range to check. Adjust this if you selected a specific range (e.g., A1:A100).
       • A1 is the first cell in your selected range (it acts as a reference for each cell in the applied range).
       • >1 tells Google Sheets to highlight any cell where its value appears more than once in the specified range.
5. Set Formatting Style:
   • Choose a distinctive fill color, text color, or style (bold, italic) from the "Formatting style" options to make the duplicates stand out.
6. Apply: Click Done at the bottom of the sidebar. All duplicate entries in your selected range will now be highlighted.

Practical Insight:

This method is excellent for visual inspection and quick identification. You can easily see which specific cells contain duplicate information.

2. Identify Duplicates Using Formulas

While conditional formatting highlights, formulas can help you list unique values, count occurrences, or create a separate list of duplicates.

A. Using UNIQUE to List Non-Duplicates:

The UNIQUE function returns a list of unique items from a specified range.

• Formula Example: =UNIQUE(A:A)
• Purpose: This will create a new list containing only the unique values from column A, effectively showing you what values aren't duplicates if you compare it to the original list.

B. Using COUNTIF for a Duplicate Indicator:

You can use COUNTIF in an adjacent column to identify duplicates with a simple indicator.

• Steps:
  1. In an empty column (e.g., column B, if your data is in A), enter the formula in the first row next to your data (e.g., in B1).
  2. Formula Example (for data in column A): =IF(COUNTIF(A:A,A1)>1,"Duplicate","Unique")
  3. Drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to the entire column.
• Purpose: This will label each row as "Duplicate" or "Unique," allowing you to filter by this column to see all duplicate entries.

3. Remove Duplicates (Built-in Feature)

If your goal is not just to find but also to eliminate duplicate rows, Google Sheets has a dedicated tool.

Steps to Remove Duplicates:

1. Select Your Data: Highlight the entire range or columns that you want to check for duplicates. If you select only one column, it will remove rows based on duplicates in that column only.
2. Access Data Cleanup:
   • Click on Data in the top menu.
   • Hover over Data cleanup.
   • Select Remove duplicates.
3. Configure Removal:
   • A dialog box will appear. Confirm if your data has a header row (check the "Data has header row" box).
   • Select the columns that should be checked for duplicate values. If all selected columns are identical in two or more rows, those rows will be considered duplicates.
4. Execute: Click Remove duplicates. Google Sheets will inform you how many duplicate rows were found and removed.

Important Note:

This action permanently removes data, so it's often a good practice to create a copy of your sheet or use conditional formatting first to review duplicates before deleting them.

By utilizing these methods, you can effectively manage and clean your data in Google Sheets, ensuring accuracy and efficiency.