How To Use Conditional Formatting for Duplicates in Google Sheets

Last Updated on November 2, 2023 by Jake Sheridan

In this tutorial, you will learn how to use conditional formatting for duplicates in Google Sheets

How To Use Conditional Formatting for Duplicates in Google Sheets

Conditional formatting is a useful feature in Google Sheets that allows you to apply formatting to cells based on certain conditions.

For example, you may want to use conditional formatting to highlight all cells in a column that contains duplicates. With custom formulas, you can even determine whether a certain row is a duplicate of another. The main function we can use to find duplicates is the COUNTIF function.

In this guide, we will show you how to use conditional formatting to highlight duplicate values 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

Here’s how to find duplicates with conditional formatting in Google Sheets.

Step 1

First, select the range that you want to check for duplicates. In this example, we want to highlight all cells in Column A that have the same last name.

Step 2

Next, select the Conditional formatting option under the Format drop-down list.

Step 3

In the Conditional format rules panel, select the option ‘Custom formula is’ in the given drop-down list. This option will allow you to specify a custom rule using a Google Sheets formula.

Step 4

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

After typing the custom formula, you may adjust the formatting style to apply. In this example, we’ll be highlighting duplicate values with a red cell color. Click on Done to apply the conditional formatting.

Step 5

All duplicate values in the specified range should now have the specified conditional formatting.

Step 6

You may notice that the example above highlights all instances of the duplicate values. If you would like to ignore the first instance, we can modify the custom formula to ‘=COUNTIF($A$2:$A2,A2)>1’ . The only difference with the new formula is that the end cell in our range is dynamic. This prevents the COUNTIF formula from looking ahead for each output.

Step 7

In the modified formula, the first instance of each string with duplicates is ignored.

Step 8

You may also want to highlight values only if the entire row is a duplicate. For example, the table below includes the last name “Bricham” twice but neither instance is highlighted since they are associated with different first names.

We can modify our custom formula to work with multiple columns. We achieved the formatting above using the formula ‘=COUNTIF(ArrayFormula($A$2:$A21&$B$2:$B21),$A2&$B2)>1’. This formula works by concatenating the last name and first name into a single string using the ‘&’ operator.

Summary

This guide should be everything you need to use conditional formatting for duplicates in Google Sheets.

You may make a copy of this example spreadsheet to test it out on your own.