In this tutorial, you will learn how to highlight duplicates in Google Sheets
How To Highlight Duplicates In Google Sheets
Quick Navigation
If you have a large dataset, it can be helpful to highlight duplicate values. This way, you can easily see which values are repeated, and take action accordingly.
There are a few different ways to highlight duplicates in Google Sheets. The most common way is to use conditional formatting. This will allow you to apply a formatting rule to your data, and duplicate values will be highlighted automatically.
In this guide, we will show you how to highlight duplicates in Google Sheets using conditional formatting.
How To Use Conditional Formatting to Find Duplicates In Google Sheets
Here’s how to use conditional formatting to find duplicates in Google Sheets.
Step 1
Select the range you want to add conditional formatting to.
Step 2
Select the Conditional formatting option under the Format menu.
Step 3
In the Conditional format rules panel, select the Custom formula is option.
This option will allow you to type your own Google Sheets formulas to use as criteria.
Step 4
Next, we’ll just have to type a custom formula to use as a criteria for our conditional formatting. In this example, we’ll use the formula ‘=COUNTIF($A$2:$A2,$A2)’’ to highlight cells with a value that has already appeared in a previous row.
Next, you may edit the formatting style used to highlight cells. Click on Done to proceed.
Step 5
You should now have a range with conditional formatting that highlights duplicates.
In the example above, the formatting rules highlighted A6 and A11 since those colors have already been written in a prior cell.
Summary
This guide should be everything you need to highlight duplicates in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.