Highlight Duplicates In Google Sheets with Conditional Formatting

Data experts are well aware that, despite their best efforts, a duplicate value can occasionally find its way into a spreadsheet. This frequently occurs when working with large databases in Google Sheets.

It is critical to use conditional formatting to find and highlight duplicates, as the user may or may not want to keep them.

In a small spreadsheet, highlighting duplicates is simple – just go through your data and format a few cells.

However, in a large spreadsheet, this process can become significantly more time-consuming.

Fortunately, Google Sheets has tools that allow you to highlight duplicates, wherever they may be hiding, to make life easier for you.

There are a number of ways to find duplicates in Google Sheets.

The easiest option is to use conditional formatting to find and highlight the cells, rows, or even columns that have duplicates.

Highlight duplicates in Google Sheets using conditional formatting

Another option is to use sorting on the column where you believe there are duplicates. This makes it easier for you to identify them.

We’ll discuss the conditional formatting method in this guide along with examples.

You’ll also learn how to remove all duplicates from your Google Sheets.

Without any further ado, let’s get started.

Use Conditional Formatting to Highlight Duplicates in Google Sheets

To see how Google Sheets can highlight duplicate cells with conditional formatting, consider Mr. GSheets daily calorie sheet shown below.

See the calorie sheet:

If you look closely in the Date’s column, you’ll notice one particular date, August 13, appears several times.

Let’s see how we can use conditional formatting to highlight these duplicated cells.

Step 1: Select the Cells you want to scan for duplicates

This step is easy.

Using your mouse pointer, click and drag over all the cells you think contain the duplicates.

In this example, we’ll select all the cells under the Date column (Range A2:A17).

Step 2: Go to Format > Conditional formatting

Move your cursor to your Google sheets Menu area and click on the Format menu.

This action will reveal all the items under the Format menu including Conditional formatting. Simply click on it to open the Conditional Formatting pane on the right.

Step 3: Under the Format rules, click on the drop-down and select “Custom formula is” from the list.

You can only highlight duplicates in Google Sheets using formula-based conditional formatting.

And you can add formula-based conditional formatting using the Custom formula rule.

To do so, first, under the Format rules, click on the drop-down and select Custom formula is from the list.

Step 4: Enter the formula: =COUNTIF(range,top_cell)>1

You’ll then be given a text box to enter your custom formula, kindly enter =COUNTIF(range,top_cell)>1.

The “range” is the selected range of the first step. The “top_cell” is the first, leftmost cell of the current selection. For this example, the “range” is A2:A17. Hence the formula becomes =COUNTIF(A2:A17,A2)>1

Thus, adjust the range and top cell according to your data.

In case you want to apply this to your own data, simply change the A2:A17 part of the formula to the range where you have the duplicates.

Step 4: Set the Formatting

Formatting duplicates in Google Sheets using conditional formatting

Here, there are a lot of formatting styles you can apply to the duplicates.

You can do that by using the font controls under the Formatting style.

There are several preset formatting you can choose from (including green background, yellow background, red background, green text, yellow text, and red text)

Click on the Default button under the Formatting style to reveal the presets.

To define your own format settings, do so using the font controls.

When it comes to conditional formatting, Google Sheets does not allow the application of specific formatting. You cannot, for example, conditionally change the font size, but you can conditionally change other font settings such as Bold, Italic, Underline, Strikethrough, and Font color of the duplicate cells.

Aside from these restrictions, the font controls are the same as those found in the full-fledged format menu which you can use to highlight duplicate cells in Google sheets.

Step 5: Click Done

Google Sheets evaluates the condition and adjusts the formatting as needed as soon as you click the done button.

When you open your spreadsheet or change the value in one of the conditional cells, Google Sheets evaluates the condition and adds or removes formatting as needed.

Now Mr. GSheets’ duplicate dates are apparent now in the screenshot below. The duplicate cells are highlighted as a result of the settings we did in the above steps.

Highlight duplicated cells in Google Sheets using conditional formatting

Note: Duplicate values in Google sheets are determined by the value stored – not necessarily the value displayed in the cell. For example, assume that two cells contain the same date. One of the dates is formatted to display August 13, and the other is formatted to display 08/13/2021. When highlighting or removing duplicates, Google Sheets considers these dates to be the same.

Remove Duplicates from Google Sheets

Before we proceed to the next example on how to highlight the entire row with duplicates, let’s take a look at how to quickly remove duplicates from your Google Sheets.

Sometimes you just need to see where the duplicates are. That’s what exactly conditional formatting does.

Other times too you may need to get rid of them completely. With Google Sheets, you can do this in three simple steps.

In this section, the steps below will show you how to easily get rid of duplicate values in Google Sheets.

Step 1: Select the range that has the duplicates

Step 2: Go to Data > Remove Duplicates

Step 3: Check “Data has header row”

If your data has a header row, click to select this option. If not, ignore this step.

With these simple steps, you can find and get rid of all your duplicate values from your Google Sheets.

Please keep in mind that the Highlight Duplicate rule and the Remove Duplicate feature in Google Sheets do not work in the same way. Remove Duplicates, for example, considers repeated empty cells (blank values) to be duplicated cells and removes them as well. The Highlight Duplicates feature, on the other hand, ignores empty cells and does not highlight them unless you specifically write a formula to highlight the empty cells.

Step 4: Click the “Remove Duplicates” button to confirm

As soon as you hit on the Remove duplicates button, Google sheets will get rid of all duplicate entries from your spreadsheet.

Use Conditional Formatting to Highlight Duplicate Rows in Google Sheets

Highlight duplicated rows in Google Sheets using conditional formatting

