Google Sheets: Highlight Duplicates in a Column

To highlight duplicate values in a Google Sheets column, select the cells, choose Format > Conditional formatting, and add a custom formula such as =AND(A2<>"",COUNTIF($A$2:$A,A2)>1). This colors every nonblank value that appears more than once in column A; adjust the column letter and starting row to match your sheet.

Set up the conditional formatting rule

For a list in column A with a header in row 1, use these steps in Google Sheets in a web browser:

Select the cells to check, such as A2:A, or a bounded range such as A2:A500. Leaving the header out prevents its text from being counted as part of the data.
Open Format > Conditional formatting. The Conditional format rules panel appears on the right.
Under “Apply to range,” confirm the range is correct. Under “Format cells if,” choose Custom formula is.
Enter =AND(A2<>"",COUNTIF($A$2:$A,A2)>1) in the formula field.
Choose a fill color or other formatting style, then click Done.

The first cell in the formula, A2, should correspond to the first row of the selected range. If your data starts at B3, for example, apply the rule to B3:B and use =AND(B3<>"",COUNTIF($B$3:$B,B3)>1). The dollar signs keep the COUNTIF search range in column A as the rule evaluates each row. They do not prevent the row-specific reference from changing.

How the duplicate formula works

Conditional formatting checks the formula separately for each cell in the selected range. COUNTIF counts how many times the current cell’s value occurs in the search range. The test >1 means that a value must occur at least twice before the rule applies. As a result, the formula highlights both the original entry and each later copy, rather than marking only the second occurrence.

AND adds a check that the current cell is not empty. Without that check, blank cells can sometimes be treated as matching other blank cells and receive formatting. The formula is a rule for appearance only: it does not remove records, change cell values, or prevent someone from entering a duplicate later.

For a quick visual check, imagine A2:A5 contains “Maple,” “Cedar,” “Maple,” and “Birch.” Both cells containing “Maple” are highlighted because COUNTIF finds two matches. “Cedar” and “Birch” remain unchanged. If the same value appears five times, all five matching cells are highlighted.

Choose the right range and starting row

Use a range that covers the data you want to check, not necessarily the whole worksheet. If your list grows regularly, applying the rule to A2:A allows new entries below the current list to be checked automatically. For a large or frequently recalculated sheet, a planned limit such as A2:A5000 can be more efficient and easier to inspect. If new rows may be added beyond that limit, expand the range when needed.

Keep the range and the formula’s first cell in sync. For a selection beginning at A5, the relative reference should usually be A5, not A2. A mismatch can make the rule evaluate the wrong row, producing highlights that appear shifted. When checking another column, change all references consistently: a list in column D beginning at D2 needs a D-based formula and a D-based apply-to range.

If a sheet has several independent lists, create separate rules for each column. Applying one rule across several columns while using a formula fixed to column A may cause cells in the other columns to be compared against column A instead of against their own column. Separate rules make the intent clear and simplify later edits.

Highlight only later occurrences

Some workflows need to keep the first occurrence visually distinct and mark only subsequent repeats. For data beginning in A2, select A2:A and use =AND(A2<>"",COUNTIF($A$2:A2,A2)>1). Here, the COUNTIF range starts at A2 and expands down to the current row as the rule moves through the column. The first “Maple” in the example has a count of one at its row, so it is not formatted; the next “Maple” has a count of two and is formatted.

This running-count approach depends on the order of rows. Sorting the list can change which appearance is considered the first occurrence. Use the standard whole-column comparison when you want every member of a duplicate group marked regardless of where it appears, and use the expanding range only when identifying later entries is useful.

What counts as a duplicate?

COUNTIF is not case-sensitive for ordinary text comparisons, so values such as “North” and “north” are treated as matches. Spaces can matter: “North” and “North ” may be different entries. Imported data often contains accidental leading or trailing spaces, inconsistent punctuation, or slightly different spellings, so a visual duplicate check may not reveal every value that is conceptually the same.

