Select the range of cells that you want to highlight duplicates

In order to select the range of cells that you want to check for duplicates, click on one cell and then drag your mouse to select the desired cells.

Open the Conditional format rules panel

For you to access the Conditional format rules panel, so that you highlight the duplicates in your cells, you need to click on the “Format menu” at the top of the page and then click on “Conditional formatting.”

Format cells based on a custom formula

In order to find duplicates, we will need to change the formatting of the cells based on a custom formula. So, in the Conditional formatting rules panel, specifically in the Format rules section, click on the “Format cells if…” menu and select “Custom formula is.”

Add custom formula

Add the following formula to highlight the cells with duplicate content: =COUNTIF($A$2:$A$23, $A2)>1

Note that this formula assumes that the data you want to check for duplicates is in column A and that you want to apply the formatting to the range A2:A23. Modify the formula as needed.

Tip: If you want to highlighting cells in multiple columns too, use the formula =COUNTIF($A$2:$F$25,A2)>1 to do so. This one will check the duplicates from cell A2 to cell F25, so modify the formula according to your data range.

Customize formatting style

In the Formatting style section of the Conditional format rules panel, choose how you want to highlight the duplicate cells. In our example, we choose a yellow background to make the duplicates stand out. Once you’ve finished choosing a formatting style, click on “Done.”

Your duplicates are now highlighted

Well done! The duplicate cells in your Google Sheets are now highlighted according the formatting style you chose.