What is Find Duplicates in Google Sheets?
The feature Find Duplicates in Google Sheets allows you to identify and highlight all the duplicate values in a spreadsheet. Many times, we deal with massive data and may want to double-check to make sure that there are no duplicates. Otherwise, there may be instances when you may want to highlight duplicate values. In such cases, there are a few ways you can find duplicates in Google Sheets.
In the example below, we have highlighted duplicate values by entering the formula under conditional formatting.
=COUNTIF(B:B, B1) > 1
Key Takeaways
- Finding duplicates in Google Sheets is the process of identifying duplicate entries in a dataset.
- Some common methods to find duplicates include Conditional Formatting, using the COUNTIF function, and the UNIQUE function.
- With COUNTIF, you can create a formula that counts how many times each value appears in the range.
- The UNIQUE function generates a list of unique values from a dataset by filtering out duplicates.
- Using these find duplicates in Google Sheets methods makes your dataset free from unnecessary duplicates.
- We use conditional formatting to highlight duplicate values in a range or particular column.
- It is also used to identify repeated entries for analysis purposes.
How to Highlight or Find Duplicates in Google Sheets
Duplicates often occur when there are plenty of data. There are a few ways to find duplicates in Google Sheets.
These include:
- Using conditional formatting
- Using COUNTIF
- Using the UNIQUE function.
Here’s a step-by-step detailed explanation on highlighting duplicates in Google Sheets:
Using conditional formatting
- Step 1: First, open the spreadsheet containing the data.
- Step 2: Under “Format”, select “Conditional Formatting.” Select “Custom formula is.”
- Step 3: Enter any custom duplicate checking formula. Here, we use the standard formula.
=COUNTIF(A:A, A1) > 1.
Also, enter the range as A1:A10.
- Step 4: Click “Done” to see the results.
Using UNIQUE Function
Another way to find duplicates in Sheets is by using the UNIQUE function. This function is useful to look up the unique values in your range and then produce a list removing duplicates. The syntax is as follows:
=UNIQUE(RANGE)
However, this formula can only find duplicates in a single column.
- Step 1: Let’s take the same data set as above and apply the following formula to cell C1.
=UNIQUE(A1:A10)
- Step 2: There’s only one step, which is to enter your formula into an empty cell. As seen below, you get a duplicate-free list in Column C.
If you must use the UNIQUE function for duplicates in multiple columns and rows, you can try this formula.
=UNIQUE(TOCOL(RANGE))
One disadvantage of using the UNIQUE function in Google Sheets is that duplicates are not highlighted but directly removed.
Hence, you may have to manually remove duplicates in your original list, which is time-consuming.
Using COUNTIF Formula
The COUNTIF formula [=COUNTIF (A:A, A1)>1] is the formula we used in the conditional formatting section. This formula tells where to look for duplicates. The A:A represents the column you want to track, and A1 is the specific cell you want to start with. The >1 outside the parentheses means you want to count duplicates in Google Sheets, or values that appear more than once. If using directly in Google Sheets, we make a small tweak to the formula as follows:
=COUNTIF($C$1:$C$10, C1)
The $A$1:$A$10 makes the range absolute (fixed), so it doesn’t vary as you drag the formula down. If the value is not a duplicate you get 1, else if it greater than 1, it indicates a duplicate.
Examples
Let us look at the following examples to easily find duplicate entries in Google Sheets, thereby making data analysis and cleaning up of duplicates much easier than ever before!
Example #1 – Highlight duplicate cells in a single Google Sheets column
You may often encounter the scenario of highlighting duplicate values in a single column. Let’s examine how to implement it.
- Step 1: Assume you have a list of names in Column A who are participants in a contest.
Steps to Highlight Duplicates
- Step 2: Now, select the column. You can click on the header of Column A or select the specific range. Now go to Format->Conditional Formatting.
- Step 3: In the Conditional formatting pane which appears on the right, go to the “Format cells if” dropdown. Here, choose the option “Custom formula is.”
- Step 4: Enter the formula:
=COUNTIF(A1:A13, A1) > 1
This formula checks if the count of each cell’s value in the column is greater than 1. We chose the formatting style where we want the duplicates to be highlighted in blue. Press Done.
- Step 5: Now, the duplicate values in Column A will be highlighted with our chosen color, which will help you easily identify duplicates.
Example #2 – Highlight duplicates in multiple Google Sheets columns
In this example, let us highlight duplicates in multiple rows and columns. We have a larger data set. The process is the same as above, but we enter the entire data range in the Conditional formatting window and formula.
- Step 1: Look at the dataset below. Let’s examine how to highlight duplicate data in Google Sheets with multiple rows and columns.
- Step 2: Select the range. Here, we select A2:C14 as Column D is any repeated data. Go to Format -> Conditional Formatting.
- Step 3: Enter the following formula in the “Custom formula is” option under the “Format cells if” dropdown.
=COUNTIF($A$2:$C$14,INDIRECT(ADDRESS(ROW(),COLUMN(),)))>1
Explanation:
- COUNTIF counts the number of times a value appears within the specified range $A$2:$C$14.
- ROW() returns the row number of the current cell where the formula is being evaluated.
- COLUMN() returns the column number of the current cell.
- ADDRESS(row, column) generates a cell reference based on the row and column numbers.
- INDIRECT(address) converts the address into an actual reference to the cell.
- The >1 checks if the value in the current cell is a duplicate. If that value appears more than once in the range, the formula will return TRUE; otherwise, it will return FALSE.
This formula highlights all duplicates across all three columns and 13 rows, helping easily identify duplicates.
Example #3 – Highlight the entire row if duplicates are in one column
Let us use the same dataset (removing Column D) to highlight an entire row if duplicates are present in column A.
- Step 1: Select the range A1: C14 by clicking and dragging. Go to Format->Conditional Formatting, as before. In the Conditional formatting pane on the right, under the “Format cells if” dropdown, choose Custom formula is.
Now, enter the following formula:
=COUNTIF($A$1:$C$14, $A1) > 1
Here, we highlight all the values with duplicates in Column A by selecting the entire range from Column A to C.
The dollar signs before the column and row make the references absolute.
- Step 2: Under “Formatting style,” choose the background color. Click Done.
Now, any row in the range where there are duplicate values in Column A will be highlighted according to the color we chose.
Example #4 – Remove Duplicates
There are times when you have a small dataset and can remove duplicate rows manually but with large datasets, we can use better removal options.
Look at the dataset below. Let us check how to remove the duplicate rows manually.
- Step 1: Select the range where you want to clear from duplicates. Now, go to Data menu -> Data cleanup -> Remove duplicates.
- Step 2: Select the columns to analyze for duplicates. Here, we choose Column A. If the column has headers, you may choose “Data has Header row.”
- Step 3: Click Remove duplicates. All the duplicated values in Column A have their rows removed.
To remove only values in one column, you may use the UNIQUE function.
Important Things to Note
- Always use absolute values to specify where to look for duplicates with the “$” symbol when using COUNTIF to avoid errors. Therefore, the formula should be as =COUNTIF ($B$1:$C$5, B1)>1 for accurate results.
- Your columns should be named to use the Conditional Formatting feature to find duplicates in Google Sheets.
- Pivot tables help you count and remove duplicates in your data.
- To make the COUNTIF formula handle each row automatically and simultaneously in the column, wrap the formula in ArrayFormula.
Frequently Asked Questions (FAQs)
Removing duplicates from a single column can be done using the UNIQUE function. For two or more columns, you might require multiple approaches, such as:
Pivot table – This table automatically removes duplicates in a separate sheet
UNIQUE function – it can be used to remove duplicates from a wider data range
QUERY function – This is slightly advanced but a great solution.
We can use the COUNTIF function to count duplicates in Google Sheets.
1. Suppose your data is in Column A from A1 to A10, you can enter the following formula in B1 to count the number of times the value in A1 appears.
=COUNTIF($A$1:$A$10, A1)
2. Drag this formula down to apply it to the entire column.
3. COUNTIF shows the count of that value in the column. If it is greater than 1, duplicates are present.
To find duplicates in Google Sheets and remove them, follow these steps.
1. Select the range where you wish to remove duplicates. (e.g., A1:A5 or the entire sheet).
2. Go to Data -> Data cleanup -> Remove duplicates.
3. Choose the columns in which you wish to remove duplicates.
4. Click Remove duplicates to clean up the data.
Download Template
This article must help understand the Find Duplicates in Google Sheets, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to What is Find Duplicates in Google Sheets. We explain how to highlight or find duplicates in Google Sheets with examples. You can learn more from the following articles.
Leave a Reply