PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

HOW TO HIGHLIGHT THE DUPLICATE ENTRIES IN GOOGLE SHEETS

INTRODUCTION

While analyzing the data, we might come across the repeated data which 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 the 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 operation. So, in this article we would try to find out a way to ONLY HIGHLIGHT the repeated 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 same but other data is different
or
we want to check the duplicate values in any column which we want to remove
and many other requirements.
In such cases we can use the process to  highlight the duplicates.

STEPS TO HIGHLIGHT THE DUPLICATES IN GOOGLE SHEETS

Let us take an example of a column containing 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. ]

highlight duplicates in google sheets
EXAMPLE DATA
It is clearly visible that few of the entries shown in the above data is duplicate for example, apple, banana etc.

 

We will first use the 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.
highlight duplicates in google sheets
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.
highlight duplicates in google sheets
STEP 2: CLICK CONDITIONAL FORMATTING BUTTON

The following picture shows the CONDITIONAL FORMAT RULES box highlighted.

highlight duplicates in google sheets
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.
highlight duplicates in google sheets
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 ].
highlight duplicates in google sheets
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]
highlight duplicates in google sheets
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.

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

  • PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
  • KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE  AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON [email protected]
  • Didn’t get what you were looking for?? Just put your question in the chat box present in the Right Lower corner. Name and email id [No spamming, just for the answer of your requirement] are needed.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: