Boots Your Excel With One Excel Suit
300+ professional and easy-used tools for Excel 2019-2003
300+ professional and easy-used tools for Excel 2019-2003
How to color coded drop down list in Excel?
In Excel, create a drop down list can help you a lot, and sometimes, you need to color coded the drop down list values depending on the corresponding selected. For instance, I have created a drop down list of the fruit names, when I select Apple, I need the cell is colored with red automatically, and when I choose Orange, the cell can be colored with orange as following screenshot shown. Are there any good suggestions to solve this?
Color coded drop down list with Conditional Formatting
Color coded drop down list with Conditional Formatting
Recommended Excel Productivity Tools
Bring tabbed editing and browsing to Office (include Excel), much more powerful than the browser's tabs. Free Download...
Now I can create the first drop down list, please select a blank cell or a column that you want to apply this drop down list, and then click Data Data Validation Data Validation, see screenshot: 3. In the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula =Foodstuff into the Source box. See screenshot. The following sheet shows a simple drop-down list in an Excel sheet. Users click the drop-down arrow to display a list of items from A1:A4. If a user tries to enter something that isn't in the.
More than300 powerful advanced features for Excel, designed for1500 work scenarios, save70% time for you. Free Download...
Color coded drop down list with Conditional Formatting
For finishing this task, we need to create a drop down list first, and then apply the Conditional Formatting to color the cells. Please do as following steps:
First, create a drop down list:
1. Create a list of data and select a range that you want to put the drop down list values into. In this case, I select column A to put the drop down list, see screenshot:
2. Click Data > Data Validation > Data Validation, see screenshot:
3. And in the Data Validation dialog box, click Settings tab, and choose List option from the Allow drop down list, and then click button to select the list values that you want to use. See screenshot:
4. Then click OK, the drop down list has been created as following shown:
Second, apply the Conditional Formatting to color coded the drop down list values
5. Highlight your drop down cells(here is column A), and go to click Home > Conditional Formatting > New Rule, see screenshot:
6. In the New Formatting Rule dialog box, click Format only cells that contain option in the Select a Rule Type section, under the Format only cells with section, choose Specific Text from the first drop down list and select containing from the second drop down, then click button to select the value that you want to format a specific color, see screenshot:
7. Then click Format button, and select one color you like from the Fill tab.
8. And then click OK > OK to close the dialogs, repeat steps 5 to 7 for each other drop down selection, for example, Peach for green, Grape for purpleā¦
9. After setting the colors for the values, when you choose anyone value from the drop down menu, the cell will be colored with its specified color automatically.
If you want to count/sum or do calculations by cell color/font color, you can try this:
Quickly Count/Sum cells by background or fomt color in Excel |
In some cases, you may have a range of cells with multiple colors, and what you want is to count/sum values based on same color, how can you quickly calculate? With Kutools for Excel's Count by Color, you can quickly do many calculations by color, and also can generate a report of the calculated result. Click for free full featured trial in 60 days! |
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. |
Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10! |
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 45-day trial of Office Tab! |
Recommended Productivity Tools for Excel
Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd
- More than300 powerful advanced features, designed for1500 work scenarios, increasing productivity by70%, give you more time to take care of family and enjoy life.
- No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
- Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds,
- Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
- 110,000 highly effective people and 300+ world-renowned companies' choice.
- 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.
Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs
- Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
- Hi,
what if i need different color for the same text in the dropdown? if supppose i have text X two times in the dropdown and i want two different colors for X because here color is representing two different results irrespective of the text. - To post as a guest, your comment is unpublished.it is working for me when but for only for the first cell what i have enable in the dropdown
i want to use it for many times.
plz solve my issue - To post as a guest, your comment is unpublished.Fantastic, very helpful, took me a couple of trials but got it pretty quickly,
- To post as a guest, your comment is unpublished.Worked perfect! Thanks for the pictures and detailed description. Didn't expect this to be so many steps
- To post as a guest, your comment is unpublished.I am only able to change the first item in the list, but when I try it with the others it changes the color of the other options to the same thing.
- ...