Conditional formatting is the formatting [ font, color, fill color , size etc. ] of data as per its value of the content of the cells.
CONDITIONAL FORMATTING IS HIGHLIGHTING THE CELLS WHICH SATISFY THE GIVEN CONDITION
It is one of the most versatile functions present in GOOGLE SHEETS and very easy to apply and learn.
It makes our task so easy which you can understand from the following example.
There are countless conditions which can exist while making use of the conditional formatting.
In this article we’ll specifically learn the way to use the conditional formatting based on another cell in Google Sheets.
Conditional formatting is generally done on the basis of the data or values in the cell itself but many conditions can arise when we need to given this control to the values in other cells.
WE CAN EASILY CONDITION THE CELLS TO HIGHLIGHT THE DATA ON THE BASIS OF VALUE CONTAINED IN SOME DIFFERENT CELL.
There are many conditions where we need to use conditional formatting on the basis of value in another cell. For Example,
If we have a big list of mixed fruits, vegetables etc. , we can create a conditional formatting rule where the particular fruit will be highlighted when we select a fruit and a vegetable will be highlighted when vegetable is selected.
Let us try different examples and learn how we can highlight the data on the basis of another cell.
Let us take an example data of number 1 to 100.
Another controlling cell will be containing the divisors from 1 to 10. [ The number that divides a number ].
The numbers which are completely divisible by the divisors should be highlighted.
HIGHLIGHT THE NUMBERS COMPLETELY DIVISIBLE BY THE CHOSEN DIVISOR .
We have a data pool with number 1 to 100 as shown in the previous picture.
For the divisors, we have created a simple list containing the digits from 1 to 10 from which we ‘ll be choosing the divisor. [ CLICK HERE TO LEARN HOW TO CREATE SIMPLE DROP DOWN LIST IN EXCEL ].
Let us add the custom rule to the data so that it highlight the cells containing the data which satisfy our condition.
We want to highlight the data which is the multiple of the selected number or which shows the numbers divisible by the selected divisor.
We’ll be using the function MOD [ which provides the remainder after dividing the number from the given divisor]
We’ll be using the NOT function too to flip the outcome of the MOD function.
So, in this example we saw that we changed the highlighting rules on the basis of the value which is contained in another cell, which is not a part of the data.
We’ll add more examples to the same article, keep checking.
Please share your views and give suggestions to make this article even better.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE