Color Duplicates in Google Sheets: Conditional Formatting

To color duplicate values in Google Sheets, select the cells, open Format > Conditional formatting, choose Custom formula is, and enter a formula such as =COUNTIF($A$2:$A,A2)>1 for values in column A starting at A2. Choose a fill color and click Done; the formatting will update as values change.

How to highlight duplicates with conditional formatting

Conditional formatting is the practical way to identify repeated values without changing or removing data. It applies a color when a rule is true, so you can still edit, sort, or copy the underlying cell values. In the example below, column A contains a list with a header in A1, and the data begins in A2.

Select the range to check, such as A2:A. Selecting the cells below the header avoids treating the heading as a value to compare.
Choose Format > Conditional formatting. The conditional-formatting panel opens on the right.
Under “Format rules,” open the rule-type menu and select Custom formula is.
Enter =COUNTIF($A$2:$A,A2)>1. The formula checks whether the value in each row occurs more than once in the column.
Under “Formatting style,” choose a fill color, text color, or other available style, then click Done.

For example, if A2:A contains the names Lee, Morgan, Lee, and Sam, both cells containing Lee are colored. The rule does not pick a permanent color for a particular name; it evaluates the current contents of the selected range. If a duplicate is edited so it appears only once, its highlight disappears.

What the duplicate formula checks

COUNTIF counts how many cells in a range match a criterion. In =COUNTIF($A$2:$A,A2)>1, the first argument is the range being searched and the second is the current cell’s value. The comparison with 1 turns the count into a true-or-false test: a value appearing twice or more passes the rule.

The dollar signs in $A$2:$A keep the search range anchored to column A as conditional formatting evaluates each row. The reference A2 is deliberately relative: for the next row, Sheets evaluates A3 against the same search range, then A4, and so on. If the reference were fixed as $A$2, every row would test the value in A2 instead of its own value.

COUNTIF treats text comparisons as case-insensitive, so “North” and “north” count as matches. A duplicate also means an exact match under the function’s comparison rules; it does not automatically ignore extra spaces or differences in punctuation. For instance, “Lee” and “Lee ” may not behave like the same entry. If the source data is inconsistent, clean or normalize it before relying on the highlight.

Keep blank cells from being highlighted

A column with empty cells can be confusing if a rule treats blanks as a repeated value. To explicitly skip empty cells, use this version for data beginning in A2:

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

It first checks that the current cell is not empty, then checks its count. Keep the Apply to range set to A2:A (or to the actual data range) so the formula’s starting reference matches the first row being formatted. If your data starts in row 5, for example, change the formula’s row references to 5 as well.

Blank-looking cells may contain spaces or formulas that return an empty string rather than being truly empty. The formula above excludes a value equal to an empty string, but a cell containing one or more spaces is not necessarily empty. If such cells are present, trim or clean the input data, or use a more specific rule suited to how those cells are produced.

Choose whether to color every copy or later copies

The standard formula colors every instance of a repeated value, including its first occurrence. That is useful when the goal is to see all members of a duplicate group. Sometimes, though, you only want to flag later entries while leaving the first one uncolored. With A2 as the first data cell, use:

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

Apply it to A2:A. The search range grows as the rule moves down the list: the first occurrence has a running count of 1, while a second or later occurrence has a count above 1. This can help locate records that were added again, but it does not identify which earlier record is correct or whether the repetition is actually an error.

To ignore blanks while highlighting only later instances, combine the running count with a blank check: =AND(A2<>"",COUNTIF($A$2:A2,A2)>1). As with other custom formulas, the first cell reference must line up with the top row of the Apply to range. Check that alignment if the rule appears to skip or mislabel the first row.

Highlight duplicate records or values across columns

You can color an entire row based on a duplicate in one identifying column. Suppose names or ID numbers are in A and the records span columns A through D. Set the Apply to range to A2:D, then use =AND($A2<>"",COUNTIF($A$2:$A,$A2)>1). The dollar sign before A locks the test to the ID column while the row number remains relative. A repeated ID in column A will therefore color the corresponding cells across that row.

