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
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.
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. ]
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
- Select the column or all the cells in which we want to highlight the duplicate items.
- Go to FORMAT MENU> Conditional Formatting .
- The CONDITIONAL FORMAT RULES box will open on the right side of the window.
The following picture shows the CONDITIONAL FORMAT RULES box highlighted.
- 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.
- 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 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]
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
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.