Finding Duplicates Using Sheets

• Introduction
• Method

How to identify duplicate values or rows in Google Sheets.

Introduction

Highlighting duplicates in Google Sheets is quite straightforward if you use custom conditional formatting. You can check for duplicates based on a single value, or an entire row.

Method

To check for duplicates, select the column you wish to de-duplicate (we’re assuming it’s column A in this example) and click ‘Format -> Conditional formatting…’. Then select ‘Custom formula’ from the drop-down menu and type the following formula (including the equals sign).

COUNTIF(A:A,A1)>1

Then select how you want to highlight duplicates (e.g. red background) and click ‘Done’. You can also highlight duplicate rows by using the concatenate function to generate a ‘key’ for each row, using a formula such as this (assuming your row is three columns wide). The key will be unique for each combination of cells, so if the key matches another key, you have a duplicate row.

CONCATENATE(A1:C1)

This can be used on data such as the following table, then perform the conditional format on the key column to identify duplicate rows.

Alternatively, you can avoid having to have a key column by using the concatenation operator and sumproduct function in your custom conditional format formula (applying the conditional format to both the first and family name columns), like this:

SUMPRODUCT(IF($A:$A&$C:$C=$A1&$C1,1,0))>1

Case Sensitivity

By default, these duplicate matching formulas are case-insensitive. To handle case-sensitive row duplicate matching, make use of the the exact function as follows:

SUMPRODUCT((--EXACT($A1&$C1,$A:$A&$C:$C)))>1

These last two formulas will also work with an arbitary number of columns to match on, for example we can expand the match to three as follows:

SUMPRODUCT((--EXACT($A1&$B1&$C1,$A:$A&$B:$B&$C:$C)))>1