How to Highlight Duplicates In Google Sheets?

As the dataset grows, there will be some repetitive values. You may want to highlight, change or delete duplicate values. In such cases, what we do is highlight repetitive ones.

How to Highlight Duplicate Cells in Columns?

You should select the related cells. In this example, we are selecting one column. We will demonstrate some examples with multiple columns later.

1. Select cells(Except Header)
2. Format-> Conditional Formatting

After selecting 'Custom formula is', enter the following formula.

(Don't forget the '$' sign here):

=COUNTIF($A$2:$A$16,A2)>1

You can highlight repeating cells in the color you want. I chose purple in this example. 'Texas' was not processed because I only selected up to Cell 16.

You can do this on a single column as well as on multiple columns.

How to Highlight Duplicate Cells in Multiple Columns?

1. Select cells (You should pay attention to be sure of the cells chosen)
2. Format -> Conditional formatting

Since I have selected all of them as a formula, this should be entered:

=COUNTIF($A$2:$E$21,A2)>1

I chose orange and the table became like this. With 'formatting style' we can choose the format we want. We can make the text bold and italicized. We can change the color, make the font different. Here we just changed the background color.

Up to this point, we have highlighted the column. Now let's highlight the rows.

Before doing so, let's remove the action we did.

How to Remove Cell Highlighting

1. Select the cells you selected above
2. Format-> Conditional formatting -> Trash icon

After the deletion is completed you can highlight the row.

How to Highlight Duplicate Rows

1. Select the cells (except the header)
2. Format-> Conditional formatting
3. Conditional format rules->  format rules -> Enter this as a formula (don't forget $):

=COUNTIF(ARRAYFORMULA($A$2:$A$21&$B$2:$B$21&$C$2:$C$21:$E$2:$E$21),$A2&$B2&$C2&E2)>1

That's it for highlighting duplicating rows and columns.

How many duplicates do the cells have? How many times is a cell repeated? These can be represented numerically, by using formulas.

How to Show Duplicate Cells with Function?

With the COUNTIF function, we can find out the number of repeating cells. In an empty cell and enter the following function:

=COUNTIF(range of cells, first cell)

You can find detailed information about the COUNTIF function here->"How to use COUNTIF function in Google Sheets?"

Pull the cell down.  After that, add a condition to it. For example, which ones are duplicated more than 3 times?

Those that were duplicated more than 3 times came back as 'TRUE'.

That's it! I hope it was helpful.