How to Highlight Duplicates in Google Sheets [Step-by-Step]

→ Access Now: Google Sheets Templates [Free Kit]

Duplicate data is the bane of spreadsheet solutions, especially at scale. Given the volume and variety of data now entered by teams, it’s possible that duplicate data in tools like Google Sheets may be relevant and necessary, or it could be a frustrating distraction from the primary purpose of spreadsheet efforts.

The potential problem raises a good question: How do you highlight duplicates in Google Sheets?

We’ve got you covered with a step-by-step look at how to highlight duplicates in Google Sheets, complete with images to make sure you’re on the right track when it comes to de-duplicating your data.

Highlighting Duplicate Data in Google Sheets

Google Sheets is a free, cloud-based alternative to proprietary spreadsheet programs and — no surprise, since it’s Google we’re dealing with — offers a host of great features to help streamline data entry, formatting, and calculations.

Google Sheets has all the familiar functions: File, Edit, View, Format, Data, Tools, etc. and makes it easy to quickly enter your data, add formulas for calculations, and discover key relationships. What Sheets doesn’t have, however, is an easy way to find and highlight duplicates.

While other spreadsheet tools, such as Excel, have built-in conditional formatting tools that can pinpoint duplicate data in your sheet, Google’s solution requires a little more manual effort.

Step-by-Step: How to Highlight Duplicates in Google Sheets (With Pictures)

So how do you automatically highlight duplicates in Google Sheets? While there’s no built-in tool for this purpose, you can leverage some built-in functions to highlight duplicate data.

Here’s a step-by-step guide:

Step 1: Open your spreadsheet.

Step 2: Highlight the data you want to check.

Step 3: Under “Format”, select “Conditional Formatting.”

Step 4: Select “Custom formula is.”

Step 5: Enter the custom duplicate checking formula.

Step 6: Click “Done” to see the results.

Step 1: Open your spreadsheet.

First, head to Google Sheets and open the spreadsheet you want to check for duplicate data.

Step 2: Highlight the data you want to check.

Next, left-click and drag your cursor over the data you want to check to highlight it.

Step 3: Under “Format”, select “Conditional Formatting.”

Now, head to “Format” in the top menu row and select “Conditional Formatting”. You may get a notification that says “cell is not empty” — if so, click on it, and you should see this: