How to mark duplicates in Google Sheets
Tiffany Zhao
12 steps
19 seconds
Google
This Scribe shows you 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
2
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 entire column of data: For PCs, press [[ctrl]] + [[shift]] + [[down]]. For Macs, press [[cmd]] + [[shift]] + [[down]]
4
Click "Format" and then "Conditional formatting"
5
Open up the "Format cells if..." field and click "Custom formula is"
6
Click the "Value or formula" field.
7
Type "=countif("
8
Click this field to copy it by pressing [[cmd]] + [[c]]
9
Return to the "Value or formula" field and paste in the range via [[cmd]] + [[v]]
10
Make sure to lock the data set. Do this manually typing in the [[$]] in front of both the letter and number of each cell
The final formula for this example is: =countif($A$2:$A$8,$A2)>1
11
Click "Done"
12
Now you'll see the duplicates highlighted in your specified color
How to mark duplicates in Google Sheets
Tiffany Zhao
12 steps
19 seconds
Google
This Scribe shows you 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
2
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 entire column of data: For PCs, press [[ctrl]] + [[shift]] + [[down]]. For Macs, press [[cmd]] + [[shift]] + [[down]]
4
Click "Format" and then "Conditional formatting"
5
Open up the "Format cells if..." field and click "Custom formula is"
6
Click the "Value or formula" field.
7
Type "=countif("
8
Click this field to copy it by pressing [[cmd]] + [[c]]
9
Return to the "Value or formula" field and paste in the range via [[cmd]] + [[v]]
10
Make sure to lock the data set. Do this manually typing in the [[$]] in front of both the letter and number of each cell
The final formula for this example is: =countif($A$2:$A$8,$A2)>1
11
Click "Done"
12
Now you'll see the duplicates highlighted in your specified color
