Google Sheets Formula to Highlight Duplicates in a Column

To highlight repeated values in Google Sheets, select the cells to check, open Format > Conditional formatting, choose Custom formula is, and enter =COUNTIF($A$2:$A,$A2)>1 for values in column A starting in row 2. Choose a formatting color and click Done; every nonblank value that appears more than once will be highlighted.

Set up the conditional formatting rule

Conditional formatting changes how matching cells look without changing their contents. The rule updates as values are edited, so it is useful for spotting repeat entries in a live list rather than making a one-time duplicate report. The formula above checks the value in each row against the full data range and returns TRUE when it occurs more than once.

Select the cells you want checked, such as A2:A. Starting at row 2 leaves a header in row 1 alone. You can use a shorter range, such as A2:A500, if you prefer.
Choose Format > Conditional formatting. The Conditional format rules panel opens on the right.
Confirm the Apply to range field covers the cells you want highlighted.
Under Format cells if, choose Custom formula is, enter the formula, and select a fill color or another formatting style.
Click Done. If you want a different color later, reopen the rule in the panel and change its formatting style.

For the example range beginning at A2, the formula is =COUNTIF($A$2:$A,$A2)>1. The dollar signs keep the checked range anchored while the relative row reference changes for each cell being formatted. In row 2, the rule checks A2; in row 3, it checks A3, and so on. If your list starts in another column or row, update both references so the range begins at the first data cell and the second reference points to the first cell in the applied range.

What the COUNTIF formula is doing

COUNTIF counts how many cells in a range meet a condition. Here, the condition is the current cell’s value. If the count is greater than 1, the custom formula returns TRUE and conditional formatting applies the chosen style. For example, if A2:A6 contains North, South, North, West, South, both instances of North and both instances of South are highlighted. West is not, because it appears only once.

The formula uses an open-ended range, $A$2:$A, so newly added entries below the existing list are included. A bounded range can be a practical choice in a very large sheet or when you only want to check a known section. Keep the applied range and the COUNTIF range consistent: if formatting applies to A2:A100 but the count checks only A2:A50, entries below row 50 will not be compared with the full intended list.

In Google Sheets, COUNTIF comparisons are not case-sensitive. For this purpose, entries such as “Maple” and “maple” count as matches. Extra spaces, however, can make two otherwise similar text entries behave differently. A cell containing “Maple” and one containing “Maple ” may not be treated as the same value. The rule identifies matches according to the values Sheets evaluates; it does not clean or standardize those values for you.

Skip blank cells

Blank cells are often not meaningful duplicates. Depending on the range and data, a plain COUNTIF rule can make empty cells appear to match one another. Add a check that the current cell is not empty to avoid coloring blank rows.

For data in A2:A, apply the rule to A2:A and use =AND(A2<>"",COUNTIF($A$2:$A,A2)>1). The AND function requires both conditions to be true: the current cell must contain something, and its value must occur more than once. This is a useful default for lists that include unused rows below the data.

A cell that looks blank may contain a space or a formula that returns an empty string. Those cases can behave differently from a genuinely empty cell. If a supposedly blank entry is highlighted, inspect it in the formula bar and consider cleaning the source data. Conditional formatting can show a problem, but it does not remove spaces or change formulas in the cells.

Highlight only the later occurrences

Sometimes it is more useful to keep the first appearance unmarked and highlight only subsequent repeats. This makes it easier to review a list from top to bottom while preserving the first entry as the original. Use a running count that expands through the current row instead of counting the entire column range.

With entries in A2:A and the rule applied to A2:A, use =COUNTIF($A$2:A2,A2)>1. The first row’s count covers A2 only, the next row’s count covers A2:A3, and each later row extends that range. In a list where A2 contains North, A3 contains South, and A4 contains North, only A4 is highlighted. Add a nonblank check if empty cells should be ignored: =AND(A2<>"",COUNTIF($A$2:A2,A2)>1).

This is different from the full-range rule: the full-range version marks every member of a repeated group, including the first occurrence; the running-count version marks only the second and later occurrences. Choose based on whether you want to see all values that participate in a duplicate group or just the entries that repeat an earlier one.

Check duplicates using more than one column

A value may be unique by itself but duplicated when considered alongside another field. For example, a product name might appear in several stores, while the combination of store and product should be unique. Use COUNTIFS to count rows that meet multiple conditions.

