How To Highlight Duplicates in Google Sheets

The fastest way to highlight duplicates in Google Sheets is with a COUNTIF formula inside conditional formatting. Select your range, set a custom formula rule, and every duplicate cell lights up automatically. This guide covers how to do it in a single column, across multiple columns, and for entire duplicate rows. It also covers how to flag only the second occurrence, handle multiple conditions, and fix the most common reasons it stops working.

How To Highlight Duplicates in a Single Column

The most common way to highlight duplicates in Google Sheets is with conditional formatting. It’s easy to do in a single column, so we’ll start there, then move on to multiple columns and full duplicate rows.

All you need is a COUNTIF function to check whether a cell value appears more than once, then conditional formatting applies the highlight automatically. (Note that you can also use the UNIQUE function to extract distinct values rather than highlight them.)

Here’s an animation that shows the full process.

Here are the steps to highlight duplicate data in a single column:

1. Select the dataset (excluding headers)
2. Go to Format > Conditional formatting
3. Click Add another rule
4. Confirm the range in the Apply to range field
5. Click Format cells if > Custom formula is
6. Enter the following formula:

   =COUNTIF($A$2:$A$10,A2)>1

7. Under Formatting style, choose your highlight color and click Done

Video Guide

I also made a video that shows exactly how to do it. You can watch it below or check it out on YouTube.

The video is about 10 minutes long and walks through everything you need to know about highlighting multiple instances of any data in your spreadsheet. It covers everything in this guide, plus I’ve included the full written walkthrough with screenshots below.

Visual Walk-Through

If you prefer step-by-step screenshots, here’s how to highlight all the names that repeat in Column A.

To highlight duplicates in Google Sheets using a single column:

1. Select the names dataset (excluding the headers)
2. Click Format > Conditional formatting
   

   

3. Select Add another rule
   

   

4. Confirm the range in the Apply to range field. Adjust it if needed.
   

   

5. Click Format cells if > Custom formula is
   

   

6. Enter the following formula:

   =COUNTIF($A$2:$A$10,A2)>1

   

7. Under Formatting style, choose your highlight color. The default is green, but any color works. Click Done.
   

   

Here’s how it looks when the rule is applied. All duplicate names are now highlighted.

Conditional Formatting is Dynamic

One important advantage of this approach is that conditional formatting updates automatically. If you change a cell value so it no longer has a match, the highlight disappears. If you add a value that creates a new duplicate, it gets highlighted immediately. No manual refresh needed.

How the Highlight Duplicates Formula Works

When you use a custom formula in conditional formatting, Google Sheets evaluates it for every cell in the selected range. If the formula returns TRUE for a cell, that cell gets highlighted. If it returns FALSE, the cell stays unchanged.

In this case, =COUNTIF($A$2:$A$10,A2)>1 counts how many times the value in A2 appears across the entire range. If it appears more than once, the result is TRUE and the cell is highlighted. You can read more about how this function works in the COUNTIF formula guide.

The dollar signs in $A$2:$A$10 are what make this work correctly. Using an absolute reference for the range means it stays locked as the formula moves from row to row, while the cell reference A2 updates relatively. Without the dollar signs on the range, the formula would shift and check the wrong cells.

To remove the highlight later, select the formatted cells, go to Format > Conditional formatting, and delete the rule from the panel on the right.

How To Highlight Only the Second (or Later) Occurrence

The formula above flags every instance of a duplicate, including the first one. If you want to leave the original entry unhighlighted and only flag the repeats, use a mixed reference that expands as the formula moves down:

=COUNTIF($A$2:A2,A2)>1

The key difference is $A$2:A2. The start row is locked with a dollar sign, but the end row is relative. As the formula evaluates each row, the range grows to include only the rows above the current cell. That means it only counts prior occurrences, so the first instance never triggers and only subsequent duplicates get highlighted.

How To Highlight Duplicates in Multiple Columns

You can extend the same approach to find duplicates across multiple columns. In this case, a value gets highlighted if it appears more than once anywhere in the selected range, regardless of which column it’s in.

To highlight duplicate data across multiple columns:

1. Select the full dataset (excluding headers)
2. Click Format > Conditional formatting
   

   

3. Choose Add another rule
   

   

4. Confirm the range covers all columns in the Apply to range field
5. Click Format cells if > Custom formula is
   

   

6. Enter the following formula:

   =COUNTIF($A$2:$C$10,A2)>1

   

7. Choose your highlight color under Formatting style and click Done
   

   

Here’s how it looks once the rule is applied. Any name that appears more than once across all three columns is highlighted.

How To Highlight Duplicate Rows in Google Sheets

