# How To Find and Remove Duplicates in Google Sheets

By Neeraj Shukla | January 10, 2024 7:51 am

Google Sheets, a versatile cloud-based spreadsheet tool, has become an integral part of modern collaborative work. As your data in Google Sheets grows, the menace of duplicate entries can undermine the accuracy of your information. This comprehensive guide will walk you through the step-by-step process of finding and removing duplicates in Google Sheets, ensuring your data remains pristine and reliable. Whether you're a data analyst, a business professional, or a student managing coursework, these techniques will empower you to maintain data integrity in your Google Sheets.

## How to Find Duplicates in Google Sheets?

Finding duplicates in Google Sheets is a crucial step in maintaining data accuracy and ensuring the integrity of your spreadsheets. Google Sheets offers several methods to identify duplicate entries, ranging from basic to more advanced techniques. Here's a step-by-step guide on how to find duplicates in Google Sheets:

### 1. Using Conditional Formatting

• Select the Range: Highlight the range of cells where you want to check for duplicates.
• Access the Format Menu: Click on "Format" in the top menu and Choose "Conditional formatting" from the dropdown menu.
• Set Up Conditional Formatting: In the Conditional format rules pane on the right, choose "Custom formula is" from the dropdown menu. Enter the formula =COUNTIF(A: A, A1)>1 (replace "A" with the column letter where you want to check for duplicates).
• Choose Formatting Style: Click on the formatting style you prefer to highlight duplicate values.
• Apply Formatting: Click "Done" to apply the formatting rules. Now, duplicate values in the selected range will be visually highlighted.

### 2. Using Formulas

• Create a New Column: Add a new column next to the one containing potential duplicates.
• Use the COUNTIF Formula: In the new column, use the formula =COUNTIF(A: A, A1) (adjust the column reference accordingly). Drag the formula down to cover all cells in the column.
• Filter for Duplicates: Filter the new column to show only values greater than 1. Now, you can easily see which values have duplicates by examining the filtered column.

• Use the UNIQUE Function: In a new column, use the formula =UNIQUE(A: A) (replace "A" with your column reference). This formula will create a list of unique values.
• Compare Lists: Use the formula =IF(COUNTIF(A: A, B1)>1, "Duplicate", "") in the adjacent column (replace "A" and "B" with your column references). Drag this formula down to cover all cells.
• Identify Duplicates: Filter the column with the new formula to show only "Duplicate" entries.

### How to Highlight Duplicates in Multiple Rows or Columns in Google Sheets

Highlighting duplicates across multiple rows or columns in Google Sheets is crucial for identifying patterns and ensuring data consistency. Google Sheets provides several methods to achieve this, and you can choose the one that best suits your needs. Here's a step-by-step guide for highlighting duplicates across multiple rows or columns:
• Create a New Column or Row: Add a new column or row where you want to identify duplicates.
• Use the COUNTIF Formula: If you're checking for duplicates in rows, use the formula =COUNTIF(\$A1:\$Z1, A1) in the first cell of the new column (replace "A1" and "Z1" with the appropriate range). If checking for duplicates in columns, use =COUNTIF(\$A\$1:\$A\$100, A1) in the first cell of the new row (replace "A1" and "A100" with the appropriate range).
• Drag Formulas Across or Down: Drag the formula across the new row or down the new column to cover all cells.
• Filter for Duplicates: Filter the new row or column to show only values greater than 1.

### How to Remove Duplicates in Google Sheets?

• Select the Data Range: Highlight the range of cells from which you want to remove duplicates. Make sure to include the entire columns or rows if you want to consider the entire dataset.
• Access the Data Menu: Click on "Data" in the top menu. No click on any cell that contains data. Then, select the Data tab > Data cleanup > Remove duplicates.
• Select Columns: Choose the columns you want to use in your search for duplicate data from the Remove Duplicates window that pops up. To eliminate duplicates, click.
• Confirm Removal: Google Sheets will let you know how many duplicate values were removed from your sheet.

### How to Find Unique Values in Google Sheets?

Finding unique values in Google Sheets is a useful task, especially when you want to analyze or manipulate distinct data points. Google Sheets provides various methods for identifying unique values. Here are two primary techniques: