Are you tired of dealing with duplicate data in your Google Sheets? Don’t worry, we’ve got you covered! Whether you have a list of email addresses or any other type of data, finding and removing duplicates can be a real time-saver. In this article, we’ll show you how to easily identify and delete duplicates in Google Sheets, as well as how to create a list of unique values. Let’s get started!
How to Find Duplicates in Google Sheets
Finding duplicates in Google Sheets is a breeze. You can highlight the duplicate entries using conditional formatting and a custom formula. The formula may vary depending on the number of columns containing repeated values. Here’s how you can do it:
How to Highlight Duplicates in a Single Column
If you have data in a single column and want to identify duplicate values, follow these steps:
- Select the range of data you want to check for duplicates. Then, go to Format > Conditional formatting.
- In the Conditional formatting rules window, click the dropdown menu under Format cells if and select Custom formula is.
- Enter the custom formula to check for duplicates in the Format cells if bar. For example, if you want to find duplicates in cells B2:B15, the custom formula would be
=COUNTIF($B$2:$B$15,B2)>1. If your duplicates are in a different range of data (e.g., A2:A15), your custom formula would be=COUNTIF($A$2:$A$15,A2)>1. - Customize the appearance of duplicate values in the spreadsheet under Formatting style. By default, Google Sheets highlights duplicate data in green. Click Done when finished. (Pro tip: If you change the fill color, choose a high-contrast color scheme like light yellow 3 to improve readability.)
- You can now review the highlighted duplicate data and decide whether or not to delete redundant information.
How to Highlight Duplicates in Multiple Rows or Columns
If you have duplicate data in multiple rows or columns, follow steps one to three above, but modify the custom formula to =COUNTIF($A:$Z,INDIRECT(ADDRESS(ROW(),COLUMN())))>1.
Pro tip: If you want to search for duplicates only in specific rows or columns, update the data range in the Apply to field to match the cell range you want to check. Customize the appearance of duplicates in the spreadsheet under Formatting style. Click Done when finished.
How to Remove Duplicates in Google Sheets
If you want to get rid of duplicate data without manually reviewing it first, Google Sheets has made it super easy. Here’s how to delete duplicate values in Google Sheets:
- Click on any cell that contains data. Then, go to Data > Remove duplicates.
- In the Remove duplicates window, select the columns you want to include in your search for duplicate data. Click Remove duplicates.
- Note: If your spreadsheet includes a header row, make sure to select Data has header row so that Google Sheets ignores that row when removing duplicates.
- Google Sheets will tell you how many duplicate values have been deleted.
Bonus: How to Find Unique Values in Google Sheets
If you want to keep your original data and obtain a list of unique values (i.e., data that isn’t duplicated) from a data range, you can use the UNIQUE function in Google Sheets.
To do this, select an empty column in your spreadsheet. Then, enter the UNIQUE function using the cell range you want to analyze for duplicates, leaving only unique values. For example, =UNIQUE(A2:B15).
Crawlan.com is an excellent tool for finding duplicates in your Google Sheets. Try it now and make your data management a breeze!
This article was originally published in May 2018 by Deb Tennen. The latest update was made in February 2023.
