HOW TO USE CONDITIONAL FORMATTING BASED ON ANOTHER CELL IN GOOGLE SHEETS ?

INTRODUCTION

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.

CLICK HERE TO LEARN THE BASICS OF CONDITIONAL FORMATTING IN GOOGLE SHEETS

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.

WHEN WE NEED TO USE CONDITIONAL FORMATTING BASED ON ANOTHER CELL

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.

EXAMPLE 1:HIGHLIGHT THE NUMBERS DIVISIBLE BY THE GIVEN DIVISOR COMPLETELY

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 .

GIVEN DATA: NUMBERS 1 TO 100


DATA PREPARATION

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 ].

DIVISOR DROP DOWN LIST

CREATING CUSTOM FORMULA TO HIGHLIGHT DATA ON THE BASIS OF DIVISOR [ OTHER CELL ]

PRE-PLANNING

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.

ADD CUSTOM FORMULA TO THE DATA

FOLLOW THE STEPS TO HIGHLIGHT THE NUMBERS WHICH ARE DIVISIBLE BY THE GIVEN DIVISOR

  • Select the numbers 1 to 100.
  • Go to FORMAT MENU and choose CONDITIONAL FORMATTING.
  • The process is shown in the picture below.
SELECT THE DATA FOR SETTING THE CONDITIONAL FORMATTING RULES


  • As we click on the CONDITIONAL FORMATTING option, CONDITIONAL FORMAT RULES dialog box will open on the right side.
  • The APPLY TO RANGE will be shown filled as we selected the data before opting for the option of conditional formatting through the menu.
  • Go to FORMAT RULES dropdown and choose CUSTOM FORMULA IS as shown in the picture below.
CHOOSE CUSTOM FORMULA OPTION

  • After choosing the CUSTOM FORMULA IS option, the field for the custom formula will open.
  • Enter the formula as =NOT(MOD(C6,$C$20))
  • Set the FORMATTING STYLE which will be given to the cells satisfying the condition.
ENTER THE CUSTOM FORMULA AND SET THE FORMATTING STYLE

  • Now, we have set the format and put the formula too, so Click DONE .
  • As soon as we click DONE , we’ll see that all the data is green as the divisor is selected as 1. It is clear that all the digits are divisible by 1 and no remainder is left.
  • Now, let us try check our example.
  • Change the divisor from the given list, and see the effect.
  • The following animation shows the running of our example, which works perfectly fine.

RUNNING THE EXAMPLE OF APPLYING THE CONDITIONAL FORMATTING BASED ON OTHER CELL

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.