How to color coded drop down list in Excel?
Color coded drop down list with Conditional Formatting
Recommended Productivity Tools for Excel/Office
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.
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.|
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 7 months agothanks for the really good help
To post as a guest, your comment is unpublished.· 11 months agoHi, this was very helpful. However, now i need to color the whole line in the same color in reference to one cell on the line. know what i mean? can you assist in this too?
To post as a guest, your comment is unpublished.· 1 years agoThank you ! It worked perfectly for me
To post as a guest, your comment is unpublished.· 1 years agowell, finally it works!
this article should have been informed about the Excel version. Format Conditioning has its new feature in Excel 2013 and above and has quite a little different.
For example, the step above as mentioned would only work if your MS Excel you are using is version 2010 and above. For below, the reason why it wouldn't worked is because conditioning format should be placed the value on the containing cell i.e you have to type the word apple to the rule (and not $D$2 as stated in the screenshot). The reason why some others had come up a not working is maybe because it use up different version of MS Excel like mine which is MS Excel 2007.
the value should have been like this upon filing up in Format only cells Specific Text:
Specific Text - Containing - type the word Apple or grapes or strawberry. Then,
Go to Format button for your specified color and fonts you desire.
As for me, you don't need to have select the entire cells in spreadsheet as it would only gotten confuse.
To post as a guest, your comment is unpublished.· 1 years agoVery helpful tutorial! Much appreciated!
- ← Previous
- Next →