Google Sheets: Conditional Formatting for Duplicates

Yes. In Google Sheets, you can highlight duplicate values with a custom conditional-formatting rule: select the cells, choose Format > Conditional formatting, and use a COUNTIF formula. For example, with values in A2:A, apply =COUNTIF($A$2:$A,$A2)>1 to the range A2:A to highlight every non-unique value.

What conditional formatting does

Conditional formatting changes how cells look when they meet a rule; it does not change their contents. A color, text style, or other chosen format can make repeated entries easier to spot while leaving the underlying data available for sorting, filtering, or editing. If the repeated value is corrected or removed, the highlight updates as the rule recalculates.

Google Sheets does not provide a dedicated “duplicate values” choice in its conditional-formatting rule list. The usual approach is a custom formula that counts how many times each value occurs. This is useful for reviewing customer IDs, email addresses, invoice numbers, product codes, or any column where a repeated entry might need attention. It is a visual check, not a validation rule: it will not prevent someone from entering another duplicate.

Highlight every occurrence in one column

To mark both the first instance and any later repeats, create a rule whose formula counts each cell’s value across the full data range. For example, assume row 1 contains a heading and the values to check begin in A2. Select A2:A, then use Format > Conditional formatting. In the side panel, set Apply to range to A2:A, select Custom formula is in the Format cells if menu, enter the formula below, and choose a formatting style.

With the data in A2:A and the rule applied to A2:A, use =COUNTIF($A$2:$A,$A2)>1. The dollar signs keep the counted range anchored at column A and row 2, while the reference $A2 adjusts to the row being formatted. If “Maple” appears in A4 and A11, both cells meet the rule because each value occurs more than once in the checked range.

Select the data cells, excluding the header row.
Choose Format > Conditional formatting.
Confirm the Apply to range box begins at the same top-left cell as your formula reference, such as A2.
Choose Custom formula is, enter the formula, and select the highlight style.
Choose Done, then check a few results against the source values.

When a column contains a heading or notes above the actual data, do not include those rows in the count range unless they should be considered part of the comparison. For a fixed list, you can use a bounded range such as $A$2:$A$500 instead of an open-ended column. That can make the rule easier to understand and limits the comparison to the intended records.

Ignore empty cells

A common surprise is that blank cells may be highlighted when the rule counts an open-ended range. Many empty cells match the blank value, so a plain COUNTIF rule can treat them as repeated. Add a check that the current cell is not empty before counting duplicates.

With values in A2:A and conditional formatting applied to A2:A, use =AND(A2<>"",COUNTIF($A$2:$A,$A2)>1). The first condition excludes blank cells; the second tests whether the nonblank value appears more than once. This is a good default when the column may have unused rows below the current list. If blanks have meaning in your data and should be treated as a repeated category, leave out the blank check deliberately.

For a multi-column record, decide what counts as empty before adding a similar guard. If a row is considered a record only when both its ID and category are present, check both fields. If one field can legitimately be blank, requiring both to be filled would hide duplicates that matter. The rule should reflect the meaning of the data, rather than simply the shape of the sheet.

Highlight only later repeats

Sometimes the first occurrence is the one to keep, and you only want to mark entries that appear afterward. In that case, count from the first data row through the current row instead of counting the entire column. The reference range expands as the rule moves down the sheet.

With values in A2:A and the rule applied to A2:A, use =COUNTIF($A$2:A2,$A2)>1. If “Maple” first appears in A4 and appears again in A11, A4 remains unformatted and A11 is highlighted. A third occurrence is highlighted too. This makes the rule handy for scanning a list where the earliest record is treated as the original, although it does not decide which record is actually correct.

Be careful to keep the range start fixed at the first data row. If the list starts in A5, use a reference pattern beginning at row 5 and apply the rule beginning at A5. A mismatch between the formula’s starting row and the Apply to range top-left cell can produce confusing results because Sheets evaluates the relative references for each formatted cell.

