How to highlight duplicates in Google Sheets?

If you regularly use Google Sheets at work, you have surely experienced a situation where you found duplicates in a column while completing data, which is very difficult to find manually.

The same situation is often repeated when you import a significant amount of data from another source into the sheet. This can cause duplicate data to lead to incorrect results of your calculations.

What to do in this situation?

In this article, I will explain to you how to safely highlight duplicate data in one or even two columns, thanks to conditional formatting functions.

How to find and highlight duplicates in a column – Google Sheets

Probably the best way to find data quickly is to use the conditional formatting function and highlight duplicates with colors.

Let’s use for this purpose the red background color of the cell where the duplicate will be located.

Turning to the details:

  1. First, open the file in Google Sheets where you want to find and highlight the duplicate cells.
    In the open file, select the column data range for which you want to highlight duplicates.


  2. After selecting the range of data to check, select Format -> Conditional Formatting.


  3. Set two items in the panel on the right.
    The first is the color in which the duplicates will be highlighted. The standard color is green. The second element is the function that will find duplicates and highlight them.


  4. Start by setting the color. To do this in the “Formatting style” section, select a color from the available palette. The default is the background color of the cell. There you can additionally change, for example, the color of the font or its style – bold, etc.


  5. Once the formatting style is ready, move on to setting the function that will highlight duplicate data.
    To do this, in the ” Format rules” section, select the “Custom formula is” option.

    After selecting this option, a box will appear for completing the formula based on which duplicate data will be selected.
    The formula you should use is =COUNTIF().
    The exact formula in this case is: =COUNTIF(B:B;B1)>1.
    “B:B;B1” because the data I am checking for duplication is in column B and starts in cell B1. In the case where the data is in column A, the function itself would look like this: =COUNTIF(B:B;B1)>1.

However, what if the data is in more than one column?

In this case, the instructions look very similar.

How to highlight duplicates in two or more columns in Google Sheets?

Google Sheets also allows you to highlight duplicates in multiple columns.

To do this, you need to do a little modification of the above instructions.

Starting from the beginning:

  1. First, open the file in which you want to highlight duplicate cell values in multiple columns.
    In the open file, select the range of data in multiple columns for which you want to highlight duplicates.


  2. After selecting the range of data to check in the columns, again select “Format” -> “Conditional Formatting”.


  3. In the panel on the right as before, set two items.
  4. Start by setting the color. To do this in the “Formatting style” section, select a color from the available palette. As you already know, the default is the background color of the cell. There you can additionally change, for example, the color of the font or its style – bold, etc. Do it exactly the same as in the earlier instructions.


  5. Once the formatting style is ready, move on to setting the function that will highlight duplicate data in the columns.
    To do this again, select “Formatting rules”. Continue in the same way, select the “Custom formula is” option.

    After selecting this option, a box will appear for completing the formula based on which duplicate data in the columns will be highlighted.
    Once again, the formula to use is =COUNTIF().
    In this case, meanwhile, the formula to use is: =COUNTIF(A:B;A1)>1.
    “A:B;A1” because the data I am checking for duplication is in columns A and B and starts in cell A1. If the data were in columns A, B and C, the function itself would look like this: =COUNTIF(A:C;A1)>1.

With the above instructions, you will quickly and safely highlight duplicate data in Google Sheets, so you will surely improve your work.

Related posts