We’ve added the ‘>1’ conditional to specify that we only want to highlight values that appear more than once.Īfter typing the custom formula, you may adjust the formatting style to apply. The COUNTIF formula will count the number of times each value in our range A2:A21 appears. We’ll use the formula ‘=COUNTIF($A$2:$A$21,A2)>1’ to find duplicates in Column A.

This option will allow you to specify a custom rule using a Google Sheets formula. In the Conditional format rules panel, select the option ‘Custom formula is’ in the given drop-down list. Next, select the Conditional formatting option under the Format drop-down list. In this example, we want to highlight all cells in Column A that have the same last name. Step 1įirst, select the range that you want to check for duplicates. Here’s how to find duplicates with conditional formatting in Google Sheets. We will also provide a few different variations of the formula for different use cases How To Find Duplicates with Conditional Formatting in Google Sheets In this guide, we will show you how to use conditional formatting to highlight duplicate values in Google Sheets. The main function we can use to find duplicates is the COUNTIF function. With custom formulas, you can even determine whether a certain row is a duplicate of another. How To Find Duplicates with Conditional Formatting in Google SheetsĬonditional formatting is a useful feature in Google Sheets that allows you to apply formatting to cells based on certain conditions.įor example, you may want to use conditional formatting to highlight all cells in a column that contains duplicates.How To Use Conditional Formatting for Duplicates in Google Sheets.

Duplicacy formula in google sheet