Google Sheets can be used to highlight duplicates in a spreadsheet. How the repeats are displayed is fully customizable in the formatting style options. This can be used to represent specific reasons for why a duplicate was highlighted so it makes sense to anyone looking at the data. For instance, you can highlight the entire row of a given column containing any duplicates. Or, alternatively, it can also be formatted to simply highlight any cell with a value that appears more than once in the spreadsheet.
An easy-to-follow, concise guide below will show you how highlighting duplicates can be achieved in Google Sheets.
Highlighting Duplicates in a Single Column
Say you have a set of data – in this example, a list of randomly arranged car models – that contains duplicate values, and you want to highlight each one. (shown below)
Step #1: Selecting The Range
To highlight the duplicate cells using the conditional formatting feature, follow the steps below.
Select the range with which you want to use the formatting tool. (shown below).
In our example, the range is A1:A12. The range can be selected by right clicking “Format” at the top of the page, and then selecting “Conditional Formatting”. (example shown below).
Right click “Format”
Select “Conditional Formatting”
This prompt should be displayed upon selecting “Conditional Formatting”. The range, as indicated by the red box, can be shown here. In case you already have a conditional formatting rule in place, simply select “add another rule” before continuing. (shown below)
Selecting a specific range is achieved by clicking the “four squares” icon at the end of the box containing the data range. (shown below)
Upon clicking the icon mentioned above, you should be greeted with a prompt requesting you to select the data range you want to format the spreadsheet with. (example shown below)
After the aforementioned prompt is displayed, simply drag your cursor over the data in question to highlight it, selecting the desired range.
Step #2: Setting the Format
Set “format cells if”, under “Format Rules”, to “Custom formula is”. And enter the duplicate check formula
=countif(Range,Criteria)>1. In “(Range,Criteria), enter the range that you have selected in this format:
=countif($A$1:$A$12,A1)>1. In our example, the range “A1:A12”. Which denotes column A (“$A$1) and rows 1-12 in column A ($A:$12,A1). Change the variables within the (Range,Criteria) parentheses according to the data range that you have selected. Then hit done. (example shown below)
In Conditional Formatting Rules, you can also specify a “formatting style”, ie. using a bold or italic typeface, or setting a specific color for the highlighting of the duplicates. Different colors for different, individual rules can be used to signify different reasons for highlighting. To do this, select one of the options displayed under “formatting style”. (shown below)
Google Sheets, within our desired column, is now highlighting in the color we selected the duplicates in our randomly assorted list of car models under an italic typeface with an underline.
Highlighting Duplicates in an Entire Row
Step #1: Selecting the Range
Following the same initial steps as with highlighting duplicates in a single column, you’ll want to make your way to the “Conditional Format Rules” menu. How highlighting the duplicates in a row differs from highlighting duplicates in a column is limited simply to the data range. For instance, our earlier example had a range of “A1:A12” to highlight the duplicates in our list of car models, though only within a single column — column A.
To highlight the duplicates in an entire row, like we did above, you’ll need to select a range by right clicking the icon with the four squares. This time, instead of dragging your cursor across column A in order to highlight it, you’ll, instead, highlight all of the columns that contain the rows with the duplicates you want to highlight. In our example, we only want to highlight columns A and B. (shown below)
Step #2: Setting the Format
The formula for highlighting duplicates in a row in lieu of a single column is obtained by making one small adjustment to the original formula. The original
=countif($A$1:$A$12,$A1)>1. The values at the end of the parentheses are changed from “,A1” to “,$A1”. We simply added a $ after the last comma before the A.
Google sheets is now highlighting the duplicates in our list of car models and their countries of origin. (shown below)
I hope this tutorial has helped you to better and more effectively utilize the conditional formatting tools in Google Sheets, and that you have successfully learned how to highlight duplicates.