How to Highlight Duplicate Rows Automatically in Google Sheets

Maintaining clean, accurate spreadsheets requires catching duplicate data before it skews reporting, leads to double billing, or corrupts your database. Whether importing customer email lists, processing inventory logs, or auditing financial entries, accidental duplicate rows can easily sneak into shared workbooks.

Unlike Microsoft Excel, Google Sheets does not feature a single-click "Highlight Duplicates" button in its top toolbar. However, you can easily build an automated duplicate detector using Conditional Formatting paired with custom COUNTIF and COUNTIFS formulas. This guide walks you through highlighting single-column duplicates, flagging entire duplicate rows, and highlighting only second-or-later occurrences.

---

Method 1: Highlight Duplicate Values in a Single Column

If you want to highlight duplicate entries within a single key column (e.g., flagging repeated Email Addresses in Column A or duplicate Order IDs in Column B), use a custom COUNTIF formula rule.

Step-by-Step Instructions:

1. Highlight the column range you want to check for duplicates (e.g., select A2:A100). Avoid selecting row 1 if it contains your column header.
2. In the top menu bar, click Format > Conditional formatting.
3. In the right sidebar panel under Format cells if..., choose Custom formula is.
4. Enter the following formula into the input field:

=COUNTIF(A$2:A$100, A2) > 1

5. Under Formatting style, select a soft red or yellow background fill color.
6. Click Done.

Formula Logic Breakdown:

• COUNTIF(A$2:A$100, A2) counts how many times the value in cell A2 appears throughout the locked range A2:A100.
• > 1 evaluates to TRUE whenever a cell value appears two or more times, triggering the highlight automatically.

---

Method 2: Highlight Entire Duplicate Rows Across Multiple Columns

Sometimes a single column value can legitimately repeat, but the combination of multiple fields (e.g., First Name + Last Name + Date of Birth) constitutes a duplicate record. To highlight the entire horizontal row when multiple fields match, use COUNTIFS.

Step-by-Step Instructions:

1. Highlight your entire dataset body range (e.g., select range A2:D100).
2. Open Format > Conditional formatting.
3. Set Format cells if... to Custom formula is.
4. Enter the multi-column COUNTIFS formula:

=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2, $C$2:$C$100, $C2) > 1

5. Choose your preferred highlight color and click Done.

Crucial Reference Locking Rule ($): Notice the dollar sign before the column letters in $A2, $B2, and $C2. Locking the column letter ensures that Google Sheets evaluates all columns across the row based on the key comparison fields, highlighting the entire row evenly.

---

Method 3: Highlight Only the 2nd (and Later) Occurrences

Methods 1 and 2 highlight all matching entries (both the original record and its duplicates). If you want to keep the first original record looking normal and highlight only the repeated duplicates below it, modify the formula range to expand dynamically:

Select range A2:A100 and enter this custom formula:

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

Why This Works:

By locking only the start of the range ($A$2) while keeping the end relative ($A2), the formula counts occurrences from the top of the sheet down to the current row. For the first original record, the count is 1 (unhighlighted). For any subsequent duplicate row below it, the count reaches 2 or higher, triggering the alert.

---

Duplicate Highlighting Formula Summary Table

Highlight Goal	Target Range	Custom Formula syntax
Highlight All Duplicates (1 Column)	A2:A100	=COUNTIF(A$2:A$100, A2) > 1
Highlight Entire Rows (Multi-Column)	A2:D100	=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1
Highlight 2nd Occurrence Only	A2:A100	=COUNTIF($A$2:$A2, $A2) > 1

---

Troubleshooting Common Formatting Issues

1. Invisible Spaces Prevent Matches:
   If "john@domain.com" and "john@domain.com " (with a trailing space) exist in your sheet, Google Sheets sees them as distinct values. Clean your dataset using =TRIM(A2) or Data > Data cleanup > Trim whitespace first.
2. Case Sensitivity:
   COUNTIF is case-insensitive by default, meaning "APPLE" and "apple" will correctly be flagged as duplicates. If you need case-sensitive matching, use an EXACT array formula combination.
3. Slow Spreadsheet Performance on Large Files:
   Applying custom COUNTIF conditional formatting rules across tens of thousands of rows can cause sheet lag. For massive datasets (50,000+ rows), consider using Data > Data cleanup > Remove duplicates or an Apps Script cleaner instead.