How to Highlight Duplicate Values in Google Sheets Using Conditional Formatting

Managing extensive datasets in Google Sheets—such as contact lists, product inventories, or event registrations—often leads to unintentional duplicate entries. Manually scanning through hundreds of rows to find matching values is time-consuming and prone to human error. Fortunately, Google Sheets allows you to automatically highlight duplicate values in real time using conditional formatting paired with a simple custom formula.

Why Highlight Duplicates Instead of Deleting Them?

While Google Sheets includes a built-in Data Cleanup tool that removes duplicates instantly, automatic deletion is often too aggressive. Highlighting duplicates provides valuable advantages:

• Visual Verification: Allows you to review potential duplicates before deciding whether to merge, edit, or delete them.
• Preserve Unique Context: Multiple rows may share an identical email address or customer ID while containing different transaction dates or shipping details.
• Real-Time Entry Alerts: Conditional formatting dynamically colours new entries as you type them, immediately alerting you if a duplicate is entered.

How to Highlight Duplicates in a Single Column

To identify repeating entries within a single column—such as Column A containing email addresses—follow these straightforward steps:

1. Open your spreadsheet in Google Sheets.
2. Click and drag to select the range of cells you wish to evaluate (for example, A2:A100), or click the column header (A) to select the entire column.
3. Click on the Format menu in the top toolbar and select Conditional formatting.
4. A sidebar titled Conditional format rules will open on the right-hand side.
5. Under the Format rules section, click the dropdown menu currently showing Format cells if… and scroll down to select Custom formula is.
6. In the text box that appears, enter the following formula:

   =COUNTIF(A:A, A2)>1

   Note: Replace A:A with your column range and A2 with the first data cell in your selected range.
7. Under Formatting style, choose your preferred highlight colour (such as a soft red or yellow fill).
8. Click Done to apply the rule. All duplicate values in that column will instantly highlight.

How to Highlight Duplicates Across Multiple Columns

If you want to check for duplicate values across an entire data table spanning multiple columns (such as A2:D100), adjust the formula to lock the evaluated grid:

1. Select your entire data range (e.g., A2:D100).
2. Open Format > Conditional formatting.
3. Choose Custom formula is from the dropdown menu.
4. Enter the following formula using absolute reference dollar signs:

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

5. Pick a formatting style and click Done. Every value that appears more than once anywhere within the table will highlight.

Pro Tip: Ignore Empty Blank Cells

By default, the standard COUNTIF formula might highlight blank cells if multiple empty rows exist within your range. To prevent blank rows from highlighting, combine your rule with the ISBLANK or LEN function using an AND condition:

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

This ensures only cells containing actual text or numbers trigger the visual highlight, keeping empty rows clean and unformatted.