HOW TO FIND AND HIGHLIGHT THE DUPLICATES IN GOOGLE SHEETS

Table of Contents

INTRODUCTION

While analyzing the data, we might come across repeated data that needs to be replaced or highlighted. If we start doing this process manually, it can be done on a small range or data but what if we have thousands of rows to be examined. Obviously, it is going to be almost impossible. That is the reason why we need to find and replace duplicate entries.

We can easily remove the duplicate entries using the available tools in GOOGLE SHEETS.

But, there is always a doubt we simply remove the duplicates.

What if we just want to highlight the duplicates in a specified column and check them manually.

There is no direct utility for such an operation.

So, in this article, we would try to find out a way to ONLY HIGHLIGHT the repeated or duplicate values in a specified column using CONDITIONAL FORMATTING.

Let us start.

WHEN TO HIGHLIGHT THE DUPLICATES IN GOOGLE SHEETS?



HIGHLIGHTING THE DUPLICATES in a specified column can be very useful while analyzing any data.

For example, If there are few cases in which the principal field is the same but other data is different

or

we want to check the duplicate values in any column that we want to remove and many other requirements. In such cases, we can use the process to highlight the duplicates.

STEPS TO FIND AND HIGHLIGHT THE DUPLICATES IN GOOGLE SHEETS

Let us take an example of a column containing a few duplicate entries.

Suppose the following data is present in the column.

APPLE

BANANA

POMEGRANATE

OLIVE

BANANA

APPLE

GRAPES

GUAVA

WALNUT

CASHEW

ALMONDS

Our aim is to just highlight the entries which are repeated. [ Only highlighting the entries which are repeated so that we don’t need to focus upon the entries which are already unique. ]

EXAMPLE DATA

It is clearly visible that a few of the entries shown in the above data is duplicate, for example, apple, banana etc. We will first use conditional formatting to highlight the data followed by the explanation of the formula. 

STEPS TO HIGHLIGHT DUPLICATES IN GOOGLE SHEETS

STEP 1:

  • Select the column or all the cells in which we want to highlight the duplicate items.
SELECT THE DATA [ WHERE WE WANT TO HIGHLIGHT THE DUPLICATES]

STEP 2:

  • Go to FORMAT MENU> Conditional Formatting .
  • The CONDITIONAL FORMAT RULES box will open on the right side of the window.
STEP 2: CLICK THE CONDITIONAL FORMATTING BUTTON

The following picture shows the CONDITIONAL FORMAT RULES box highlighted.

CONDITIONAL FORMATTING OPTION BOX

STEP 3:

  • As we had already selected the Range before applying the conditional formatting, the APPLY TO RANGE field is already filled. If not, we can enter or edit the field as per requirement.
  • Go to FORMAT RULES and click the drop down.
  • Choose CUSTOM FORMULA IS option at the end of the list as shown in the following picture.
CLICK CUSTOM FORMULA

STEP 4:

  • After clicking the CUSTOM FORMULA IS option , we will see the following window.

The picture below shows the location for entering the formula and the options to choose the formatting of the data which satisfies the condition [ i.e. the data which we want to highlight ].

ENTER CUSTOM FORMULA

STEP 5:

  • ENTER THE FORMULA AS =NOT(COUNTIF($D$6:$D$16,D6)=1)  [EXPLANATION IN NEXT SECTION]
  • Select the format for the HIGHLIGHTED CELLS. For our example we have chosen the FILL COLOR as OLIVE GREEN which is visible in the picture below.
  • Click DONE.
  • The cells which satisfy our condition will be highlighted. [ The duplicate data to be specific in our case]
FINAL RESULT

EXPLANATION OF THE METHOD TO HIGHLIGHT THE DUPLICATE CELLS IN GOOGLE SHEETS

CONDITIONAL FORMATTING is a very useful option present in GOOGLE SHEETS.

Conditional formatting provides us with the option of HIGHLIGHTING the specific cells within our data which fulfills the certain given conditions.

In our example, we want to highlight the duplicate data for which we used a CUSTOM FORMULA.

The formula used is

=NOT(COUNTIF($D$6:$D$16,D6)=1)

Let us understand the formula.

The internal function is simple COUNTIF which counts the cells containing a particular data with the given condition.

In this we put the range as the complete data with the absolute references using the $. [ Absolute references are the ones which doesn’t change while dragging down the formula or any relative action as in conditional formatting.]

The value to be looked up is given as D6 which is the first cell in our selection.

IN CONDITIONAL FORMATTING, THE FORMULA IS ALWAYS PUT FOR THE FIRST CELL OF THE SELECTED RANGE.

The outcome of countif is equated to 1 which means that we are asking the google sheets to tell us whether the number of occurrences of data is equal to 1 or not.

If yes, then we don’t want a highlight and if no, then we want a highlight.

So we used the NOT function which simply reverses the outcome from true to false and false to true which solves our problem.

RELATED ARTICLES

COUNT DUPLICATE VALUES IN GOOGLE SHEETS