The steps you need to highlight duplicate rows in Google sheets are identical to what we’ve just discussed above.

The only difference is in the formula used which I will explain soon.

Let’s see how we can use conditional formatting to highlight duplicate rows in the below steps.

Step 1: Select the Cells you want to scan for duplicates

Using your mouse pointer, click and drag over all the cells you think contain the duplicates.

Unlike the previous example where we highlighted only one column which contains the duplicates, here you should highlight all the cells with data since the duplicates are rows.

In this example, we’ll select all the cells under the Date column (Range A2:D17).

Step 2: Go to Format > Conditional formatting

Move your cursor to your Google sheets Menu section and click on the Format menu.

This action will reveal all the items under the Format menu including Conditional formatting. Simply click on it to open the Conditional Formatting pane on the right.

Step 3: Under the Format rules, click on the drop-down and select “Custom formula is” from the list.

As stated in the previous example, you can only highlight duplicates in Google Sheets using formula-based conditional formatting.

To do so, first, under the Format rules, click on the drop-down and select “Custom formula is” from the list.

Step 4: Enter the formula: =COUNTIF(range,top_cell)>1

You’ll then be given a text box to enter your custom formula, kindly enter =COUNTIF(range,top_cell)>1.

The “range” is the selected range of the first step. The “top_cell” is the first, leftmost cell of the current selection. For this example, the “range” is $A$2:$D$17. Hence the formula becomes =COUNTIF($A$2:$D$17,$A2)>1

Thus, adjust the range and top cell according to your data.

In case you want to apply this to your own data, simply change the $A$2:$D$17 part of the formula to the range where you have the duplicates.

Step 4: Set the Formatting

Here, there are a lot of formatting styles you can apply to the duplicates.

You can do that by using the font controls under the Formatting style.

There are several preset formatting you can choose from (including green background, yellow background, red background, green text, yellow text, and red text)

Click on the Default button under the Formatting style to reveal the presets.

To define your own format settings, do so using the font controls.

Aside from these restrictions, the font controls are the same as those found in the full-fledged format menu which you can use to highlight duplicate cells in Google sheets.

Step 5: Click Done

Google Sheets evaluates the condition and adjusts the formatting as needed as soon as you click the done button.

When you open your spreadsheet or change the value in one of the conditional cells, Google Sheets evaluates the condition and adds or removes formatting as needed.

Now Mr. GSheets’ duplicate rows are apparent now in the screenshot below. The duplicate rows are highlighted as a result of the settings we did in the above steps.

Highlight Current and Future Duplicates in Google Sheets

This example consists of a spreadsheet with invoice numbers and invoice amounts.

We are going to highlight the current duplicate values as well as duplicate values to be entered in the future.

Follow the following instructions to do so with conditional formatting in Google Sheets.

Step 1: Select Column A and B

Using your mouse pointer, click and drag over both columns A and B to select them, i.e., the Invoice Number and the Invoice Amount columns.

If you select only the invoice number column, Google Sheets will highlight only duplicate invoice numbers and not the entire row. So select both columns.

Step 2: Go to Format > Conditional formatting

Move your cursor to your Google sheets Menu area and click on the Format menu.

This action will reveal all the items under the Format menu including Conditional formatting. Simply click on it to open the Conditional Formatting pane on the right.

Step 3: Under the Format rules, click on the drop-down and select “Custom formula is” from the list.

As stated in the previous example, you can only highlight duplicates in Google Sheets using formula-based conditional formatting.

To do so, first, under the Format rules, click on the drop-down and select “Custom formula is” from the list.

Step 4: Enter the formula: =COUNTIF(range,top_cell)>1

You’ll then be given a text box to enter your custom formula, kindly enter =COUNTIF(range,top_cell)>1.

The “range” is the selected range of the first step. The “top_cell” is the first, leftmost cell of the current selection. For this example, the “range” is $A$1:$B$997. Hence the formula becomes =COUNTIF($A$1:$B$997,$A1)>1

Thus, adjust the range and top cell according to your data.

In case you want to apply this to your own data, simply change the $A$1:$B$997 part of the formula to the range where you have the duplicates.

Step 4: Set the Formatting

You can apply a variety of formatting styles to the duplicates here.

You can do this by navigating to the Font controls in the Formatting style.

You can select from a number of predefined formatting options (including green background, yellow background, red background, green text, yellow text, and red text)

To access the presets, click the Default button under the Formatting style.

Use the font controls to define your own format settings.

Google Sheets does not support the application of specific formatting when it comes to conditional formatting. You cannot, for example, conditionally change the font size, but you can conditionally change the font settings of duplicate cells such as Bold, Italic, Underline, Strikethrough, and Font color.

Aside from these limitations, the font controls are identical to those found in the full-fledged format menu, which can be used to highlight duplicate cells in Google Sheets.

Step 5: Click Done

When you click the done button, Google Sheets evaluates the condition and adjusts the formatting as needed.

Google Sheets evaluates the condition and adds or removes formatting whenever a duplicate is entered or removed.

Conclusion

That is all there is to it. A step-by-step guide for using conditional formatting to highlight and remove duplicates in Google Sheets.

If you have any concerns about this topic, please express them in the comments section.

Other Google Sheets Resources You May Find Useful

Joshua

Thursday 9th of March 2023

Can we do a formula in which we can highlight the cell in the specified column if there are duplicate values in cells in different columns? For example, cells A2 & B3 have the same value which means there is a duplicate value, but in diff columns.

Abarika Abdulai

Yes Joshua. Using the same concept explained in this post, you can write a conditional formatting formula to highlight cells in different columns.