# How To Highlight Duplicates In A Range: Google Sheets Conditional Formatting

How do you highlight duplicates in a defined range using conditional formatting in Google Sheets?

To highlight cells that are the same value in a range, select the range and use a custom formula in the conditional formatting area that uses relative referencing.

The custom formula you will want to insert into the conditional formatting area is:

``=COUNTIF(range, INDIRECT("RC", FALSE)) > 1``

Where ``` range ``` is the same highlighted range of the conditional formatting range.

Here’s what this would look like:

As you can see from the above example, the ``` range ``` in the Custom formula is section references the same range in the Apply to range section. This is important for the custom formula to work.

This formula works well with any type of range. Above a single column was highlighted, but the same could be applied to a row or a rectangular range.

Can the same formula work with multiple non-joining ranges?

## Highlight Duplicates In Multiple Non-Contiguous Ranges

How can you highlight duplicates in ranges that are not joining?

The same formula is used, however, for each range, you would need to perform one slight change: you need to use ``` INDIRECT ``` for each range (whether named or not).

If I had a rectangular range from ``` C3:F5 ``` and wanted to keep the top row from ``` A1:Z1 ``` and see if both ranges contained duplicates my new formula for checking these ranges would now look like this:

``````=COUNTIF(INDIRECT("A1:Z1"),INDIRECT("RC",FALSE)) +
COUNTIF(INDIRECT("C3:F5"),INDIRECT("RC",FALSE)) > 1``````

Each range you would want to check again would need to be defined in its own ``` COUNTIF ``` function, with every range wrapped in the first parameter with ``` INDIRECT("range") ``` .

You could make it easier by naming the ranges, like so:

``````=COUNTIF(INDIRECT("topRow"),INDIRECT("RC",FALSE)) +
COUNTIF(INDIRECT("rect"),INDIRECT("RC",FALSE)) > 1``````

Here’s the result of applying the above formula to the two ranges which are not connected or joined (I’ve highlighted the entire sheet to show that conditional formatting custom formulas will be needed in both the ranges):

As you can see from the result, the highlighted cells which can be found in both the top row and the rectangular ranges are highlighted.

## Summary

To highlight cells that are duplicated in a range, use the conditional formatting custom formula functionality with the formula ``` =COUNTIF(range, INDIRECT("RC",FALSE))>1 ``` where ``` range ``` matches the highlighted range applied for the conditional formatting.

If you want to highlight duplicates in non-contiguous ranges modify the formula slightly by making the formula: ``` =COUNTIF(INDIRECT("range"), INDIRECT("RC",FALSE)) + ... > 1 ``` where ``` range ``` is the range you want to check for duplicates and the ``` ... ``` representing the repeated formula again for each contiguous range. For example, ``` =COUNTIF(INDIRECT("A1:Z1"), INDIRECT("RC", FALSE)) + COUNTIF(INDIRECT("C3:F5"), INDIRECT("RC", FALSE)) > 1 ``` and this formula being pasted in each custom formula’s conditional formatting range (i.e. in ranges ``` A1:Z1 ``` and ``` C3:F5 ``` ).

By highlighting duplicates in your ranges you’ll be better able to warn users if they have accidentally typed a value that needs to be unique.

Author of scripteverything.com, Ryan has been dabbling in code since the late '90s when he cut his teeth by exploring VBA in Excel when trying to do something more. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP. When he is not behind a screen, Ryan enjoys a good bush walk with the family during the cooler months, and going with them to the beach during the warmer months.