Assume store names are in A2:A and product names are in B2:B, with the data starting in row 2. To highlight duplicate store-and-product pairs across both columns, select A2:B and use =AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1). The dollar signs before the column letters keep each condition tied to its column as the rule evaluates cells in the applied range. The row numbers remain relative so the formula checks the current row. Both cells in each repeated pair will be highlighted.

If only one column should receive the color, apply the same rule to that column instead. For example, applying it to B2:B highlights the product cell when its store-and-product combination repeats. Add or remove blank checks depending on whether an empty field is valid data. If the rule covers a wide range of columns, test it on a few known examples first to verify that each reference tracks the intended row and field.

Common problems and how to fix them

The header is colored: Start both the applied range and the formula’s data range on the first data row, not the heading row.
Only one duplicate is colored: Check that the COUNTIF range is anchored with dollar signs, and that the applied range covers every cell you meant to format.
Nothing is highlighted: Confirm that Custom formula is is selected, the first-cell reference matches the top row of the applied range, and the rule has a visible formatting style.
Blank rows are colored: Add the nonblank condition shown above, and check whether the cells contain spaces or formulas rather than being truly empty.
Near-matches are missed: Look for leading or trailing spaces, different punctuation, or inconsistent spellings. The duplicate rule does not automatically trim, standardize, or correct text.
The rule works in one place but not another: Review the Apply to range field. A custom formula is evaluated relative to the first cell in that range, so a mismatched starting row can shift what each row compares.

Google Sheets formulas may use different argument separators depending on the spreadsheet’s locale. If the formula editor rejects commas, check File > Settings and the spreadsheet’s locale; some locales use semicolons between function arguments. Keep the formula structure the same while using the separator expected by that sheet.

Conditional formatting versus removing duplicates

Highlighting is a review aid: it leaves every row in place and keeps the formatting rule active as the data changes. That makes it safer when you need to inspect repeated records, decide which one is correct, or retain a history. It does not delete records, merge information, or tell you which entry should be kept.

Google Sheets also provides a remove-duplicates tool under Data > Data cleanup > Remove duplicates. That operation changes the data by removing repeated rows from the selected range, so make a copy or confirm the range and header settings before using it. It is not a substitute for conditional formatting when you need to review the entries first. Conversely, a highlighted cell is not proof that a row can safely be deleted; two records can share a value and still represent separate valid events.

To get a simple count without changing the list, you can also use a formula in a separate column, but conditional formatting is often quicker when the goal is visual scanning. A helper column is more informative when you need labels such as “duplicate” or want to filter the table by status. Either approach depends on defining what counts as a duplicate: one field, a combination of fields, or a cleaned version of the data.

Frequently Asked Questions

Does the formula highlight both copies of a duplicate?
Yes. The full-range formula =COUNTIF($A$2:$A,$A2)>1 marks every matching cell in the checked range, including the first occurrence. Use the running-count version if you want only later occurrences marked.

Can I highlight duplicates across an entire row?
Yes. For a duplicate combination across two fields, apply a COUNTIFS-based custom formula to the range you want colored. Make sure each condition refers to the correct column and that the first row reference matches the top of the applied range.

Does Google Sheets treat uppercase and lowercase as different?
No. COUNTIF is not case-sensitive, so uppercase and lowercase variations are generally counted together. If capitalization should distinguish records, use a separate, carefully designed case-sensitive check rather than assuming the standard COUNTIF rule will do it.

Will new entries be checked automatically?
Yes, if the conditional-formatting range and formula count range include those rows. Open-ended ranges such as A2:A are convenient for entries added below the current list. A fixed range stops at its specified last row.

Does highlighting duplicates delete or change any values?
No. Conditional formatting only changes the cells’ appearance. To remove records, use a separate data-cleanup operation after reviewing the data and confirming which rows should remain.

When to use a helper column instead

A conditional-formatting rule is a good fit when you want an immediate visual signal and do not need to change the underlying list. Use a helper column when people need to filter, sort, or export a clear duplicate status, or when the matching rule is more involved than a straightforward comparison. For example, a workflow might need to treat names with extra spaces as equivalent. In that case, first standardize the values in a helper column, then base the duplicate rule on the standardized results.

Before relying on any duplicate check, decide what makes two records the same and try the rule against a small set with known repeats and non-repeats. A customer ID may be a better key than a customer name; an order may be unique only when its date and reference number are considered together. Choosing the right key prevents a technically correct formula from flagging legitimate separate records—or missing repeats that matter.

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.