How to Highlight Duplicate Data in Google Sheets

HOW TO HIGHLIGHT DUPLICATE CONTENT

This works by color coding duplicates so you can easily check them. This formula is the simplest and most effective.

Step 1 : Open the spreadsheet

Step 2 : Select the column you want to highlight

How to Highlight Duplicate Data in Google Sheets Picture 1

Step 3 : Select Format on the toolbar and then select Conditional Formatting

How to Highlight Duplicate Data in Google Sheets Picture 2

Step 4 : In the Apply to range section , the data column you selected above will be automatically added.

How to Highlight Duplicate Data in Google Sheets Picture 3

Step 5 : In ' Format cells if' we change the value here to 'Custom formula is'

How to Highlight Duplicate Data in Google Sheets Picture 4

Step 6 : Enter the following in the box below: '=countif(A:A,A1)>1'

How to Highlight Duplicate Data in Google Sheets Picture 5

Step 7 : In the Formatting Style section , you can change the color to fill the cells. Here I choose red. Then click Done to complete.

How to Highlight Duplicate Data in Google Sheets Picture 6

Now the duplicate data will be highlighted in red immediately. Now you can check again if this data needs to be deleted or not.

How to Highlight Duplicate Data in Google Sheets Picture 7

Coloring duplicate data in Google Sheets makes managing your spreadsheets easier. Use Conditional Formatting or the COUNTIF function to handle data accurately.

David Pac

Update 14 March 2025