Google Sheets: How to Highlight Duplicates

Yes. In Google Sheets, select the cells you want to check, open Format > Conditional formatting, and use a custom formula such as =COUNTIF($A$2:$A,$A2)>1 to color repeated values in column A. Conditional formatting marks duplicates without deleting or changing the underlying data.

How duplicate highlighting works

A conditional formatting rule checks each cell against a condition and applies a chosen style when that condition is true. For duplicate detection, the condition counts how many times the current cell’s value appears in a specified range. If the count is greater than one, the cell qualifies for highlighting.

The example in the opening paragraph assumes that values begin in A2 and continue down column A. The dollar signs keep the search range anchored while the row reference changes as the rule checks each cell. So, when the rule evaluates A3, it compares A3’s value with the same search range, rather than continuing to compare against A2.

This approach highlights every occurrence of a repeated value, including the first one. For example, if “North” appears in A2, A7, and A12, all three cells are formatted. The rule responds when values change, so it can keep working as you edit the sheet; however, the formatting only applies to the range specified in the rule.

Set up a rule in Google Sheets

Select the cells to check, such as A2:A500. Excluding a header prevents the heading from being treated as a value.
Choose Format > Conditional formatting. The conditional formatting panel opens on the right.
Check the “Apply to range” field. Set it to the cells you selected, such as A2:A500.
Under “Format cells if,” choose Custom formula is.
Enter the appropriate formula, then choose a fill color or other formatting in “Formatting style.”
Choose Done. Repeated values in the applied range should now use the selected style.

To check a different column, update both the applied range and the formula’s column references. For example, a rule for values beginning in C2 should use C2 as the current-cell reference and column C as the search range. If your sheet uses a locale that expects semicolons between formula arguments, Sheets may require semicolons instead of the commas shown here.

Choose whether to highlight all copies or later copies

Highlighting every occurrence is useful when you want to see the full set of repeated values. Sometimes, though, the first entry is the original and only later entries need attention. For that view, use a running count: with values in A2:A500 and the formatting rule applied to A2:A500, enter =COUNTIF($A$2:A2,$A2)>1. The search range starts at A2 and expands as the rule moves down the column. The first “North” has a count of one and is not highlighted; the second and third have counts above one and are.

Be careful about where you start the applied range. If the rule starts at A3 but the formula refers to A2, its relative references may not match the intended first data row. Keep the first row in the formula aligned with the first row in the “Apply to range” field, or adjust the formula deliberately.

Ignore blank cells and headers

A basic count rule can mark empty cells if the checked range contains multiple blanks, because an empty value may be counted as occurring more than once. A header can also be marked if its text happens to appear in the data. The simplest safeguards are to begin the applied range below the header and use a formula that excludes empty cells.

With data in A2:A500 and the rule applied to A2:A500, use =AND(A2<>"",COUNTIF($A$2:$A$500,A2)>1) to require a nonblank cell as well as a repeated value. This version uses a bounded search range, which can also make the intended data area easier to understand. If blank-looking cells still behave unexpectedly, check whether they contain spaces or formulas that return an empty string; a cell containing a space is not truly empty.

Find duplicate records across multiple columns

Sometimes the duplicate is a record, not an individual cell. For example, a customer might be identified by the combination of an email address in column A and a region in column B. In that case, checking column A alone could flag two rows with the same email even if the regions differ. Use a multi-condition count instead.

With email addresses in A2:A500 and regions in B2:B500, apply the formatting rule to A2:B500 and use =COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2)>1. This counts rows where both the email and region match the current row, and formats the cells across each matching row. To avoid treating incomplete records as duplicates, use =AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2)>1) with the same range. Add another condition and matching range if the record is defined by more columns.

Decide what “same record” means before applying a rule. If two rows should count as duplicates only when every relevant field matches, include all those fields. If an ID alone defines uniqueness, checking that ID may be clearer and less vulnerable to differences in names, addresses, or other descriptive fields.

Matching details that can cause surprises

