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.
Like this guide? Check out these related resources
- Google Sheets 101: Google Sheets Tutorial — Everything You Need to Know to Be an Expert
- Free Tool: Google Sheets Training Generator
- Step-by-Step Guide: How to Lock a Row in Google Sheets
- Free Tool: Google Flowchart Generator
- Step-by-step Guide: How to Create a Drop Down in Google Sheets
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!