Check duplicates across multiple columns

A duplicate record is sometimes defined by a combination of fields rather than one value alone. For example, a sales list might contain a customer name in column A and an order date in column B. The same name can appear on different dates without being a duplicate, so you can count pairs instead of names.

With names in A2:A, dates in B2:B, and conditional formatting applied to A2:B, use =COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1. COUNTIFS counts rows where both conditions match the current row. This will highlight the pair wherever it appears, not just the individual name. If you want to shade the entire record, set Apply to range to the full row area, such as A2:F, while keeping the formula’s column references fixed as shown.

If incomplete rows should not be included, add conditions for the fields that must be present. For a record requiring both a name and a date, with names in A2:A, dates in B2:B, and formatting applied to A2:F, use =AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1). This ignores rows missing either field. For records defined by three or more columns, add matching range-and-cell pairs to COUNTIFS, and update the blank checks to fit your requirements.

Important matching details

COUNTIF and COUNTIFS are generally case-insensitive in Google Sheets, so entries such as “abc” and “ABC” are treated as matches. They also compare underlying cell values, not merely how those values appear on screen. Two dates displayed with different date formats can still represent the same date value; numbers displayed with different decimal places may also be equal underneath.

On the other hand, extra spaces can cause entries that look similar to be treated as different. A name with a trailing space is not necessarily the same text as the same name without it. If duplicates are being missed, inspect the source values for spaces, inconsistent punctuation, or mixed data types. You can clean or standardize the source data first, but consider making a copy if that cleanup could remove meaningful distinctions.

Conditional-formatting rules can overlap. If a cell has multiple rules, the visible result may depend on the rules and their order in the panel. If a highlight does not appear as expected, check the rule’s Apply to range, its formula, and any other rules affecting those cells. Also confirm that the formula is entered as a custom formula rather than as text in a cell.

Highlighting versus removing duplicates

Highlighting is reversible and preserves every row, making it the safer first step when you need to inspect possible repeats. To actually remove duplicate rows, Google Sheets provides a separate command under Data > Data cleanup > Remove duplicates. That operation changes the data, so review its choices carefully and keep a copy of important information before using it. A highlight does not tell Sheets which row to retain, and removing duplicates is not simply a different color-formatting setting.

Excel differs here: Microsoft Excel includes a built-in Duplicate Values option under Home > Conditional Formatting > Highlight Cells Rules. Google Sheets typically uses the custom-formula method described above instead. If instructions written for Excel mention a Duplicate Values dialog, do not expect that same menu choice in Sheets; use Format > Conditional formatting and a formula.

Frequently Asked Questions

Does Google Sheets have a built-in duplicate highlighting rule?
No dedicated duplicate-values preset is generally available in the conditional-formatting rule list. Use Custom formula is with COUNTIF for one column or COUNTIFS for a combination of columns.

How do I highlight duplicates in a column but not blank cells?
With the data starting in A2 and the rule applied to A2:A, use =AND(A2<>"",COUNTIF($A$2:$A,$A2)>1). The first test skips empty cells, and the count checks whether the value appears more than once.

Can I highlight an entire row when one cell is duplicated?
Yes. Set Apply to range to the row area, such as A2:F, and use a formula that locks the checked column but leaves the row relative. With values in A2:A, use =AND($A2<>"",COUNTIF($A$2:$A,$A2)>1). Each matching row is formatted across columns A through F.

Can I highlight only the second and later occurrences?
Yes. With values in A2:A and the rule applied to A2:A, use =COUNTIF($A$2:A2,$A2)>1. The first instance is not highlighted; subsequent instances are.

Does highlighting duplicates delete or block them?
No. Conditional formatting only changes appearance. It neither deletes existing entries nor prevents new ones. Use Data > Data cleanup > Remove duplicates to remove repeated rows, or consider a separate data-validation approach if the goal is to discourage future duplicate entries.

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.