Highlight Duplicates in Google Sheets: How to Do It

Yes. In Google Sheets in a web browser, choose Format > Conditional formatting, select the cells to check, and add a custom formula using COUNTIF to color repeated values. The formatting updates as the cell contents change, so you can spot duplicates without deleting or changing any data.

What duplicate highlighting does

Google Sheets can flag duplicates with conditional formatting: a rule checks each cell against a range and applies a style, such as a fill color, when the rule is true. It is a visual warning, not a cleanup operation. The values remain in place, and you can edit or remove the rule without altering the underlying data.

For example, suppose product codes are in A2:A1000 and SKU-204 appears in A7 and A19. A duplicate rule can color both cells. This is useful when reviewing imported lists, contact records, order numbers, or inventory before deciding which records, if any, should be removed.

Highlight every repeated value with a custom formula

Use a formula-based rule when you want to control the comparison range or avoid counting a header. The example below assumes product codes are in A2:A1000, with a header in A1:

Select A2:A1000, or select the range that contains the values you want to check.
Choose Format > Conditional formatting. The conditional-formatting rules panel opens.
Confirm the Apply to range field shows the intended cells, such as A2:A1000.
Under Format rules, open the condition list and choose Custom formula is.
Enter =COUNTIF($A$2:$A$1000,$A2)>1.
Choose a formatting style, such as a colored fill, then select Done.

COUNTIF counts how many times the current cell’s value occurs in the comparison range. If the count is greater than one, the rule applies. The dollar signs keep the lookup range fixed as Sheets evaluates the rule for each row, while the row reference in $A2 adjusts. If your data is in another column, update the references. For example, for values in column C, use =COUNTIF($C$2:$C$1000,$C2)>1.

The formula should refer to the top-left cell of the selected range. If the applied range starts at A3, use A3 as the current-row reference and adjust the comparison range accordingly. A mismatch between the first row of the range and the formula reference is a common cause of highlights appearing one row too early or too late.

Exclude blank cells from the highlights

A plain COUNTIF rule can treat empty cells as repeated values, which may color many unused rows in a selected range. Add a test that the current cell is not blank. With values in A2:A1000, use this custom formula:

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

AND requires both conditions to be true: the current cell is nonblank, and its value occurs more than once. This is especially helpful when the rule covers a large range to allow for future entries. If cells contain formulas that return an empty string, the blank test also prevents those displayed-empty results from being flagged.

Highlight only the later occurrences

Sometimes the goal is not to mark every member of a duplicate group, but to identify the second and later entries while leaving the first occurrence alone. For a list in A2:A1000, apply this custom formula to that same range:

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

Here, the counted range starts at row 2 and ends at the current row. The first time a value appears, its running count is one; each later appearance makes the count greater than one. To avoid highlighting blank cells, combine the approach with the nonblank test: =AND($A2<>"",COUNTIF($A$2:$A2,$A2)>1). This can help keep the original record visually distinct when reviewing a list for cleanup.

Highlight entire rows or duplicates based on multiple columns

You can apply a rule to a wider range to color a whole row when a particular value is duplicated. Suppose IDs are in column A and the records span A2:D1000. Select A2:D1000, choose Custom formula is, and enter =COUNTIF($A$2:$A$1000,$A2)>1. The fixed column reference checks the ID in column A for each row, while the formatting applies across columns A through D. If blank IDs should not count, add the nonblank condition.

If a duplicate means that two fields together match, use COUNTIFS. For example, say an order is considered duplicated only when both the customer in column A and order date in column B match. Apply the rule to the rows you want to color and use:

=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)>1

COUNTIFS checks both criteria at once, so a repeated customer with a different date is not flagged as the same pair. For a key made from more columns, add another range-and-criterion pair. Keep the ranges the same size and make sure the row references match the first row in the applied range.

What counts as a duplicate—and what can cause surprises

The rule compares cell values, not a reader’s idea of whether two entries mean the same thing. COUNTIF is not case-sensitive, so ABC-17 and abc-17 are generally treated as a match. However, extra spaces can make otherwise similar text different: “Northwind” and “Northwind ” may not match. Imported data can also mix numbers stored as text with numeric values, or contain inconsistent date formats.

Before removing records, check whether the values are genuinely equivalent. You can clean inconsistent spacing in a helper column with TRIM, or standardize formats first. If you are comparing names, decide whether punctuation, middle initials, or alternate spellings should count as differences. Conditional formatting reveals matches according to the rule; it cannot decide whether two records represent the same person or transaction.

Also note that COUNTIF treats the asterisk and question mark as wildcard characters in criteria. If your values literally contain those characters, a simple COUNTIF rule may not behave like a strict character-for-character comparison. For ordinary IDs and names this is rarely an issue, but it matters for data that uses wildcard symbols as part of its actual text.

Keep the rule manageable as your sheet changes

A bounded range such as A2:A1000 is easy to understand and works well when you know roughly how many rows you need. Its limitation is that entries added below row 1000 will not be checked until you extend both the applied range and the formula’s comparison range. Review the rule if your list grows substantially.

For a very large sheet, avoid applying multiple complex rules to many unnecessary columns. Each rule has to evaluate the relevant cells, and oversized ranges can make a busy sheet harder to manage. A practical approach is to select the data columns rather than the entire sheet, keep the header outside the range, and give each rule a recognizable style. You can review, edit, or delete rules in the conditional-formatting panel.

If a cell already has formatting, conditional formatting may affect how its appearance is shown while the rule is true. If the result looks unexpected, inspect the rule’s Apply to range, formula, and formatting style. Also check whether another conditional-formatting rule applies to the same cells. These checks are often more useful than changing the underlying data immediately.

Highlighting duplicates versus removing them

Highlighting is the safer first step when you need to inspect records or preserve every row. To delete repeated rows instead, Google Sheets has a separate command: select the data, then choose Data > Data cleanup > Remove duplicates. Review the selected columns and whether the data has a header before confirming. That command changes the data, whereas conditional formatting only changes its appearance.

Removal may not be appropriate just because two cells in one column match. Two customers can share a name, or two orders can have the same date while representing separate transactions. Decide which columns define a duplicate before using the cleanup command, and consider making a copy of the sheet if the original records need to be preserved.

Frequently Asked Questions

Does Google Sheets have a built-in duplicate highlight button?
There is not a one-click command that automatically identifies duplicates across any chosen data. Conditional formatting provides the practical built-in method: create a rule, choose Custom formula is, and use a formula such as COUNTIF.

Will duplicate highlighting update when I edit a value?
Yes. Conditional formatting evaluates the rule against the current cell contents, so changing or adding a value within the applied range can change which cells are highlighted. Make sure the range includes new rows.

Can I highlight duplicates in more than one column?
Yes. Create a rule for each column you want to check, or apply a rule to a wider range and use a formula that checks a chosen key column. For duplicates defined by a combination of fields, use COUNTIFS with one criterion for each field.

Why are blank cells highlighted as duplicates?
A range may contain multiple empty cells, and the duplicate-counting rule can flag them. Add a nonblank check, such as AND($A2<>"",COUNTIF($A$2:$A$1000,$A2)>1), and adjust the references to match your range.

Can I use the same steps in Excel?
The general idea is similar, but this guide uses Google Sheets in a browser and its Format > Conditional formatting panel. Excel has its own Conditional Formatting controls and rule dialogs, so menu names and setup steps are not identical.

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.