How to Find Duplicate in Google Sheets

You can easily locate duplicate values in Google Sheets through the Conditional Formatting tool. Using Conditional Formatting, Excel formats cells that match the set condition.

If you’re familiar with Excel, you may have realized there isn’t a dedicated tool as “Highlight duplicates” in Google Sheets. However, you can still use a custom formula to highlight the duplicate values in the selected cells. You could also use the Find tool to locate the duplicate values.

Table of Contents

Use COUNTIF in Conditional Formatting

For our custom formula, we will be using the COUNTIF function. The COUNTIF function is a library function that counts values that meet the set criteria.

In the function, You must pass an array and less than 1 condition as arguments. Using the COUNTIF function, the conditional formatting tool will run the array and apply the format to the cells with all repeated values.

The formula will vary slightly depending on the range you choose to pass as an array.

1. Open your Google Sheets.
2. Select the cells from the row you wish to apply the formatting.
3. From the menubar, select Format > Conditional Formatting.
   

   

4. In the Conditional format rules panel, drop down the list under Format cells if.
5. Choose Custom Formula is.
   

   

6. In the Value or Formula section, use the formula with the COUNTIF function in this format =COUNTIF(range, cell) > 1.
   

   

7. Under Formatting Style, customize the way you want the highlighted cell to look.

Example 1: Find Duplicates in Individual Columns

In the following sheet, we’re looking to find the most preferred fruit in the classroom. The row with the list of fruits is C, so our range will be C:C. The list of fruits begins from the C2 cell so we will pass that argument first.

=COUNTIF(C:C, C2) > 1

Using this formula, Sheets will apply the set formatting to cells that have repeated values.

Example 2: Find Duplicates in Multiple Columns

In this example, we are looking to find the most preferred lunch between four students. The most duplicated value will be the most preferred lunch among these students.

The range will start from $B:$C. The dollar sign ($) before a cell is used to create an absolute cell reference. This means even if you change the values, the location remains the same.

=COUNTIF($B:$C, B2) > 1

Sheets will run this formula in these two rows and highlight the cells with the repeated values.

Find Tool

You could use the Find Tool to highlight specific repeated values from the grid. You can simply enter the value in the tool and use the arrow keys to switch between the repeated cells. In addition to locating the repeated values by highlighting the cell, the Find tool also displays the number of times the item has been repeated.

Refer to the following steps to use the Find tool to highlight the repeated values:

1. Open your worksheet.
2. Use the shortcut, Ctrl + F, to open the Find tool.
3. In the message box, enter the repeated keyword.
   

   

Furthermore, you could configure the Find tool to perform an even more advanced search. To explore more about what the Find tool can do to locate the repeated values, follow these steps:

1. From the Find tool pop-up, select the three-dot menu.
   

   

2. In the Search section, select the drop-down menu to choose where you wish the Find tool to run.
3. Select the boxes for the additional settings you prefer.