How to highlight duplicates in Google Sheets

How to Highlight Duplicates in Google Sheets using Conditional Formatting

Time and again you will find yourself wondering how to highlight duplicates in Google Sheets. Conditional formatting, although computation intensive if the size of the database is huge, allows you do identify duplicates proactively, instead of trying to find duplicates in Google Sheets manually every time.

Structure of the Sample Google Sheet

The live google sheet for this exercise is posted here and is split across 4 tabs as of today:

  1. Data: Input data for this exercise.
  2. Highlighting Cell in a Column: How to highlight duplicate cell in a column?
  3. Highlighting Entire Row: How to highlight entire row based on a duplicate value in a column?
  4. Highlighting Subsequent Duplicate Rows: How to highlight only subsequent duplicate rows based on a duplicate value in a column?

In the sample data, no two different customers can have the same Customer ID, and therefore the challenge here is to identify:

  1. All duplicate entries for the same customer.
  2. Ensure that the same Customer ID is not assigned to two different customers.

Housekeeping

There are two ways to find the Conditional Formatting menu:

  1. Main Menu –> Format –> Conditional Formatting
  2. Right-click a cell –> Conditional Formatting

Challenge 1: Highlighting Subsequent Duplicate Rows

Switch to tab: Highlighting Cell in a Column in the workbook to understand the output of this exercise.

Highlighting Cell in a Column Output
Highlighting Cell in a Column Output

The idea here is to highlight only one value based on the column of interest.

Highlighting Duplicate Cell in a Column
Highlighting Duplicate Cell in a Column
  • Open the Conditional Formatting Menu.
  • Enter A2:A in the Apply to range input area. Google Sheets will automatically update the range to indicate the maximum number of rows in the sheet.
  • Under the Format rules section, from the dropdown menu, select Custom formula is.
  • In the area provided, enter the formula =COUNTIFS($A$2:$A,$A2)>1.
  • This formula evaluates if there are more than one instances of the value in cell A2 in the range A2:A.
  • Conditional formatting always applies when the result of the formula/condition is TRUE.
  • When done, all the cells in column A with duplicate entries will be highlighted using the formatting chosen in the Conditional formatting menu, in this case, magenta background color with light ping text color.

Challenge 2: Highlighting Entire Row

Switch to tab: Highlighting Entire Row in the workbook to understand the output of this exercise.

Highlighting Entire Row Output
Highlighting Entire Row Output

The idea here is to highlight the entire row based on the value in the column of interest.

  • Repeat everything listed in the solution to Challenge 1, except for one change: the range.
  • Enter A2:B in the Apply to range input area. Google Sheets will automatically update the range to indicate the maximum number of rows in the sheet.

Challenge 3: Highlighting Subsequent Duplicate Rows

Switch to tab: Highlighting Subsequent Duplicate Rows in the workbook to understand the output of this exercise.

Highlighting Subsequent Duplicate Rows Output
Highlighting Subsequent Duplicate Rows Output

The idea here is to highlight the entire row based on the value in the column of interest, except for the first occurrence of the duplicate row.

  • Repeat everything listed in the solution to Challenge 1, except for two changes: the range, and the formula.
  • Enter A2:B in the Apply to range input area. Google Sheets will automatically update the range to indicate the maximum number of rows in the sheet.
  • Under the Format rules section, from the dropdown menu, select Custom formula is. In the area provided, enter the formula =COUNTIFS($A$2:$A2,$A2)>1. Instead of looking in the entire column, we are looking for duplicates only up to the row in question. So, to check if Customer ID in row 4 has already been entered into the list, we will check all rows from Row 2 to Row 4, and not in all the rows.

Processing…
Success! You're on Amit's list.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s