Highlight Duplicates In Google Sheets With Conditional Formatting

Conditional formatting is a powerful tool in Google Sheets that allows you to highlight cells based on specific conditions. One common use case is to highlight duplicate values in a dataset. In this article, we will explore how to use conditional formatting to highlight duplicates in Google Sheets.

Why Highlight Duplicates?

Highlighting duplicates is essential in data analysis as it helps to identify and remove duplicate entries, which can affect the accuracy of your analysis. Duplicate values can also lead to incorrect calculations and insights. By highlighting duplicates, you can quickly identify and address the issue, ensuring that your data is clean and reliable.

Using Conditional Formatting to Highlight Duplicates

To highlight duplicates in Google Sheets, you can use the COUNTIF function in combination with conditional formatting. Here's a step-by-step guide:

1. Select the range of cells that you want to check for duplicates.
2. Go to the "Format" tab in the top menu and select "Conditional formatting."
3. In the "Format cells if" dropdown menu, select "Custom formula is."
4. Enter the following formula: =COUNTIF(A:A, A2)>1 (assuming your data is in column A and you want to check for duplicates in cell A2).
5. Click on the "Format" button and select the formatting style you want to apply to the duplicates (e.g., yellow background).
6. Click "Done."

How the Formula Works

The COUNTIF function counts the number of cells in the range A:A that match the value in cell A2. If the count is greater than 1, it means that the value in cell A2 is a duplicate. The formula returns TRUE if the value is a duplicate and FALSE otherwise. The conditional formatting rule applies the formatting style to cells that return TRUE.

Example

Suppose you have a dataset with names and ages, and you want to highlight duplicate names.

Name	Age
John	25
Jane	30
John	25
Joe	35
Jane	30

To highlight duplicate names, select the range A2:A6 (assuming the data starts in cell A2). Enter the formula =COUNTIF(A:A, A2)>1 in the conditional formatting rule. Click "Done" to apply the formatting.

The resulting sheet will highlight the duplicate names:

Name	Age
John	25
Jane	30
John	25
Joe	35
Jane	30

Tips and Variations

• To highlight duplicates in multiple columns, modify the formula to =COUNTIFS(A:A, A2, B:B, B2)>1 (assuming you want to check for duplicates in columns A and B).
• To highlight duplicates in a specific range, modify the formula to =COUNTIF($A$2:$A$100, A2)>1 (assuming you want to check for duplicates in the range A2:A100).
• To highlight duplicates in a table, select the entire table and use the formula =COUNTIF($A:$A, A2)>1 (assuming the table has headers in row 1).

Gallery of Duplicate Values

Duplicate Values in Google Sheets

Conclusion

Highlighting duplicates in Google Sheets is a crucial step in data analysis. By using conditional formatting with the COUNTIF function, you can quickly identify and highlight duplicate values in your dataset. This helps to ensure that your data is clean and reliable, making it easier to draw accurate insights and conclusions. Try using the techniques outlined in this article to highlight duplicates in your own Google Sheets data.