The approaches above highlight individual cells with matching values. If you need to find entire rows that are duplicated (where every column in the row matches), the technique is different. It relies on array formulas to concatenate each row into a single string, then check how many times that string appears.

In the example above, rows 2, 4, 7, and 8 are all flagged because every cell in those rows is an exact match with another row.

To highlight duplicate rows:

1. Select the dataset (excluding headers)
2. Click Format > Conditional formatting > Add another rule
3. Click Format cells if > Custom formula is
4. Enter the following formula:

   =COUNTIF(ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1

   

5. Choose your highlight color and click Done
   

   

How the ARRAYFORMULA and COUNTIF Work Together

Since the goal is to match entire rows rather than individual cells, the formula works by collapsing each row into a single combined string, then checking how many times that string appears.

This part creates an array of combined strings using the ampersand (&) to join columns:

=ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10)

This part represents the current row as a single string to compare against that array:

$A2&$B2&$C2

The COUNTIF function then counts how many times the current row’s combined string appears in the array. If the count is greater than 1, the row is a duplicate and gets highlighted.

How To Highlight Duplicates With Multiple Conditions

You can make the duplicate check more specific by combining multiple COUNTIF conditions using the asterisk (*) as an AND operator. This is useful when you need two or more criteria to be true simultaneously before a cell gets highlighted.

The syntax pattern looks like this:

=(COUNTIF(Range,Criteria)>1)*(COUNTIF(Range,Criteria)>1)

Here’s a practical example. Two employees named Henry work in the marketing department, but they have different ID numbers. After updating one ID, a simple name-only check would still flag both rows as duplicates even though the IDs differ. Adding a second condition fixes this:

To apply the two-condition rule:

1. Open the conditional formatting rules panel
2. Enter the combined formula:

   =(COUNTIF($A$2:$C$10,$A2)>1)*(COUNTIF($A$2:$C$10,$C2)>1)

The first condition checks for a duplicate name in column A. The second checks for a duplicate ID in column C. Both must be true for a row to be highlighted. You can extend this pattern with additional conditions using more asterisks, or adjust the threshold (for example, using >0 or >2) depending on your needs.

There’s also a use for concatenation in Google Sheets here, joining multiple cell values into a single string before running the comparison, which is what the ARRAYFORMULA row-matching technique does under the hood.

How To Edit or Delete a Conditional Formatting Rule

To remove the duplicate highlighting without changing the data:

1. Select the cells that have the conditional formatting rule applied
2. Go to Format > Conditional formatting
3. Click the trash can icon next to the rule you want to delete

You can also use the same panel to edit the rule: change the formula, adjust the color, or update the range. The same conditional formatting panel is where you’d set up alternating row colors for readability, if you want to combine that with your duplicate highlighting.

Why Duplicates Aren’t Getting Highlighted: Common Problems

If you’ve followed the steps and Google Sheets still isn’t highlighting what you expect, here are the two most common causes.

Extra Spaces in the Cells

Are there any extra spaces (leading, trailing, or double spaces) in one cell but not the other? COUNTIF looks for an exact match, so a space you can’t see will prevent a match from registering.

To fix this, use the TRIM function to strip extra spaces before the comparison:

=COUNTIF($A$2:$A$10,TRIM(A2))>1

You can also use TRIM to clean the source data itself before applying conditional formatting.

Incorrect Reference Type

There are three reference types in Google Sheets: absolute ($A$1), relative (A1), and mixed ($A1 or A$1). Using the wrong type in the formula range is one of the most common reasons duplicate highlighting fails.

The range in the COUNTIF formula ($A$2:$A$10) should always use absolute references, with both the column and row locked. This keeps the lookup range fixed as the formula evaluates each cell. The cell reference (A2) should stay relative so it updates as the formula moves down.

Tips for Highlighting Duplicates in Google Sheets

• Always exclude header rows from your selected range. Headers will trigger false positives if any data matches the column label.
• If COUNTIF is not catching duplicates, check for extra spaces using TRIM before running the formula
• Use $A$2:A2 (expanding range) instead of $A$2:$A$10 (fixed range) if you only want to highlight the second and later occurrences
• Conditional formatting rules apply in order. If two rules conflict, the one higher in the list wins.
• For large datasets, conditional formatting with COUNTIF can slow down the sheet. If performance is an issue, consider using a helper column with the COUNTIF formula instead, and apply formatting based on that column’s values

Frequently Asked Questions

=COUNTIF(range, criterion)

=COUNTIF($A$2:A2,A2)>1

=COUNTIF($A$2:$A$10,TRIM(A2))>1

Once you’ve identified your duplicates, the usual next step is cleaning them out. The guide on how to remove duplicates in Google Sheets covers every method, from the built-in Data Cleanup tool to UNIQUE formula extraction, so you can keep whichever record you want and delete the rest.