How to Use Formula to Highlight Duplicates in Google Sheets

When a dataset has duplicate quantities, you may wish to highlight those duplicates to visualize the dataset. You may wish to highlight those duplicates in various ways each time. In this post, we’ll show you how to use a formula to highlight duplicates in Google Sheets in six different ways.


6 Examples of Using Formula to Highlight Duplicates in Google Sheets

The dataset below will be used to demonstrate examples of how to use a formula to highlight duplicates in Google Sheets. This dataset contains the product id, product name, and product sales of a shop on a particular day. We will highlight the duplicates of this dataset in Google Sheets. Basically, we will use a formula in Conditional Formatting to highlight duplicates in Google Sheets.

Highlight Duplicates in in Google Sheets


1. Highlight Duplicates in a Single Column

One of the most popular Google Sheets features is highlighting duplicates in a single column. We can quickly identify duplicates in a single column using the Conditional Formatting tools in Google Sheets. We will use the COUNTIF function in it to highlight the duplicates.

Steps:

  • Select the data range from which you want to highlight the duplicates. We chose Cell C5:C15.

Highlight Duplicates in a Single Column in Google Sheets

  • Go to the Format bar from the top menu bar and select Conditional formatting.

  • The Conditional format rules dialog box will open. Now click on the drop-down icon bar in the Format cells if…. option.

  • Choose Custom formula is from the Format cells if…. option.

  • Type the formula in the Formula bar option and click Done.
=COUNTIF($C$5:$C$15,$C5)>1

Using COUNIF Formula to highlight duplicates in Google Sheets

  • All the duplicates in the Product Name column are highlighted.

Read More: How to Merge Columns in Google Sheets


2. Insert Formula to Highlight Duplicates in Multiple Columns

Your dataset in Google Sheets can have duplicate values in several columns. Importantly, Google Sheets allows you to use a single formula to indicate duplicates across multiple columns. We will use the same COUNTIF function in a different way to highlight the duplicates.

Steps:

  • Choose the data range where you want to highlight the duplicates. We chose Cell B5:D15. Select Add another rule from the Conditional format rules dialog box.

Highlight Duplicates in Multiple Columns in Google Sheets

  • As mentioned before, click on the drop-down icon bar in the Format cells if…. option and choose Custom formula is.

  • Now type the formula in the Formula bar option and click Done.
=COUNTIF($B$5:$D$15,B5)>1

  • All the duplicates from multiple columns are now highlighted.

Read More: How to Highlight Duplicates for Multiple Columns in Google Sheets


3. Ignore the First Instance to Highlight Duplicates

In Google Sheets, you might want to ignore the first duplicate and highlight the others as you might wish to get rid of the others later. We will use the same COUNTIF function again to highlight the duplicates.

Steps:

  • Choose the data range to highlight duplicates. We chose Cell B5:D15. From the Conditional format rules dialog box, select Add another rule.

Highlight Duplicates ignoring the first instance in Google Sheets

  • After completing the steps previously mentioned, enter the formula below in the Formula bar option and click Done.
=COUNTIF($B$5:$B5,$B5)>1

  • All the duplicates ignoring the first instance are now highlighted.

Similar Readings


4. Highlight the Entire Row If Duplicates Are in One Column

If you have duplicates in a particular column, you may occasionally wish to highlight the entire row to visualize the dataset in Google Sheets. Here we’ll highlight the entire row based on Column C and again we’ll use the COUNTIF function to highlight the duplicates.

Steps:

  • To highlight duplicates, choose the data range. We chose Cell B5:D15. Select Add another rule from the Conditional format rules dialog box.

Highlight entire row if duplicates are in one column in Google Sheets

  • After completing the aforementioned steps, type the following formula into the Formula bar option, then click Done.
=COUNTIF($C$5:$C15,$C5)>1

  • All rows that contain duplicates in the Product Name column are now highlighted.

Read More: How to Sum Multiple Rows in Google Sheets (3 Ways)

 


5. Apply Formula to Highlight Complete Row with Duplicates

In Google Sheets, sometimes you may wish to highlight the row if every column in the dataset is an exact match. To accomplish this, before utilizing the COUNTIF function, we need to use the ARRAYFORMULA function to consolidate the data into a single string.

Steps:

  • To highlight duplicates, select the data range. We selected Cell B5:D15. Select Add another rule from the Conditional format rules dialog box.

Highlight Complete Row Duplicates in Google Sheets

  • After completing the previously mentioned steps, type the below formula into the Formula bar option, then click Done.
=COUNTIF(ARRAYFORMULA($B$5:$B$15&$C$5:$C$15&$D$5:$D$15),$B5&$C5&$D5)>1

Formula Breakdown:

  • ARRAYFORMULA($B$5:$B$15&$C$5:$C$15&$D$5:$D$15)

First, the ARRAYFORMULA function will generate an array of strings by combining the contents of each row’s cells.

  • COUNTIF(ARRAYFORMULA($B$5:$B$15&$C$5:$C$15&$D$5:$D$15),$B5&$C5&$D5)>1

Then, the COUNTIF function will use the ARRAYFORMULA as a range and it will count the number of times the combined string appears in the array. If it appears more than 1 time, it will highlight the combined string.

  • All of the complete row duplicates are now highlighted.

Read More: How to Sum Using ARRAYFORMULA in Google Sheets


6. Use Added Criteria to Highlight Duplicates

To highlight duplicates, Google Sheets offers added criteria feature by which you can highlight row duplicates across various columns or highlight only duplicates with specific criteria.

The star operator (“*“) is required in your syntax to instruct the COUNTIF function to consider both factors. The whole syntax will be as follows:

=(COUNTIF(Range,Criteria)>1) * (New Condition) )

For example, in our dataset, we want to highlight the entire row if any cell in the Sales column appears more than 3 times.

Steps:

  • Choose the data range to highlight duplicates. We chose Cell B5:D15. Select Add another rule from the Conditional format rules dialog box.

Use Added Criteria to Highlight Duplicates in Google Sheets

  • After completing the previously mentioned steps, type the below formula into the Formula bar option, then click Done.
=(COUNTIF($D$5:$D$15,$D5)>3)*(COUNTIF($B$5:$D$15,$D5)>0)

Formula Breakdown:

  • COUNTIF($D$5:$D$15,$D5)>3

It will count the number of times each cell repeats in Column D and if any cell appears more than 3 times, it will highlight those.

  • (COUNTIF($D$5:$D$15,$D5)>3)*(COUNTIF($B$5:$D$15,$D5)>0)

The second condition is added by the star (*) operator. COUNTIF($B$5:$D$15,$D5)>0 will highlight the entire row in the dataset.

  • All of the duplicates that appear more than 3 times in the Sales column are now highlighted.

Read More: Count Duplicates In Google Sheets (3 Ways)


Conclusion

We may want to highlight duplicate entries in different ways every time. In this article, we have shown you all possible ways of using formulas to highlight duplicates. You can follow any method based on your criteria and highlight duplicates. Besides, Google Sheets has a feature called added criteria by which you can highlight duplicates based on your specified criteria. Please feel free to leave any questions or suggestions in the comments section below. Visit our site Officewheel.com to explore more.


Related Articles

Sishir Roy

Sishir Roy

Hello, I'm Sishir Roy. I currently work for the SOFTEKO company at officewheel.com as a technical writer and content developer. I completed my BSc degree in civil engineering from BUET. I like using my talents as a creative problem-solver and thinker to come up with innovative solutions to problems. I have a great interest in the research and development of data science. Please follow my blog at Officewheel.com for more Google Sheets solutions.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo