Question

How to highlight duplicate values in sheets?

Answer 1

1

Open Google Sheets in your internet browser. Type sheets.google.com into the address bar, and hit ↵ Enter or ⏎Return on your keyboard.

Advertisement



2

Click the spreadsheet you want to edit. Find the spreadsheet you want to filter on your saved sheets list, and open it.



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.

Advertisement



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.