How to Highlight Duplicates on Google Sheets on PC or Mac

Co-authored by wikiHow Staff

Last Updated: May 15, 2018 Tested

This wikiHow teaches you how to use a custom formula in Google Sheets conditional formatting tool in order to highlight the cells with duplicate values, using a computer.

Steps

1
Open Google Sheets in your internet browser. Type sheets.google.com into the address bar, and hit ↵ Enter or ⏎ Return on your keyboard.
2
Click the spreadsheet you want to edit. Find the spreadsheet you want to filter on your saved sheets list, and open it.

Advertisement
3
Select the cells you want to filter. Click a cell, and drag your mouse to select the adjacent cells.
4
Click the Format tab. This button is on a tabs bar at the top of your spreadsheet. It will open a drop-down menu.
5
Select Conditional formatting on the menu. This will open the formatting sidebar on the right-hand side of your screen.
6
Click the drop-down menu below "Format cells if" on the sidebar. This will open a list of filters you can apply to your spreadsheet.
7
Select Custom formula is on the drop-down menu. This option will allow you to manually enter a filter formula.
8
Type =COUNTIF(A:A,A1)>1 into the "Value or formula" box. This formula will let you highlight all the duplicate cells in the selected range.
- If the range of cells you're editing is in a different column than column A, change A:A and A1 in the formula to your selected column.
- For example, if you're editing cells on column D, your formula should be =COUNTIF(D:D,D1)>1.
9
Change A1 in the formula to the beginning cell of your selected range. This part of the custom formula indicates the first cell in your selected data range.
- For example, if the first cell of your selected data range is D5, your formula should be =COUNTIF(D:D,D5)>1.
10
Click the blue Done button. This will apply your custom formula, and highlight every duplicate cell in the selected range.

Advertisement