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.

**Table of Contents**hide

**A Sample of Practice Spreadsheet**

You can download the free Google Sheet from here and practice independently.

**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.

### 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**.

- 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`

- All the duplicates in the
**Product Name**column are highlighted.

**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.

- 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.

**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**.

- 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.

**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.

- 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.

**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.

- 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.

**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.

- 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.

**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.