COUNTIF is not case-sensitive, so it generally treats “Green” and “green” as the same text for this purpose. It also compares the cell contents, not just what they look like on screen. For instance, numeric 5 and text “5” may be treated differently depending on the stored values and formatting, while two dates displayed in different formats can still represent the same underlying date value.

Leading or trailing spaces matter: “Lee” and “Lee ” are not identical text. If imported data may contain stray spaces, clean or normalize a copy of the data before relying on duplicate flags. Also note that COUNTIF criteria treat the characters * and ? as wildcards. If values contain those characters, a basic COUNTIF rule can match more broadly than a literal character-by-character comparison would suggest.

For case-sensitive matching, with values in A2:A100 and the rule applied to A2:A100, use =SUMPRODUCT(N(EXACT($A$2:$A$100,A2)))>1. EXACT compares text with case sensitivity, and the count tests whether more than one matching value exists. This bounded-range alternative is more specialized; for routine lists, the simpler COUNTIF rule is easier to maintain.

Highlighting versus removing duplicates

Highlighting is a review step: it makes possible repeats visible while leaving every row in place. That is valuable when duplicates may be legitimate, such as two orders from the same customer, or when you need to inspect which copy should be kept. A color is not proof that a record is erroneous; it only means that the rule’s matching condition was met.

To remove repeated rows, Google Sheets provides Data > Data cleanup > Remove duplicates. Review the selected data range and the columns used to identify duplicates before confirming, because removal changes the data and may discard rows. If you only want a separate list of unique values, Google Sheets also has the UNIQUE function. Highlighting, removing duplicates, and producing a unique list solve different problems: choose based on whether you want to inspect, delete, or summarize repeated entries.

Keep the rule useful as the sheet changes

A fixed range such as A2:A500 is straightforward and avoids applying a rule to more cells than you need. If the data grows beyond row 500, expand the applied range and any bounded ranges in the formula. An open-ended range such as A2:A can automatically cover later rows, but it may be less suitable in a very large or complex sheet if the rule does more work than necessary.

To edit an existing rule, select a cell in the affected range and reopen Format > Conditional formatting. The panel lets you adjust the range, formula, or style, and it can show other rules that apply to the selection. If a cell has several conditional formatting rules, review their order and conditions when the displayed color does not appear to match your expectation.

For a reusable review process, keep the rule aligned with the data structure: exclude title rows, include all fields that define a duplicate record, and make the applied range cover the data but not unrelated notes. If you share the sheet, explain whether the highlight means “check this” or “confirmed error.” That prevents collaborators from assuming the formatting itself has removed or validated anything.

How this differs from Excel

Google Sheets in a browser uses Format > Conditional formatting and a custom formula for this method. Excel for Microsoft 365 on Windows also supports formula-based conditional formatting, and it offers a built-in duplicate-values rule through Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The menu names and available rule choices differ, so instructions for Excel should not be followed word for word in Sheets. In either program, highlighting identifies matching values; it does not automatically determine whether a repeated entry is a mistake.

Frequently Asked Questions

Can Google Sheets highlight duplicates automatically?
Yes. Conditional formatting recalculates the rule as the relevant cell values change. Set the formatting range to include the rows you expect to use; entries outside that range will not receive the rule’s formatting until you expand it.

How do I highlight only the second and later occurrences?
Apply a running-count rule to a range beginning at A2. With the values in A2:A500, use =COUNTIF($A$2:A2,$A2)>1. It leaves the first appearance unformatted and marks later appearances.

Can I find duplicates using two columns?
Yes. Use COUNTIFS to count rows that match both fields, such as an email and region, and apply the rule across the relevant row range. Include every column that defines a duplicate record; matching only one field can flag records that are otherwise different.

Does highlighting duplicates delete them?
No. Conditional formatting changes only how qualifying cells look. To remove duplicate rows, use Data > Data cleanup > Remove duplicates and carefully review the selected range and columns before confirming.

Why are blank cells or values with different capitalization highlighted?
A basic count rule may count repeated blanks, so exclude empty cells with an AND condition if needed. COUNTIF is also case-insensitive. Check for spaces, text-versus-number differences, and wildcard characters if results still do not match what you expect.

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.