Google Sheets: Highlight Duplicate Values with Color

How-to basics · Updated November 20, 2026

To highlight every repeated value in Google Sheets, select the cells, choose Format > Conditional formatting, set the rule to Custom formula is, and enter a COUNTIF formula. For example, with values in A2:A1000, use =AND(A2<>"",COUNTIF($A$2:$A$1000,A2)>1), choose a fill color, and click Done. This marks all matching occurrences while leaving blank cells uncolored.

What You’ll Need

  • A Google account and a spreadsheet open in Google Sheets in a web browser.
  • A column or range of values to check. This guide uses A2:A1000 as an example; replace it with the range in your sheet.
  • Permission to edit the spreadsheet. View-only access will not let you add or change a formatting rule.

You do not need an add-on or a separate duplicate-finding tool. Conditional formatting updates the colors when values in the selected range change. Google Sheets does not offer the same built-in “Duplicate Values” preset found in some Excel versions, so the custom formula is the reliable way to create this rule. The formula examples below use standard commas and work in both Sheets and current Excel when the ranges are written explicitly.

Step-by-Step

  1. Choose what counts as a duplicate. Decide whether you are checking one column, a selected block, or a combination of fields. For example, two identical email addresses in column A are duplicates even if their rows contain different names. If duplicates mean matching customer ID and date together, you will need a formula that checks both columns. Make this decision before applying formatting so the rule reflects what you actually want to find.
  2. Check the data and identify its boundaries. Look at the header row and the first and last data rows. If row 1 contains a heading and the values start in row 2, the example range is A2:A1000. Set the ending row to cover your current data and some likely additions. Avoid including the heading: it could be highlighted if its text appears elsewhere. If your data is in another column, use that column’s letter instead of A.
  3. Select the cells to format. Click the first data cell, then drag to the last cell in the range, or click the name box to the left of the formula bar and type the range, such as A2:A1000. Press Enter to select it. The selected range is where the color will appear; the formula range used to count matches should cover the data you intend to compare.
  4. Open the conditional-formatting panel. From the menu, choose Format > Conditional formatting. A Conditional format rules panel opens on the right. If you selected the cells first, check the Apply to range box to confirm it shows the right range. You can edit that box directly if needed, for example by entering A2:A1000.
  5. Choose a custom formula rule. In the Format rules section, open the format-cells-if dropdown and select Custom formula is. Do not choose “Text contains”: it looks for a fragment of text and does not count how many times a value appears. In the formula box, enter the following. This assumes the data is in A2:A1000 and the apply-to range also begins at A2:

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

The dollar signs keep the range being counted fixed as the rule evaluates each cell. The reference A2 is intentionally not locked, so Sheets checks A3 when it formats A3, A4 when it formats A4, and so on. The empty-string check prevents blank cells from being treated as repeated values. COUNTIF counts a value across the stated range, and the greater-than-one test is true for every occurrence of a value that appears more than once.

  1. Pick a visible format and save it. Under Formatting style, choose a fill color that stands out but keeps the text readable. You can also change text color or add bold formatting. Click Done to save the rule. The matching values should now be colored. If nothing changes, see Troubleshooting below before rebuilding the rule.
  2. Test the rule with a known example. If it is safe to do so, enter the same sample value in two cells in the selected range. Both should receive the chosen format. Remove the test entries afterward. You can also check an existing pair by searching for the value and confirming whether both cells are highlighted. Testing helps separate a formula problem from a range that simply has no repeated values.
  3. Adjust the rule when your data grows. If your Apply to range ends at row 1000, entries below row 1000 will not be colored. Either expand the apply-to range and the formula’s count range together, or set a larger practical limit in advance. For example, you can choose A2:A5000 if the sheet may grow to several thousand records. Keep the starting row aligned in the selected range and formula.

To flag only the second and later occurrence—leaving the first instance uncolored—use a running count instead. With values in A2:A1000, apply this formula to A2:A1000:

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

Here the counted range starts at A2 but expands one row at a time. The first instance has a count of one, while later instances have a count above one. Use the all-occurrences formula when you want to see every member of a duplicate group; use the running-count version when you want to identify which entries came after the first.

For duplicates defined by two fields, suppose column A contains customer IDs and column B contains dates, with records in rows 2 through 1000. Apply the following formula to the rows or cells you want to color:

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

COUNTIFS checks that the ID and date both match another row. The two nonblank checks mean a row is considered only when both fields have values; remove or change those checks if blank fields are meaningful in your records. If you apply this rule across A2:B1000, both cells in a matching record can be formatted. If you apply it only to A2:A1000, only the ID cells will be colored, even though the date is part of the comparison.

