How to Use Google Sheets

How to Highlight Duplicates in Google Sheets

Mark duplicates in Google Sheets to highlight potential errors or inconsistencies, maintaining data integrity.
Save for later
Save for later
Table of Contents
Like this guide? Save it and create your own!
Save for later
Save for later

How to highlight duplicates in Google Sheets

This guide walks through how to visibly mark duplicates in a single column in Google Sheets using conditional formatting. The same logic came be applied to multiple columns i.e. entire datasets

1. Navigate to your spreadsheet

You can find this in the Google Drive or in your team's knowledge management tool.

2. Select the cell

Select the second cell in the column where you want to mark duplicates. If your column does not have a column header, select the first cell

3. Highlight the column

Highlight the entire column of data: For PCs, press ctrl + shift + down. For Macs, press cmd + shift + down

4. Click "Format."

This button is in the top menu navigational bar of your sheet.

5. Click "Conditional Formatting."

This is at the bottom of the drop down menu pop up.

6. Select the options under "Format cells if..."

This will appear on the lefthand side of your sheet.

7. Click "Custom formula is."

This will allow you to set your own formula.

8. Click "Value or formula."

Select the "Value or formula" field to start typing in your formula.

9. Type "=countif("

This formula will help you identify the range to pull from.

10. Copy and paste the range.

Click the "Apply to range." field to copy it by pressing cmd + c. Return to the "Value or formula" field and paste in the range via cmd + v.

11. Lock the data set.

Do this manually typing in the $ in front of both the letter and number of each cell.

💡 Note: The final formula for this example is: =countif($A$2:$A$8,$A2)>1

Like this guide? Check out these related resources

Get more Google Sheets guides and make your own

Scribe has thousands of guides for Google Sheets, Excel and so much more. Sign up for a free account to save and share this guide with your team.

Scribe is an AI-powered process documentation tool that turns any workflow into a visual step-by-step guide — complete with text, links and annotated screenshots. Build guides for your colleagues and clients in seconds. All for free!

Save and share this guide with your team!