How to Highlight Duplicates in Google Sheets

Last Modified: February 20, 2024 - 9 min read

Hannah Recker

The following is a comprehensive guide on how to highlight duplicates in Google Sheets.

Duplicate data compromises the quality and accuracy of your performance metrics, sales dashboards, app kpis, decision-making, and other mission-critical efforts.

That’s why you need an efficient data cleanup method to highlight and remove duplicates in Google Sheets.

In this blog, we’ll provide step-by-step walkthrough on how to find duplicate data in Google Sheets and examples for all the different ways you can highlight duplicates in Google Sheets. Also, watch our video tutorial below for a complete guide on how to highlight duplicates in Google Sheets.

Video Tutorial: How to Highlight Duplicates in Google Sheets

Prerequisites for Highlighting Duplicates in Google Sheets

Before attempting to highlight duplicates in Google Sheets, try to ensure the following:

  • Eliminate other conditional formatting rules currently applied to the cell you’re targeting
  • Make sure you don’t have missing spaces in your searches
  • Do not select headers when highlighting duplicates with Array Formulas
  • Avoid highlighting specified duplicate values if necessary. Select the cells with the conditional formatting rule. Then click the trash icon under Format > Conditional formatting.

Here’s another pitfall to watch out for. Google Sheets can fail to highlight duplicates due to extra space characters.

This is when there are extra spaces — trailing or leading spaces — around the text in one cell but not in another. These extra spaces within the cells can result in missed duplicates, since Sheets searches for an exact match.

You can remove extra spaces in your cells by using the TRIM or CLEAN functions in Google Sheets.

By dealing with these potential issues up front, you can save yourself some headaches down the road.

5 Ways to Highlight Duplicates in Google Sheets

In Google Sheets, you can use custom formulas paired with conditional formatting to highlight duplicates.

Before we start, let’s pull a sample Salesforce dataset for our examples into Google Sheets by using Coefficient. To learn how to import real-time data from Salesforce to Google Sheets in one click with Coefficient sidebar, read our Salesforce to Google Sheets blog.

salesforce duplicate data

We’ll use this Salesforce dataset to perform the step-by-step walkthroughs below.

1. Highlight duplicates in a single column

Highlighting duplicates in a single column is very straightforward. Suppose you want to highlight the duplicate entries in the Deal Owner First Name column.

delete duplicates google sheets

First, select the names in the dataset (excluding the headers).

find duplicates in column google sheets

Click Format in the top menu and select Conditional Formatting from the dropdown.

conditional formatting find duplicates

Click Add another rule on the Conditional format rules side panel.

conditional format rules duplicates google sheets

Under Format rules, click Format cells if… and select Custom formula is from the drop down menu. Now, let’s write our first Google Sheet duplicate formula.

custom formula highlight duplicates

Enter the COUNTIF formula under the Format cells if… field. Check out our Ultimate Guide to COUNTIF in Google Sheets blog if you want to learn more about this function.


countif find duplicates google sheets

Then specify the formatting for the duplicate cells — in other words, how they will appear on the spreadsheet — by setting the Formatting style options.

duplicates conditional formatting

Click Done. You will see the duplicate names highlighted in the column range in your set color and format.

delete duplicates google sheets

Conditional formatting is also dynamic. It means you can change the data in any cell within your range, and the formatting will update automatically.

For instance, if you remove one of the two Suzys from the column range, Google Sheets will automatically stop highlighting Suzy since the name is no longer duplicated.

You can remove the highlight on a duplicate value by selecting the cell (or cells) and deleting the conditional formatting rule.

custom formula duplicates

You can also clear the formatting without deleting the rule. Select your cell or range of cells, click Format on the top menu, and choose Clear formatting from the dropdown.

clear formatting duplicates Google Sheets

2. Highlight duplicates in multiple columns

You can also use conditional formatting to find duplicates in multiple Google spreadsheet columns

For example, let’s say you have three columns, all containing duplicate data.

find duplicates multiple columns google sheets

Follow the same steps from the previous example. Select the data range (excluding headers) and click Format > Conditional formatting.

highlight duplicates columns google sheets

Select Add another rule on the Conditional format rules side panel.

Double check the range of cells. Make sure it includes all the columns you want to find duplicates in.

find duplicate data in columns

Click the Format cells if… option and select Custom formula is.

Google Sheets formula duplicate data

Enter the following formula in the designated field.


COUNTIF function find duplicates

Set your preference in the formatting style section for highlighting duplicate data and click Done. The duplicate names that appear across these three columns are now highlighted.

highlight duplicates in Google Sheets

3. Highlight duplicate rows

It’s a bit trickier to highlight duplicate rows. For instance, in the Salesforce dataset below, rows six and eight are duplicated. All the corresponding cells contain the same data.

highlight duplicate rows in google sheets

