How to Highlight Duplicates in Google Sheets?

Google Sheets is a powerful tool for organizing and analyzing data. However, when working with large sets of data, it can be difficult to identify duplicates or recurring values. Highlighting duplicates can help you quickly identify patterns in your data and make informed decisions based on that information.

In this tutorial, we will show you how to use Google Sheets’ conditional formatting feature to highlight duplicates in your data. With just a few simple steps, you can set up a rule that will automatically highlight duplicate values in your spreadsheet, making it easier to identify and analyze the data. 

How to Highlight Duplicates in Google Sheet

1. Highlighting Duplicates in a Single Column

Just consider that you have simple data with names column with some duplicated entries. 

We will try to highlight duplicates in this column.

To highlight duplicates, just follow these steps:

1. Select the range of data you want to highlight (excluding headers).

The range of data is selected. Now we will proceed to the next step.

2. Click the format option and from the dropdown click Conditional Formatting.

It will open a conditional formatting window on the right side.

3. Make sure the range of which we need to highlight duplicates is correct. If not then adjust it accordingly.

4. Click on Format Rules and from the dropdown menu go to the end and click “Custom Formula is”.

It will open another dialogue box like this

We will need to write a custom formula to highlight duplicates.

5. In the field below, enter the following formula to highlight duplicates:

In this formula, “$A$2:$A” represents the range of cells you want to check for duplicates, and “A2” represents the first cell in the range you have selected. This formula checks if the value in the current cell (A2) appears more than once in the selected range of cells ($A$2:$A). If it does, the formula returns a value greater than 1, and the cell will be highlighted.

6. Change Formatting Styles and click Done.

It will highlight the duplicated values.

It shows that only Smith was the unique name. All the other ones were repeating more than once.

ADDITIONAL TIP: 

To highlight only the second duplicated entry, change the formula to

Now the second duplicated entries are being highlighted as shown.

2. Highlighting Duplicates in Multiple Columns

What if you want to check for duplicated entries in multiple columns? Don’t worry we will guide you on how to do it step by step in this tutorial. Suppose you got the names data in multiple columns which look like this.

You can clearly see that the duplication is there but in other columns. To highlight duplicates across the columns, just follow these steps.

1. Select the range of data you want to highlight (excluding headers).

The range is selected to highlight the duplicates.

2. Click the format option and from the dropdown click Conditional Formatting.

It will open a conditional formatting toolbar like this.

3. Enter a custom formula like this.

IMPORTANT TIP: 

It is important to note that the range used for highlighting duplicates in Google Sheets, $A$2:$C$10, contains a dollar sign before the column alphabet and the row number. This is crucial because the dollar sign signifies absolute referencing, which ensures that the range remains constant while the conditional formatting checks for the count of the name in each cell. Without absolute referencing, the range would change as the formula is applied to different cells, leading to incorrect counts and highlighting of non-duplicate cells. Therefore, it’s essential to use absolute referencing to maintain consistency and accuracy when highlighting duplicates in your spreadsheet.

4. Apply the Formatting style and click Done.

It highlights the duplicates across the columns. 

3. Highlighting the exactly duplicated rows

What if someone wants to check whether the entire row is duplicated or not? We will show step-by-step how to do so.

Consider you are dealing with this simple dataset.

To find the entire duplicated row, follow these steps:

1. Select the range of the data you want to analyze.

2. Open the Conditional formatting tab.

3. Add this formula.

To identify duplicates in Google Sheets, an array formula is used to concatenate the values in each row of the range being checked. The formula, ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10), combines the values in columns A, B, and C for each row in the range.

This concatenated string is then used as the criterion for the COUNTIF function. By using the formula $A2&$B2&$C2, each row in the range is checked against its corresponding concatenated string. This allows the function to determine how many times the combined string appears in the array of strings created by the ARRAYFORMULA.

If the COUNTIF function identifies more than one occurrence of the combined string, the row is considered a duplicate and will be highlighted accordingly. By using this method, all records that contain duplicate values in the specified range will be highlighted, making it easy to identify and analyze patterns in your data.

4. Click Done and it will highlight the duplicates.

In conclusion, highlighting duplicates in Google Sheets is an important step in analyzing data, and with the conditional formatting feature, it can be done quickly and easily. By following the steps outlined in this tutorial, you can learn how to use Google Sheets’ conditional formatting feature to highlight duplicates in your data.