Common Mistakes

  • Including the header row. If the range starts at A1, the heading is included in the comparison and formatting. Start at the first data row, or change every relevant reference consistently if your data starts elsewhere.
  • Using a mismatched starting row. If Apply to range starts at A3 but the formula refers to A2, the rule evaluates the wrong row for each cell. Make the formula’s relative cell reference match the top-left cell of the apply-to range.
  • Leaving the comparison range unlocked. A reference such as A2:A1000 may shift as the rule evaluates other cells. In a single-column rule, lock the counting range as $A$2:$A$1000 but leave the current-cell reference A2 relative.
  • Expecting only later copies to be highlighted. The standard COUNTIF rule colors every occurrence, including the first one. Choose the running-count version if you want only second and later appearances to stand out.
  • Applying one-column logic to several columns. A rule that checks column A does not automatically determine whether whole records are duplicates. Decide whether you mean a repeated value in one field or a repeated combination of fields, then use a matching rule and apply-to range.
  • Assuming different capitalization creates a different value. COUNTIF is not case-sensitive in Google Sheets, so values such as “North” and “north” are generally treated as matches. If capitalization should distinguish records, this basic rule does not meet that requirement.

Tips

  • Use a consistent data range. For a large list that grows regularly, choose a reasonable upper limit and use it in both Apply to range and the formula. This avoids repeatedly extending the rule while avoiding an unnecessarily huge comparison range.
  • Keep the color readable. A light fill is often easier to scan than a dark one, especially when cells contain long names, addresses, or IDs. Choose a color that is distinct from other status colors already used in the sheet.
  • Use filters to review the results. After highlighting duplicates, you can sort or filter the data to examine the affected records. Formatting only marks cells; it does not delete, merge, or move any data.
  • Review existing rules if colors look unexpected. Open Format > Conditional formatting and check the rules for the selected cells. Multiple rules can apply to the same range, and another formatting rule may make the final appearance confusing.
  • Make a copy before cleaning the data. Highlighting is non-destructive, but removing duplicates is a separate action. Keep a copy of important records before deleting rows or using a data-cleanup command.

Troubleshooting

No cells are highlighted: Confirm that the conditional-format rule is saved, the Apply to range covers the data, and the formula references the top-left cell of that range. Check that the formula’s count range includes all copies. Also verify that the cells truly contain the same value; a trailing space or a different punctuation mark can make two entries unequal.

Blank cells are colored: Make sure the formula includes the nonblank check, such as A2<>””, and that the reference matches the current cell. If your actual data begins in a different row, update the reference and range rather than copying the example unchanged.

Only some duplicates are marked: Check whether the rule’s comparison range stops before the later rows. For example, a formula counting only through row 1000 cannot find a matching value in row 1200. Expand both the count range and Apply to range to cover the records you want checked.

Values that look alike do not match: Inspect for leading or trailing spaces, extra line breaks, inconsistent punctuation, and values stored differently. For example, a number may be stored as numeric data in one cell and as text in another. Correcting or standardizing the source data is usually better than making the formatting rule more complicated.

The wrong cells are colored: Check the relative reference in the custom formula and the first cell in Apply to range. If the selected range begins at B2, the formula should evaluate B2 for its first cell, not A2. For a multi-column rule, use dollar signs on the column reference where appropriate, such as $A2, so the criterion remains in column A across the row.

The color is hard to see: Reopen Format > Conditional formatting, select the rule, and choose a clearer fill or text color. If another rule also formats the same cells, review both rules and their ranges so that the intended style is apparent.

Frequently Asked Questions

Does Google Sheets have a built-in highlight-duplicates button?
No dedicated duplicate-values preset is needed for this method. Use Format > Conditional formatting and create a Custom formula is rule with COUNTIF. The rule updates as the values change.

Does the rule highlight the first occurrence too?
Yes. The formula using COUNTIF across the full range highlights every occurrence of a repeated value, including the first. Use the running-count formula in the guide to highlight only the second and later occurrences.

Can I highlight duplicates in more than one column?
Yes. Apply the rule to the cells or rows you want to color, and use a formula that checks the appropriate field or combination of fields. The two-column COUNTIFS example checks customer ID and date together; change the references to fit your layout.

Will new rows be checked automatically?
Only if they fall inside the rule’s Apply to range and the formula’s comparison range. Set a larger practical ending row for both ranges, or edit the rule when the data grows beyond its current limit.

Are uppercase and lowercase values treated as different?
Not by the COUNTIF rule shown here. COUNTIF is case-insensitive in Google Sheets, so capitalization alone will not keep otherwise identical text values from matching.

How is this different in Excel?
Excel for Microsoft 365 on Windows has a built-in option at Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The custom-formula approach is useful when you need to ignore blanks, highlight only later occurrences, or compare multiple columns. Google Sheets uses Format > Conditional formatting instead.

Related

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.