How to Highlight Duplicates In Google Sheets (Easy Steps)

by

Engage with Video – Identifying Duplicates in Google Sheets

Google Sheets offers several ways to spot duplicates, with one of the most straightforward being the application of a custom formula in the conditional formatting menu:

=COUNTIF($A$2:$A$15,A2)>1

No need to fret if it appears confusing, we’re here to help. We’ll provide a comprehensive guide demonstrating this approach in-depth, along with a few alternative methods. Keep reading to know more.

Recognizing Duplicates in Google Sheets

  1. Choose the range.
  2. Proceed to Format > Conditional formatting
  3. Click on +Add a rule
  4. Adjust “Format cells if…” to “Custom formula is” and type the duplicate verification formula =COUNTIF($A$2:$A$15,A2)>1”
  5. Click on Done.
  6. Download Our Sample Spreadsheet

Our Sample Sheet can be copied to help you follow this tutorial more smoothly. It comes with 2 sheet tabs for the dual examples employed in this guide.

Mastering Google Sheets Swiftly

For beginners, highlighting duplicates via conditional formatting can seem a bit daunting. If you’re facing difficulty, we suggest enrolling in a complete Google Sheets tutorial to refine your skills.

Identifying Duplicates in Google Sheets in a Single Column

Let’s assume you have a dataset as given below, and you desire to highlight all the duplicate data in column A.

To do so using conditional formatting, execute these steps:

  1. Pick the range. For this example, we’ll use A2:A15 (image 1, box).
  2. Navigate to Format >Conditional formatting.
  3. In the conditional formatting pane that opens, select the “+ Add another rule” option. If it’s already open, you’ll find this option beneath the existing rules.
  4. Adjust “Format cells if…” to “Custom formula is” and type in the duplicate check formula “=COUNTIF(Range,Criteria)>1” In this case, the formula =COUNTIF($A$2:$A$15,A2)>1 is used.
  5. Click on Done.

The duplicates in our grocery list are now being highlighted by Google Sheets.

Notably, this method highlights every instance of the duplicate cell. If an item recurs twice or more, all cells containing this item/text will be highlighted.

The COUNTIF function used in conditional formatting calculates the number of times a cell text string appears in the list. If it’s more than 1, the formula yields TRUE, and those cells get highlighted.

Note: The “Formatting style” section can be used to change the highlight color and font style. This is particularly useful when you need to highlight more than one fill color simultaneously.

Identifying Duplicates in Multiple Google Sheets Columns

With larger spreadsheets that have multiple columns, it’s often beneficial to highlight the entire column when a duplicate is found, instead of just the cell.

This assists Google Sheets in detecting duplicates when the duplicated cell is off-screen.

The process changes in two ways:

  • The range needs to cover all rows (A2:A15 is changed to A2:F15).
  • The criteria section should use an absolute value for the column (A2 is altered to $A2).

Let’s use our earlier grocery list, but now we’ve added the location of each product in the store as a new column (as shown below).

Follow these steps to highlight the entire row for duplicate cells in one column:

  1. Select the range of all columns and rows you wish to highlight. In the example, select A2:B15 instead of A2:A15
  2. Open the “Conditional Formatting” feature and verify the new range (image 2, top box).
  3. In the “Conditional Formatting” tab, adjust the “Format rules” to “Custom formula is…”
  4. Enter the duplicate-detection formula with the criteria adjusted for an absolute column. This implies adding a dollar sign “$” before
  5. “A2” in our case. So “=COUNTIF($A$2:$A$15,A2)>1” is now “=COUNTIF($A$2:$A$15,$A2)>1”.
  6. Click “Done” to apply the new formatting (image 2, bottom box).

We’ve now highlighted the entire row range for columns containing duplicates.

Highlighting the Entire Row if Duplicates Are in One Column

Let’s assume we want to highlight a complete row if there are duplicates in column B. In our example below, we could apply the following formula to the conditional formatting menu to achieve this:

=COUNTIF($B$2:$B$7,$B2)>1

This shows that the conditional formatting should highlight the entire row if Google Sheets indicates duplicates in the range $B$2:$B$7.

Highlighting Duplicates But Ignoring the First Instance

As previously noted, using conditional formatting to highlight duplicates in Google Sheets will highlight every instance of duplicate cells.

