Excel Formula Based Conditional Formatting

Hello everyone in this tutorial I will be showing Formula Based Conditional Formatting with the help of an example given below:


Full Excel Course in Hindi

  • Get 2-Year Access with unlimited views of 220+ videos
  • Get Course Completion Certificate & Excel Mastery eBooks

Assume that I have data with a lot of Divisions. Now I want that RAD Division which is written inside the yellow cell as soon as it changes to another Division then the entire line in the list should be selected and colored automatically where the text is written inside Divisions.

So here we will be using Formula Based Conditional Formatting by following the steps are given below:

1. Choose the first name cell in the list

2. Press Shift + Ctrl + Right arrow Key and again Press Shift + Ctrl + Down arrow Key to choose the entire data

3. Then Press Ctrl + Backspace to deselect the entire Headers

Note – Do not choose Header while applying Formula Based Conditional Formatting

4. Go to Home Tab > Conditional Formatting Button

5. Under the Conditional Formatting dropdown select the New Rule option

6. A New Formatting Rule Panel will open go to the “Use a formula to determine which cells to format” option

Note – Whatever Formula is written inside the tab should get the answer of the formula in the form of true or false only

7. Now select the first cell inside the Divisions column =$C$8

8. Remove the Dollar ($) sign inside the tab =$C8

9. Press = and select the yellow cell in which divisions is written =$C8$B$3

10. Now go to Format > Fill Color > Choose any color which you want to fill > Press OK

11. Lastly, Press OK

12. You will see that the text which was written inside the Yellow cell is been colored in the list given below

13. Now type another division inside the Yellow cell and you will see that the entire division is colored in the list is given below