How to Highlight Duplicate Values in Google Sheets

When compiling contact lists, inventory records, survey responses, or customer databases in Google Sheets, duplicate entries can easily creep in. While Excel includes a dedicated one-click button for highlighting duplicates, Google Sheets achieves this flexibility through Conditional Formatting powered by a straightforward custom formula. Setting up automated duplicate highlighting allows you to identify redundant records instantly before cleaning or deduplicating your dataset.

Why Highlight Duplicates Instead of Deleting Them Immediately?

Highlighting duplicates offers significant workflow advantages over instant removal:

  • Safe Visual Audit: Allows you to review why an item is repeated (such as genuine repeat customers with different order numbers) before making permanent deletions.
  • Real-Time Detection: As new rows are added or imported, any repeated value highlights immediately in colour.
  • Preserves Dataset Context: Keeps adjacent transaction details intact while flagging only the specific duplicate identifier.

Step-by-Step: Highlighting Duplicates Using Conditional Formatting

To highlight duplicates across an individual column or multiple columns:

  1. Open your spreadsheet in Google Sheets.
  2. Select the column or range of cells you wish to examine (for example, click column header A or select range A2:A100).
  3. Click on the Format menu in the top navigation bar.
  4. Select Conditional formatting to open the sidebar pane on the right.
  5. Under the Format rules section, click the drop-down menu currently set to “Format cells if…” and scroll down to select Custom formula is.
  6. In the formula input box, type the following formula:
    =COUNTIF(A:A, A1)>1
  7. Under Formatting style, choose your preferred highlight colour (such as a soft yellow or red fill).
  8. Click Done.

Note: If your selected range begins at cell A2 (leaving header row 1 unselected), modify the formula to match your starting cell: =COUNTIF(A$2:A, A2)>1.

Highlighting Duplicates Across Multiple Columns

If you want to check for repeated values across several columns simultaneously (for example, across columns A through D):

  1. In the Apply to range field of the Conditional formatting rule, enter: A2:D100.
  2. In the Custom formula is field, enter:
    =COUNTIF($A$2:$D$100, A2)>1
  3. Click Done. Any value appearing more than once across that entire grid will highlight instantly.

Removing Highlights or Deduplicating Rows

Once you have reviewed the highlighted entries:

  • To Remove Highlighting: Open Format > Conditional formatting, hover over the rule in the sidebar, and click the bin icon.
  • To Automatically Delete Duplicate Rows: Highlight your data range, click Data > Data cleanup > Remove duplicates, choose the relevant identifier columns, and confirm.

Using conditional formatting formulas gives you complete visual oversight, ensuring your spreadsheets stay clean, accurate, and trustworthy.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.