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.
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.
Next, select the Conditional formatting option under the Format drop-down list.
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.
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.
All duplicate values in the specified range should now have the specified conditional formatting.
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.
In the modified formula, the first instance of each string with duplicates is ignored.
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.
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.
More ways to do conditional formatting Google Sheets:
- Remove Conditional Formatting
- How To Do Conditional Formatting
- Copy Conditional Formatting
- Conditional Formatting Based On Another Cell
- Conditional Formatting with a Checkbox
- Conditional Formatting with Multiple Conditions
- Conditional Formatting Based On Another Sheet
- Apply Conditional Formatting Across An Entire Row
- Conditional Formatting to Compare Two Columns
- Conditional Data Validation
- Color Scale Based On Another Cell
- Relative Reference
- Conditional Formulas
- Conditional Formatting Based On Another Cell Not Empty
- Conditional Formatting Based On Another Cell Date
- If Cell Contains Text in
- Highlight Cells
- Highlight Duplicate Cells
- Make Negative Numbers Red