PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
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 EXCEL.
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.
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. ]
The following picture shows the NEW FORMATTING RULE DIALOG box which will appear after choosing the NEW RULE option from the drop down.
In the dialog box choose USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT, the last option which is shown by a RED ARROW in the following picture.
STEP 3:
STEP 4:
STEP 5:
STEP 6:
CONDITIONAL FORMATTING is a very useful option present in EXCEL.
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($G$G5:$G$15,G5)=1)
Let us understand the formula.
The internal function is simple COUNTIF FUNCTION 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 EXCEL 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
YOU MAY LIKE