But what if you want to only highlight the duplicate instances (excluding the first time it appears in the list)?

You can modify the formula so Google Sheets will only highlight the second or subsequent instance of a duplicate by making a slight alteration to the formula.

Suppose you have the dataset as aren’t blank.

Can You Delete Duplicates Instead of Highlighting Them?

Indeed, Google Sheets has a built-in function to remove duplicate data without having to highlight them first. Here’s how you do it:

1. Select the data range you wish to check.
2. Open the “Data” menu.
3. Navigate to Data Cleanup > “Remove Duplicates”.
4. In the pop-up window, if your data has a header row, check “Data has header row”.
5. Select the columns you wish to scrutinize for duplicates.
6. Finally, click “Remove duplicates” to apply the changes.

Your sheet now only consists of rows without any duplicates from your selection.

As such, using conditional formatting to highlight duplicates in Google Sheets is also helpful to identify any redundant data you may wish to manually remove at your convenience.

What If You Only Want to Highlight Subsequent Duplicates – 2nd, 3rd, etc. Instances?

Conditional formatting allows you to spotlight only specific instances of repetition. For example, you might want to highlight duplicates from the second occurrence onward, without marking the initial instance. To achieve this:

1. Select your data range.
2. Proceed to Format > Conditional formatting.
3. Click +Add a rule.
4. Set “Format cells if…” to “Custom formula is” and input the duplicate check formula =COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2),$A2&$B2)>1.
5. Choose a color scheme, then click Done.

This formula will then flag duplicates starting from the second instances onwards.

Is It Possible to Highlight Duplicates with Additional Conditions?

Google Sheets enables conditional highlighting with extra conditions. You can configure it to mark only those rows with duplicate data across several columns or duplicates with specific values.

You will need to employ the “*” (and) operator to enforce both conditions. For instance, a formula to locate duplicates in Google Sheets with added criteria could look like this:

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

Suppose we want to highlight duplicate products in our grocery list that fall under the category of fruits or vegetables, regardless of their occurrence in other categories. Our new formula would appear as follows:

=(COUNTIF($C$2:$C$15,$C2)>1)*(COUNTIF($A$2:$B$15,$C2)>0)

Applying this formula will highlight all duplicate fruits and vegetables without flagging other duplicate products from different sections.

Some Useful Tips When Highlighting Duplicates in Google Sheets

1. How to Modify, Add or Delete Conditional Formatting Rules

To edit or remove conditional formatting rules:

– Select any cell with the existing formatting.
– Navigate to Format > Conditional formatting.
– Click on the rule in the menu to edit it or select the trash icon to delete it.

2. Creating a Unique Cells List

If you wish to find out the number of duplicates quickly, creating a list of unique values could be more efficient. The Unique function can help with this.

You can use it by typing “=unique(range start: range end)” in the cell where you want your list to start.

3. Trimming Whitespace

Extraneous spaces at the beginning or end of your data can interfere with the identification of duplicates. You can rectify this by selecting the data to trim, opening the “Data” drop menu, and choosing “Trim whitespace” from the list.

FAQs

Can Google Sheets Highlight Duplicates?

Yes, Google Sheets can detect and highlight duplicates by utilizing a COUNTIF formula within the Conditional formatting feature.

What Is the Formula to Highlight Duplicates in Google Sheets?

Combining the COUNTIF function with the Conditional formatting feature will enable you to highlight duplicates.

How Do I Find Duplicates in Two Columns in Google Sheets?

To spot duplicates across two columns, you need to use absolute cell references in the Custom formula inside the Conditional formatting feature.

How Do I Count Only Duplicates in Google Sheets?

You can count duplicates in Google Sheets by crafting a custom formula within the Conditional formatting rule.

Can I Compare Different Google Spreadsheets for Duplicates?

To identify duplicates across sheets, a more complex formula like “=AND(A2=INDIRECT(“Sheet1!A2:A”),B2=INDIRECT(“Sheet1!B2:B”), C2=INDIRECT(“Sheet1!C2:C”))” will be necessary.

Can I Exclude Blanks for Duplicate Highlights in the Same Row?

Yes, by combining the AND function and COUNTIFS, you can prevent blank cells from being highlighted. A formula like “=AND(COUNTIFS(B:B,B1)>1,B1″”)” can help with this.

 

Leave a Comment