Highlight duplicates in Google Sheets: conditional formatting vs add-on

This blog post explores various methods for highlighting duplicate data in Google Sheets, ranging from using conditional formatting with different formulas to employing the "Remove Duplicates" add-on. Let's dive into the most common scenarios.

• Highlighting Duplicates in a Single Column
• Highlighting Duplicates Across Multiple Columns
• Highlighting Entire Rows Based on a Single Column Duplicate
• Highlighting Entire Duplicate Rows
• Highlighting All but the First Instance of Duplicates
• Formula-Free Highlighting with the "Remove Duplicates" Add-on
  • Scheduled Highlighting with the Add-on
• Video Tutorial: Highlighting Duplicates in Google Sheets

Highlighting Duplicates in a Single Column

This example shows highlighting duplicates within a single column:

We'll primarily use conditional formatting (explained in detail in this blog post).

1. Navigate to Format > Conditional formatting.
2. In the Conditional format rules window, ensure the Single color tab is selected.
3. Specify the cell range (e.g., A2:A10) in the Apply to range field.
4. Choose Custom formula from the Format rules dropdown.
5. Enter the formula: =COUNTIF($A$2:$A$10,$A2)>1 (The $ before A and 2 in $A$2 makes the range absolute, ensuring the entire column is checked for each cell.)
6. Select your desired highlight color.
7. Click Done.

The COUNTIF formula identifies and highlights cells appearing more than once.

Highlighting Duplicates Across Multiple Columns

To highlight duplicates across multiple columns (e.g., columns A, B, and C):

1. Set the Apply to range to A2:C10.
2. Use the custom formula: =COUNTIF($A$2:$C$10,A2)>1 (Note the removal of the $ before the 2 in A2, making it relative to the current cell.)
3. Choose a highlight color and click Done.

This COUNTIF counts occurrences across all three columns.

Highlighting Entire Rows Based on a Single Column Duplicate

If you want to highlight entire rows based on duplicates in a specific column (e.g., column B):

1. Apply the rule to A2:C10.
2. Use the formula: =COUNTIF($B$2:$B$10,$B2)>1

This highlights entire rows where column B contains duplicates.

Highlighting Entire Duplicate Rows

To highlight complete duplicate rows (identical across all columns):

Use this formula:

=COUNTIF(ArrayFormula($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1

This concatenates values from each row and counts occurrences of the combined strings.

Highlighting All but the First Instance of Duplicates

To highlight only subsequent instances of duplicate rows, modify the formula from the previous section:

=COUNTIF(ArrayFormula($A$2:$A2&$B$2:$B2&$C$2:$C2),$A2&$B2&$C2)>1

The change in the range makes each row compare only to the rows above it.

Formula-Free Highlighting with the "Remove Duplicates" Add-on

The "Remove Duplicates" add-on provides a user-friendly, formula-free approach. It guides you through selecting data, choosing highlighting options, and even scheduling automatic highlighting. See the images and video below for details.

Video Tutorial: Remove Duplicates Add-on Image 1: Add-on Data Selection Image 2: Add-on Settings Image 3: Add-on Column Selection Image 4: Add-on Highlighting Options

Scheduled Highlighting with the Add-on

The add-on allows you to save and schedule highlighting tasks.

Video Tutorial: Scheduled Highlighting Image 5: Saving Scenarios Image 6: Scheduling Runs Image 7: Log Sheet

Video Tutorial: Highlighting Duplicates in Google Sheets

Video Tutorial: Quick Methods

Practice Spreadsheet

Link to Practice Spreadsheet

Remember to replace bracketed placeholders ([link_to_...]) with actual links.

The above is the detailed content of Highlight duplicates in Google Sheets: conditional formatting vs add-on. For more information, please follow other related articles on the PHP Chinese website!