We will use COUNTIF or COUNTIFS functions to create a custom formula for highlighting duplicates in Google Sheets. There is no built-in rule for this purpose.

“Duplicates” refer to multiple occurrences of a value or row. Therefore, we use these two count functions to count values conditionally.

Highlighting duplicates offers two main advantages:

1. It helps alert you to duplicate values when entered or found.
2. You can use the “Data > Create a filter” option to filter duplicates once highlighted, as it provides a “Filter by color” option.

Let’s explore how to highlight duplicates in Google Sheets.

Example 1: Highlight Duplicates in a Single Column

When you want to highlight duplicates in a single column, follow these steps. There are two highlighting options available:

1. The first option highlights all occurrences of duplicates.
2. The second option highlights duplicates excluding the first occurrence.

For example, let’s consider values in column A, starting from cell A1.

To highlight all occurrences of duplicates in column A starting from A1, use the formula:

=COUNTIF($A$1:$A, A1)>1

If you want to apply this rule starting from cell A10 in column A, adjust the formula to: =COUNTIF($A$10:$A, A10) > 1

Ensure that the “Apply to range” in Conditional Formatting matches the starting range specified in the formula (e.g., A1:A or A10:A).

Here’s how to set it up:

1. Select the range (e.g., A1:A).
2. Click on “Format” > “Conditional formatting”.
3. If there’s already a rule applied, click “Add another rule” in the sidebar panel.
4. Under “Apply to range”, enter the correct range (e.g., A1:A or A10:A).
5. Choose “Custom formula is” under “Format rules” and enter the respective formula.
6. Select your desired formatting style and click “OK”.

To highlight duplicates from the second occurrence onwards, use the formula:

=COUNTIF($A$1:$A1, A1)>1

Example 2: Highlight Duplicates by Row

Highlighting duplicates row-wise differs from highlighting duplicates in a single column. Here, we evaluate the entire row (within the selected range) as a single unit.

For example, if A1:B1 contains “Apple” and “Grade A”, and A2:B2 contains the same values, they are duplicates. However, if B2 contains “Grade B”, the two rows are not duplicates.

Here, use the COUNTIFS formulas based on your requirement, i.e., whether you want to highlight all duplicate rows or duplicates from the second instance onwards.

To highlight all occurrences of duplicate rows for the range (Apply to range) A1:B, use the following formula:

=COUNTIFS($A$1:$A, $A1, $B$1:$B, $B1)>1

If you have more columns, include them in the formula accordingly.

To highlight duplicate rows from the second instance onwards, use:

=COUNTIFS($A$1:$A1, $A1, $B$1:$B1, $B1)>1

Example 3: Find Duplicates Across Your Entire Sheet

Some of you might want to highlight duplicates if they appear anywhere in a sheet. For example, if you want to highlight duplicates in the range B2:D, use the following formula:

=COUNTIF($B$2:$D, B2)>1

Use this rule to highlight duplicates across the entire sheet.

Please note that the “Apply to range” setting is important. When you apply the formulas to different ranges, you should modify them accordingly.

Additional Tips

Here I’ll show you how to use some of the above formulas in a slightly different way in real-life examples.

Highlight Frequently Purchased Items (Monthly)

Here, we have the date of purchase in column A and the item name in column B, with the Apply to range set to A1:B. You can use the following formulas:

• To highlight all occurrences of duplicates:

=ArrayFormula(COUNTIFS(EOMONTH($A$1:$A, 0), EOMONTH($A1, 0), $B$1:$B, $B1)>1)

• For highlighting only from the second occurrence of duplicates:

=ArrayFormula(COUNTIFS(EOMONTH($A$1:$A1, 0), EOMONTH($A1, 0), $B$1:$B1, $B1)>1)

In addition to the COUNTIFS function, we use the EOMONTH function, which converts the dates to the end-of-the-month dates. This ensures that, for each month, there is one unique date.

We need to use the ARRAYFORMULA function since we are applying a non-array function (EOMONTH) within an array context.

These two formulas are derived from our example 2, “Highlight Duplicates by Row.” The only additions here are the ARRAYFORMULA and EOMONTH functions.

Highlight Frequently Purchased Items (Daily)

Here, you can use the same formulas as under “Highlight Duplicates by Row.” No changes are necessary unless you have timestamps instead of dates.

If you have timestamps, use the INT function along with ARRAYFORMULA. For example, if A1:A contains timestamps and B1:B contains items, use the following formula:

• To highlight all occurrences of duplicates:

=ArrayFormula(COUNTIFS(INT($A$1:$A), INT($A1), $B$1:$B, $B1)>1)

• For highlighting only from the second occurrence of duplicates:

=ArrayFormula(COUNTIFS(INT($A$1:$A1), INT($A1), $B$1:$B1, $B1)>1)

Resources

Here are some additional resources that address highlighting duplicates from various angles in Google Sheets.

• Highlight Partial Matching Duplicates in Google Sheets
• Highlight Visible Duplicates in Google Sheets
• Skip Duplicates in Min | Small Value Highlighting Row Wise in Google Sheets
• Highlight Max Value Leaving Duplicates in Row Wise in Google Sheets
• Highlight Duplicate Values Based on Occurrence Days in Google Sheets
• How to Highlight Conditional Duplicates in Google Sheets
• How to Conditional Format Duplicates Across Sheet Tabs in Google Sheets