Conditional Formatting in Excel w. Drop-down list based Input

Best features of conditional formatting in excel which if coupled with drop down data validation list gives you wonderful reports.

Let me show you an example: In this case, I want to allot divisions to these particular newly joined employees. Whenever I want to select a drop down value which says HFD, it should then get colored according to the predefined color allocation. So if I choose RAD, there you go its green. If I choose RAD once again, again green. If I choose CDFD, orange.

Conditional formatting in Excel Step by Step


conditional formating in excel

In fact, let me quickly show you the procedure how to create a drop down. I choose all the cells. (conditional formating in excel)

Create a Dropdown in Data

I go to data. Data validation.

Data-Data Vslidation

I click on data validation. within which I will have to choose a list

Data Validation-List

And within that, I will have the ability to choose the source, OK.

Data Validation-Choose Souce

So all these cells now contain predefined list values, HFD, RAD, and CDFD.

Predefind List Values

Now I choose the entire region where I am supposed to give the division name. After which I go to home tab -> Conditional formatting -> new rule. Second option, it says format only cells that contain. So if any of the cell values is equal to yes, if any one of the cell values is equal to, now one option is you can write the hard coded data, HFD.

New Formatting Rule Box

Else you can also choose the cell containing HFD. So this is already fixed, which means whatever you do, wherever you apply this conditional Formatting, this $E$4 indicates this will not move.

New Formatting Rule Box-Select Cells

So if it’s HFD what color you want to give. I go to format, I go to fill. I put a light blue, ok. Ok,

New Formatting Rule Box-Format-Fill

Now just to test whether it’s working I put HFD. I put RAD, no color. Why no color? Because I have not applied the rule for RAD and CDFD.

HFD Division RAD Division

So once again I choose the entire block. I go to conditional formatting -> new rule within which I go to the same second option. And this time I say cell value equal to equal to what, equal to the cell containing the value RAD. Let me quickly apply the format. Format which should be light green, perfect. Ok, there you go green.

New Formatting Rule-Format Should be Light Green

Once again for the CDFD, I go to Conditional Formatting -> New rule, second option, cell value equal to equal to the cell containing the CDFD value. Let me go to format and choose orange.

New Formatting Rule-Format Should be Orange

So now I choose CDFD, HFD and RAD, all with colors. So this is how you can link a cell’s conditional formatting with another cell so that the value defines the color.

Different Colours Divisions in Data


I hope you like our blog, do share with your friend and colleague.

More related content available on our youtube channel: Yoda learning Youtube Channel

Check out our Premium E-Learning Video Courses

Download our Excel Ninja App

Conditional Formating in Excel| Excel VBA Tutorial | Excel Dashboard Tutorial | Text To Column in Excel | Password Remover | Free Excel Course