This approach is appropriate only if the value in column A is meant to identify a record. If names are not unique in your data, two people with the same name may be highlighted even when they are separate records. Prefer a stable identifier, such as an account number, when one is available.

To find repeated values anywhere in a two-column area, apply a rule to A2:B and use =AND(A2<>"",COUNTIF($A$2:$B,A2)>1). The current cell is compared against the full selected data area. This checks for repeated cell values across both columns, rather than treating each column as a separate list. For a few specific columns that should be compared with one another, test the rule on a small sample first so you can confirm that its scope matches your intended definition of a duplicate.

Common problems and how to fix them

Nothing is highlighted: Confirm that the rule type is Custom formula is, the formula begins with =, and the Apply to range includes the data. Check that the formula’s first row matches the first row of that range.
The wrong row is colored: Review the relative reference in the formula. If the range starts at A2, use A2 as the current-cell reference; if it starts at A3, use A3. A mismatch shifts the test.
Headers are being treated as values: Start the Apply to range and the COUNTIF search range below the header, or exclude the header row in the formula.
Only some apparent duplicates match: Look for extra spaces, different punctuation, or inconsistent data types. Visually similar values are not always identical for comparison purposes.
The color is difficult to see: Choose a stronger fill or a contrasting text color in the formatting style. If several conditional-formatting rules apply to the same cells, inspect their order and conditions in the side panel.

Large open-ended ranges are convenient because new rows are included automatically, but unnecessary calculations over very large sheets can make a spreadsheet less responsive. If the data has a known limit, use a bounded range such as $A$2:$A$5000 and set the Apply to range accordingly. Extend both ranges when the list grows. For a frequently updated list, an open-ended range can be more convenient; choose based on the sheet’s size and how it is maintained.

Conditional formatting versus removing duplicates

Coloring duplicates and removing duplicates solve different problems. Conditional formatting is a visual flag: it preserves every row and lets you review repeated entries in context. Use it when you want to investigate, verify records, or keep a live warning as new data is entered.

Google Sheets also provides Data > Data cleanup > Remove duplicates. That tool removes repeated rows according to the columns you choose, so it is a data-cleaning action rather than a visual indicator. Before using it, make a copy or otherwise preserve the original if the records matter. Decide which columns define a duplicate: matching email addresses may be meaningful even when other fields differ, while identical names alone may not be enough to identify the same person.

Excel differs in the interface: it has a built-in duplicate-values conditional-formatting rule under Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In Google Sheets, the custom-formula method gives more control over blank handling, later occurrences, and whole-row highlighting. Both approaches format cells rather than proving that a repeated entry is wrong; the meaning of “duplicate” depends on the data and the task.

Frequently Asked Questions

Does Google Sheets have a built-in duplicate-highlighting button?
Google Sheets’ conditional-formatting panel does not offer the same simple Duplicate Values preset found in Excel. Use Format > Conditional formatting and a custom formula such as =COUNTIF($A$2:$A,A2)>1 instead.

Can I highlight duplicates in multiple columns?
Yes. Set the Apply to range to the columns you want to check, then make the COUNTIF range cover those columns. For example, for A2:B, =AND(A2<>"",COUNTIF($A$2:$B,A2)>1) checks each nonblank cell against the values in both columns.

Will the color update when I add or change data?
Yes. Conditional formatting recalculates when sheet values change. An open-ended range such as A2:A can include new rows in column A; a bounded range will not automatically cover rows outside its set range.

Can I highlight duplicates but leave the first occurrence uncolored?
Yes. Use =COUNTIF($A$2:A2,A2)>1 for a list starting at A2. The expanding range counts only entries up to the current row, so the first occurrence is not colored and later occurrences are.

Does highlighting duplicates delete or change the values?
No. Conditional formatting changes only how matching cells look. To remove repeated records, use the separate Remove duplicates tool, and review which columns define a duplicate before confirming that action.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.