Introduction

Time and again you will find yourself in the middle of messy data and wondering, among many other data cleanup activities, how to highlight duplicates in Google Sheets.

Conditional formatting, although computation intensive if the database size is enormous, allows you to 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:

Data: Input data for this exercise.
Highlighting Cell in a Column: How to highlight duplicate cells in a column?
Highlighting Entire Row: How to highlight an entire row based on a duplicate value in a column?
Highlighting Subsequent Duplicate Rows: How to highlight only subsequent duplicate rows based on an identical 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:

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

Do you spend a lot of time with spreadsheets or Airtable?