Create Drop Down List in Excel using Conditional Formatting in Excel


Let’s say you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts and sparklines, conditional formatting provides another way to visualize data and make worksheets easier to understand.

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 allow 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 formating in excel

Let’s create Conditional Formatting in Excel with Drop Down List
Excel Conditional Formating

  1. I go to Data. Data validation.
    Data-Data Vslidation
  2. I click on Data validation. within which I will have to choose a list
    Data Validation-List
  3. And within that, we have the ability to choose the source.
    Data Validation-Choose Souce
  4. So all these cells now contain predefined list values, HFD, RAD, and CDFD.
    Predefind List Values
  5. Now choose the entire region where we are supposed to give the division name.
  6. Now got to home tab -> Conditional formatting -> new rule. The 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
  7. Also, 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. Create Drop Down List in Excel

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. Create Drop Down List in Excel, So this is how you can link a cell’s excel conditional formatting with another cell so that the value defines the color.

Different Colours Divisions in Data

I hope you like our Excel Conditional Formatting 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

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018