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

Open Your Google Sheet: Navigate to Google Drive, locate your spreadsheet, and open it.
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

Open Your Google Sheet: Access your Google Sheet.
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.

3.Utilizing Google Sheets Functions

Open Your Google Sheet: Go to your Google Sheet.
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:

Open Your Google Sheet: Access your Google Sheet.
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?

Open Your Google Sheet: Navigate to the Google Sheet containing the data with duplicates.
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:

Open Your Google Sheet: Open Your Google Sheet: Navigate to the Google Sheet containing the data from which you want to extract unique values.
Select a Destination Cell: Choose a cell where you want the unique values to appear. This can be in the same sheet or a different sheet.
Enter the UNIQUE Formula: In the selected cell, enter the formula =UNIQUE(A: A) (replace "A" with your column reference). This formula generates a list of unique values from the specified column.
Press Enter: Hit Enter to execute the formula. The unique values from the specified column will be displayed in the selected cell.

Conclusion

Mastering the techniques to find and remove duplicates in Google Sheets is essential for maintaining accurate data and workflow automation. The step-by-step guides provided offer a comprehensive understanding, catering to users with varying levels of expertise. Whether employing conditional formatting, utilizing formulas, or leveraging Google Sheets functions, users can confidently navigate the process. Additionally, the guide on finding unique values underscores the importance of data manipulation and analysis. With these tools at your disposal, whether you're a data analyst, business professional, or student, you can harness the full potential of Google Sheets, ensuring your collaborative work is streamlined, accurate, and free from the complexities introduced by duplicate entries.

Neeraj Shukla

Content Manager at Appy Pie