Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


Boots Your Excel With One Excel Suit
300+ professional and easy-used tools for Excel 2019-2003

adfloat 1adfloat 2adfloat 3adfloat 4

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

arrow blue right bubble  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 doc-button-1 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 doc-button-1button 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!
doc count by color 1
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!

ot excel

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 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...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    PM · 9 months ago
    thanks for the really good help
  • To post as a guest, your comment is unpublished.
    oli · 1 years ago
    Hi, 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?

    thanks again!
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Try this: in step 5, Highlight your drop down cells(here is column A) is replace with highlight the whole lines where place your drop down list cells, then apply the conditional formatting rule.
  • To post as a guest, your comment is unpublished.
    Tonf · 1 years ago
    Thank you ! It worked perfectly for me
  • To post as a guest, your comment is unpublished.
    eds · 1 years ago
    well, 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.
    Lauren · 1 years ago
    Very helpful tutorial! Much appreciated!