Dates and numbers should also be checked in context. Two date cells with the same underlying date value can be duplicates even if their display formats differ. Conversely, a date and a text string that merely looks like that date may not compare as the same value. If the goal is to compare standardized names, product codes, or dates, clean or normalize the source data first, or use a helper column with a consistent representation and apply duplicate formatting to that cleaned column.

COUNTIF also treats the asterisk and question mark as wildcard characters in criteria. If your values literally contain those characters, a criterion can match more broadly than a simple character-for-character reading suggests. For most ordinary lists this is not an issue; for codes containing wildcard symbols, verify a few results or use a comparison method designed for literal matching.

Common problems and fixes

Nothing is highlighted: Check that the apply-to range includes the data, that the formula starts with the correct first cell, and that the rule type is Custom formula is. Also confirm that the value actually occurs more than once within the search range.
The wrong rows are colored: Align the formula’s relative cell reference with the first row in the apply-to range. For example, use A3 when the range begins at A3.
Blank cells are colored: Include the nonblank test, such as A2<>"", inside AND.
Some apparent duplicates are missed: Check for extra spaces, different punctuation, number-versus-text values, or differences in the underlying date. Case differences alone generally do not prevent COUNTIF from matching.
Unexpected values are highlighted: Review whether the search range includes headers or unrelated records, and consider whether a value contains a wildcard character. Also inspect other conditional formatting rules that may apply to the same cells.

To review existing rules, select a cell in the affected range and open Format > Conditional formatting. The panel shows the rules and their ranges. Edit the formula or apply-to range there, or remove a rule if it is no longer needed. If several rules overlap, their formatting can make the final color confusing; simplify the rules or choose clearly different styles.

Conditional formatting versus removing duplicates

Conditional formatting is the right choice when you want to find repeated values while preserving every row. It is reversible and updates as the data changes, making it useful for quality checks on shared lists, sign-up sheets, inventory codes, or imported records. It does not decide which copy is correct, and it will not resolve duplicates for you.

Google Sheets also has a data-cleanup option for removing duplicate rows. That is a different operation: removing duplicates changes the dataset, while conditional formatting only changes how matching cells look. Before deleting records, decide which columns define a duplicate and whether repeated rows might represent legitimate separate events. Keep a copy of important data before using any removal or cleanup operation.

In Excel, the broad idea of a custom conditional-formatting formula is similar, but the interface differs. Excel for Microsoft 365 on Windows uses Home > Conditional Formatting; Google Sheets uses Format > Conditional formatting. The steps and rule editor are not interchangeable, so use the menu path for the application you have open.

Keep rules manageable in larger sheets

Whole-column ranges are convenient because the rule can cover future entries, but very large sheets with many complex conditional-formatting rules may become slower to edit or recalculate. Prefer a bounded range when the list has a known size, avoid creating many overlapping rules, and remove obsolete rules from the panel. For a collaborative sheet, add a short note or clear header so other editors understand that the color flags duplicates rather than indicating an error that has already been fixed.

Color is also not the only way to communicate a result. Choose a fill with enough contrast against the cell text, and do not rely on color alone if the sheet is used for an important review. A clear label, filter, or follow-up check can help readers understand what the highlighting means and decide what action, if any, is appropriate.

Frequently Asked Questions

Does Google Sheets highlight both copies of a duplicate?
Yes. With the formula using COUNTIF over the full comparison range and a condition greater than one, every matching nonblank cell is highlighted, including the first occurrence.

How do I highlight duplicates in a column but ignore blanks?
Use a custom formula with a nonblank condition, such as =AND(A2<>"",COUNTIF($A$2:$A,A2)>1), and set the apply-to range to begin at A2. Adjust the references if your data starts elsewhere.

Can I highlight duplicates in two columns separately?
Yes. Add one conditional formatting rule for each column, with its own apply-to range and formula references. That compares values within each column rather than treating a match across different columns as a duplicate.

Does this method remove duplicate values?
No. It only changes formatting. To delete repeated data, use a separate cleanup process and first confirm which rows should be retained.

Will the rule include new rows?
If the apply-to range extends below the current entries, such as A2:A, new values entered within that range are checked automatically. A bounded range stops checking beyond its final row until you expand it.

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.