In this article, we will learn how to create a Dropdown list with color in Microsoft Excel.
Drop down list limits the user to choose a value from the list provided instead of adding values in sheet.
We will be using Conditional Formatting and Data Validation options.
First, let’s understand how to make a dropdown list in Excel with an example here
Here is a list of three colours Red, Green and Blue. We need that the user has to select from this list.
Click Data > Data Validation option in Excel 2016
Data Validation dialog box appears as shown above.
Select the option List in Allow and select the source list in Source option and click OK.
A dropdown list will be created on the cell.
Now click Home > Conditional formatting. Select New rule from the list and a dialog box will appear.
Select Specific Text option and select the cell for colour as in this case Red.
Select Format > Fill option. Select the Red colour and click OK.
Repeat the process for all the options like Green and Blue and your list will be shown like in below snapshot.
Drop down list and Conditional formatting tools are very useful in Excel 2016, to view your data in a particular format manner. You can create a dropdown list in google sheets using the same method.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com
Check out more related articles:
How to edit a drop down list in Excel?
How to delete drop down list?
How to create dependent drop down list?
How to create multiple dropdown List without repetition using named ranges?
How to create drop down list?
Popular Articles:
50 Excel Shortcut to Increase Your Productivity
How to use the VLOOKUP Function in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Super, thank you
Our pleasure.
I facing challenges having this process executed. The colors are still not getting picked.
Grandan dankon por artikolo! Tre uzebla informacio.
Hi, I am still having problems with creating a coloured list. I followed the examples and still am not getting colour
Brilliant thank you
Easy way to understand. Thank you.
Great Explanation. Easy to understand, Screenshots helped.
Thanks a lot for the instruction. Can I ask how do I delete selected items in the cell? It seems like I can add onto the selection but cannot edit..
Thanks!
Thanks, for sharing knowledge. much helpful
Your page is very helpful. Thank You!
I think the admin of this web site is truly working hard for his site, for the reason that here every data is quality based material.
Good job
Thank you much for this, this is exactly what I was looking for, and the screenshot walkthrough was excellent 🙂
how to create drop down list from diferent sheet data
Gracias muy útil, espero seguir recibiendo mas tips.
Muchas Gracias por compartirlo, Prof. Arango
Muy Buena Profesor Arango. Gracias por compartirlo, eso Ud. nos enseñó en EXCELL BASICO I
This is cool !!