To find duplicate rows, you should execute the steps from the previous example, but with modifications to the Custom formula is step:

  • Select your data range, but not the header row
  • Open the Conditional formatting option and the Conditional format rules side panel from the Format top menu
  • Click Add another rule
  • Select Custom formula is from the Format cells if… dropdown list
  • Input the following formula in the Value or formula field: =COUNTIF(ARRAYFORMULA($A$3:$A$18&$B$3:$B$18&$C$3:$C$18),$A3&$B3&$C3)>1
  • Choose your preferred formatting style and color for your highlighted duplicates
  • Click Done

You should see rows six and eight highlighted, since these contain repeating cell values.

highlight duplicate records google sheets

A quick explanation of this method:

The conditional formatting formula in this example works the same as in the others. The formula highlights duplicate cells within a column. However, the formula combines all the rows’ content and makes a single string for each row.

This part of the formula: ARRAYFORMULA($A$3:$A$18&$B$3:$B$18&$C$3:$C$18)  creates an array of strings where the cells’ content within a row are combined. Concatenation is performed using the ampersand.

The COUNTIF formula uses the array. The condition is a concatenated string with all the values in a row, appearing like this:


spreadsheet ai
Free AI-Powered Tools Right Within Your Spreadsheet

Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.

The array is essentially a column-type construct. The COUNTIF function checks the number of times the combined string repeats in the array.

The end result: the formula highlights all the duplicate rows from the unique rows in the specified cell range.

4. Highlight duplicates using added criteria

You can use added criteria in Google Sheets to highlight duplicate data. For example, you can configure your Sheet to only highlight duplicates for specific values.

Your syntax should include the star operator (“*”) to tell the COUNTIF function to use both criteria. The complete syntax will look like this:

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

Let’s look again at the dataset from the previous example. There are two employees named Jacob with different Deal IDs.

highlight duplicates added criteria google sheets

In this case, we will highlight the duplicate employees, and also add a second condition for the Deal ID.

First, follow the same steps as our previous examples. When you get to the Conditional format rules side panel, input this formula:


Then you’ll need to this formula to account for the Deal ID:

    • Place the “*” operator after the initial formula
    • Add the second condition to your (COUNTIF(Range,Criteria)>1) syntax
    • Your whole complete formula should look like this: =(COUNTIF($A$3:$C$18,$A3)>1)*(COUNTIF($A$3:$C$18,$C3)>1)
* duplicates google sheets

Of course, you can keep fine tuning your formula for the conditional format rules:

    • Add a third criteria
    • Place another argument after the criteria (e.g., <5 or >0)
    • Insert several other “*” conditions

5. Highlight duplicates using the UNIQUE function

A UNIQUE formula finds all the unique values within a cell range. The function works best with a smaller dataset.

The syntax is simple: =UNIQUE(Range)

Let’s apply this function to the same dataset, so we can find unique values.

Select an empty cell. Type in =UNIQUE and include the data range you want to check for unique data:


UNIQUE function google sheets

The formula will return the unique values within A3:A18.

UNIQUE function highlight duplicates google sheets

UNIQUE eliminates complicated formula configurations and provides you with a clean dataset without duplicates. However, in more advanced duplicate use cases, this function alone can run into limitations.

Use AI to Write Formulas for Conditional Formatting

As you can see, there are quite a few alternatives to highlight duplicates within Google Sheets. You may be a little overwhelmed, but you don’t have to go at it alone.

You can use Coefficient’s free GPT Copilot to automatically generate any spreadsheet formula, pivot, or chart, including your conditional formatting formulas. To use GPT Copilot, you need to install Coefficient. The install process takes less than a minute.

First, you’ll get started for free by simply submitted your email.

Follow along with the prompts to install. Once the installation is finished, return to Extensions on the Google Sheets menu. Coefficient will be available as an add-on.

Now launch the app. Coefficient will run on the sidebar of your Google Sheet. Select GPT Copilot on the Coefficient sidebar.

 use the Coefficient GPT Copilot’s Formula builder to calculate the number of comments per post

Then click Formula Builder.

select formula builder

Type a description of a formula into the text box. To recreate one of our conditional formatting formulas above, let’s highlight deals if the deal owner’s name and deal ID are both the same on multiple rows. Let’s type: Write a conditional formatting formula to highlight duplicate rows if both of the values in column A AND C are the same in both rows in sheet11.

Then press ‘Build’. Formula Builder will automatically generate the formula from the first example.

Simply open up Conditional Formatting in the Format dropdown, add a new rule, select Custom Formula and paste your conditional formatting formula.

Highlight Duplicates in Google Sheets to Improve Data Quality

Your spreadsheet data impacts your metrics, reporting, analysis, and decision-making at every level. With Google Sheets, it’s easy to highlight and remove duplicates, improving data quality for your business operations. The methods outlined in this blog are simple yet effective ways to ensure superior data.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 350,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
350,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies