How to flag duplicates in Google Sheets [Step-by-Step]

Duplicate data is the bane of spreadsheet solutions, especially at scale. Given the volume and variety of data that teams now enter, it is possible to duplicate data in tools such as Google Sheets it can be relevant and necessary, or it can be a frustrating distraction from the primary purpose of the spreadsheet effort.

A potential problem raises a good question: How do you flag duplicates in Google Sheets?

We’ve given you a step-by-step guide on how to flag duplicates in Google Sheets, complete with images, to ensure you’re on the right track when it comes to deduplicating your data.

Flagging duplicate data in Google Sheets

Google Sheets is a free, cloud-based alternative to proprietary spreadsheet programs and—unsurprisingly, since we’re dealing with Google—it offers a ton of great features to simplify data entry, formatting, and calculations.

Google Sheets has all the familiar features: File, Edit, View, Format, Data, Tools, etc. and facilitate quick data entry, addition formulas for calculations and discover key ratios. What Sheets doesn’t have is an easy way to find and flag duplicates.

While other spreadsheet tools such as Excelhave built-in conditional formatting tools that can pinpoint duplicate data in your sheet, Google’s solution requires a little more manual effort.

Step by Step: How to Flag Duplicates in Google Sheets (with Pictures)

So how do you automatically flag duplicates in Google Sheets? Although there is no built-in tool for this purpose, you can use some built-in functions to flag duplicate data.

Here’s a step-by-step guide:

Step 1: Open your spreadsheet.

Step 2: Highlight the data you want to check.

Step 3: In the Format section, select Conditional Formatting.

Step 4: Select “Custom formula is”.

Step 5: Enter your custom duplicate check formula.

Step 6: Click “Done” to view the results.

Step 1: Open your spreadsheet.

First, go to Google Sheets and open the spreadsheet you want to check for duplicate data.

Step 2: Highlight the data you want to check.

Then left-click and drag the pointer over the data you want to check to highlight it.

Step 3: In the Format section, select Conditional Formatting.

Now go to “Format” in the top menu bar and select “Conditional Formatting”. You may get a notification saying that the cell is not empty – if so, click on it and you should see this:

Step 4: Select “Custom formula is”.

Next, we need to create a custom formula. In the “Format cells if” section, select the drop-down menu and scroll down to “Custom formula is”.

Step 5: Enter your custom duplicate check formula.

To find duplicate data, we need to enter a custom duplicate check formula that looks like this for our data column:

=COUNTIF(A:A,A1)>1

This formula looks for any text string that appears more than once in our selected dataset and will highlight it in green by default. If you prefer a different color, click the small color pot icon in the design style bar and select the color you want.

Step 6: Click “Done” to view the results.

And voilà – we’ve exposed duplicate data in Google Sheets.

How to mark duplicates in multiple rows and columns

If you have a larger data set to check, it is also possible to flag duplicate data in multiple columns or rows.

This starts the same way as the duplicate checking process above – the only difference is that you change the data range to include all the cells you want to compare.

In practice, this means entering an extended range of data in the Conditional Format Rules menu and the Custom Format field. Let’s use the example above as a starting point, but instead of just looking for duplicates in column A, we’ll look in three columns: A, B, and C, as well as rows 1-10.

After entering our conditional format rules, the option becomes Apply to range A1:C10 and our custom formula becomes:

=COUNTIF($A$2:G,Indirect(title(row(),column(),)))>1

This will highlight all duplicates in all three columns and all 10 rows, making it easier to spot duplicate data:

Dealing with Duplicates v Duplicates in Google Sheets

Can you flag duplicates in Google Sheets? Definitely. While the process requires more effort than some other spreadsheet solutions, it’s easy to repeat once or twice, and once you’re comfortable with the process, you can zoom in and find duplicates in rows, columns, and even many larger datasets.