提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中使用颜色编码的下拉列表?

DOC-颜色下拉-list1
在Excel中,创建一个下拉列表可以帮助您很多,有时,您需要根据相应的选定内容对下拉列表值进行颜色编码。 例如,我创建了一个水果名称的下拉列表,当我选择Apple时,我需要将该单元格自动着色为红色,而当我选择橙色时,单元格可以用橙色着色,如下图所示。 有没有什么好的建议来解决这个问题?
带有条件格式的颜色编码下拉列表
Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文字; 将数字和货币转换为英文单词。
  • 合并工具:将多个工作簿和工作表合二为一; 合并多个单元格/行/列,而不会丢失数据; 合并重复的行和总和。
  • 拆分工具:根据价值将数据分割成多个工作表; 一本工作簿可转换为多个Excel,PDF或CSV文件; 一列到多列。
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 将个性化电子邮件批量发送给多个收件人。
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 与Office 2007-2019和365一起使用; 支持所有语言; 在您的企业或组织中轻松部署。

箭头蓝色右泡 带有条件格式的颜色编码下拉列表

为完成此任务,我们需要先创建一个下拉列表,然后应用条件格式对单元格着色。 请按以下步骤操作:

首先,创建一个下拉列表:

1. 创建数据列表并选择您想要将下拉列表值添加到的范围。 在这种情况下,我选择列A放置下拉列表,请参阅截图:

DOC-颜色下拉-list1

2. 点击 数据 > 数据验证 > 数据验证,看截图:

DOC-颜色下拉-list1

3。 和在 数据验证 对话框,单击 设置 选项卡,然后选择 名单 选项从 下拉列表,然后单击 DOC-按钮1 按钮选择要使用的列表值。 看截图:

DOC-颜色下拉-list1

4。 然后点击 OK,下拉列表已创建如下所示:

DOC-颜色下拉-list1

其次,应用条件格式对下拉列表值进行颜色编码

5。 突出显示您的下拉单元格(这里是A列),然后点击 主页 > 条件格式 > 新规则,看截图:

DOC-颜色下拉-list1

6。 在 新的格式规则 对话框,单击 格式 只有包含选项的单元格 选择一个规则类型 部,下 只格式化单元格 部分中,选择 特定文本 从第一个下拉列表中选择 包含 从第二个下拉开始,然后单击 DOC-按钮1按钮选择想要格式化特定颜色的值,请参阅屏幕截图:

DOC-颜色下拉-list1

7。 然后点击 格式 按钮,然后从中选择一种你喜欢的颜色 标签。
DOC-颜色下拉-list1

8。 然后点击 OK > OK 关闭对话框,对每个其他下拉选择重复步骤5到7,例如桃绿色,葡萄紫色......

9。 设置值的颜色后,当您从下拉菜单中选择任何值时,单元格将自动以其指定颜色着色。

DOC-颜色下拉-list1

如果你想计算/求和或按单元格颜色/字体颜色进行计算,你可以试试这个:

通过Excel中的背景或颜色快速计算/求和单元格

在某些情况下,您可能有多种颜色的单元格区域,您想要根据相同颜色对值进行计数/求和,您如何快速计算?
您订购的 Kutools for Excel's 按颜色计数,可以通过颜色快速进行多次计算,也可以生成计算结果的报告。 在30天内点击免费的全功能试用版!
doc按颜色1计数
Kutools for Excel:拥有超过300个方便的Excel加载项,可以在30天内免费试用,没有限制。

选项卡式浏览和编辑多个Excel工作簿/ Word文档,如Firefox,Chrome,Internet浏览10!

您可能熟悉在Firefox / Chrome / IE中查看多个网页,并通过轻松单击相应的选项卡在它们之间切换。 此处,Office选项卡支持类似的处理,允许您在一个Excel窗口或Word窗口中浏览多个Excel工作簿或Word文档,并通过单击其选项卡轻松切换它们。 单击免费获得Office Tab的30天试用!

擅长

Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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 · 1 years 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 · 2 years ago
    Thank you ! It worked perfectly for me
  • To post as a guest, your comment is unpublished.
    eds · 2 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 · 2 years ago
    Very helpful tutorial! Much appreciated!
  • To post as a guest, your comment is unpublished.
    chris harris · 2 years ago
    How can I change the colors to a pattern type thatch? In multiple cells/columns to something more aesthetic. Would like to send you a screen shot of what i'm creating, would you assist?
  • To post as a guest, your comment is unpublished.
    bachie · 2 years ago
    This Is really cool I tried It & It worked I feel so good about it.

    Thanks
  • To post as a guest, your comment is unpublished.
    R.Karanth · 2 years ago
    it is helpful to data validation colour change the perticular name
    thank you
  • To post as a guest, your comment is unpublished.
    Amjad · 3 years ago
    It worked.... Many Many Thanks.
  • To post as a guest, your comment is unpublished.
    Harbindar · 3 years ago
    Kudos - this really helped and gave the best examples compared to most sites. Thank You.
  • To post as a guest, your comment is unpublished.
    vinay · 3 years ago
    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.
    pavan · 3 years ago
    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.
    kARINE · 3 years ago
    Fantastic, very helpful, took me a couple of trials but got it pretty quickly,
  • To post as a guest, your comment is unpublished.
    Troy · 3 years ago
    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.
    Tayah · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Edukondalu · 4 years ago
    Excellent, it helped to me while creating some templates.
  • To post as a guest, your comment is unpublished.
    Mary · 4 years ago
    I wanted to use the color to tell me what Data Validation text cells had changed (after sending back to client).
  • To post as a guest, your comment is unpublished.
    Sharon · 4 years ago
    Great. Thanks Charlotte. Took a few goes to get all my rules working as I had quite a few drop down options to correct, but worked eventually. I didn't fancy changing some other formatting by cutting and pasting, so a really good option. New skill acquired!
  • To post as a guest, your comment is unpublished.
    Charlotte · 4 years ago
    To those who followed the steps but found when they selected something from the drop down list the colour wasn't coming up you need to as Jocelyn says, make sure that your selection of where to apply the change to covers your whole spreadsheet. To do this click in the cell entry (like Apple) and go back to Conditional Formatting -> find the colour you're looking for in 'cell value contains' and click on the value selector button -> this will show that you have selected one cell, you need to then go back to your spreadsheet and click in the top left corner of your spreadsheet to select all content and hit return then click OK. This should now work - it did for me. Thanks for this article - very useful!
    • To post as a guest, your comment is unpublished.
      Charlotte · 3 years ago
      To clarify - you highlight the cell and go to conditional formatting, manage rules and then change the selection that the rule applies to, to ensure it applies to the whole spreadsheet (easiest way to do that is to click the top left corner of the spreadsheet).
  • To post as a guest, your comment is unpublished.
    Jocelyn · 4 years ago
    This is awesome!
    Very useful and time saving for my work!
  • To post as a guest, your comment is unpublished.
    Jenny · 4 years ago
    You can apply the conditional formatting to the values that the dropdown feeds off of, so that it reflects everywhere you put the drop down automatically. After you add your rules for formatting, go into Rules Manager and ensure the rule is being applied to the entire spreadsheet and not just one value cell.
  • To post as a guest, your comment is unpublished.
    Cpt. Haym · 4 years ago
    Man, this is what I was exactly looking for. Thank you very much!
  • To post as a guest, your comment is unpublished.
    imahogg · 4 years ago
    This works perfectly. If it doesn't work for you, remember to place the conditional formatting to the cell where you want the drop down placed, not on the drop down text. It didn't work for me at first, but when I read these comments, I understood where I went wrong. It works great.
  • To post as a guest, your comment is unpublished.
    JP · 4 years ago
    Worked well and is simple.
    Thanks
  • To post as a guest, your comment is unpublished.
    d · 4 years ago
    Worked like a champ. Thanks! Remember to do conditional formatting to the cell which will use the drop and not the values in the drop down. Once I did that it worked as designed.
  • To post as a guest, your comment is unpublished.
    Tracy Perrin · 5 years ago
    I've set up my drop down boxes in excel. I now want to include this into an email so my recipients can use the drop downs. When I insert excel spreadsheet into Outlook and send email,drop downs don't work.
  • To post as a guest, your comment is unpublished.
    jagoan · 5 years ago
    it works... thank you :-)
  • To post as a guest, your comment is unpublished.
    Amtrix · 5 years ago
    Excellent page, just what I was looking for.
    Many thanks.
  • To post as a guest, your comment is unpublished.
    Adam Pavey · 5 years ago
    Works brilliantly. Thanks very much.
  • To post as a guest, your comment is unpublished.
    Sung · 5 years ago
    Awesome! It was very helpful. Thank you.
  • To post as a guest, your comment is unpublished.
    ian · 5 years ago
    won't work for me either
  • To post as a guest, your comment is unpublished.
    narusso · 5 years ago
    This will format the cell with the data chosen via the pulldown list, but it will *not* format the options you see in the pulldown list before you select one. In other words, it's just Data Validation and Conditional Formatting on the same cell -- not formatted pulldown list entries.
  • To post as a guest, your comment is unpublished.
    jdl · 5 years ago
    It didn't work for me either. I followed it step by step 3 times and still no color.
  • To post as a guest, your comment is unpublished.
    Balaji · 5 years ago
    It's very helpful to saparate the data with colours.
  • To post as a guest, your comment is unpublished.
    Shabnam · 5 years ago
    This is good stuff but it does not work for me. I formatted the cells that I used for my list with the colours but when I select from my list it's not working. Please help!
  • To post as a guest, your comment is unpublished.
    Richard Hay · 5 years ago
    I can get to point 6 then when i select specifc text and containg, when i go to use the cell location icon it is not there, can anybody help please. I have tried just putting in the cell details but this does not work either.help required please.
  • To post as a guest, your comment is unpublished.
    Ianford · 5 years ago
    Awesome stuff, made me look so genius to my friend
  • To post as a guest, your comment is unpublished.
    shri · 5 years ago
    extremely helpful :-)
    thank you
  • To post as a guest, your comment is unpublished.
    Ben Quinn · 5 years ago
    Really great and useful and clear. THANK YOU.
    • To post as a guest, your comment is unpublished.
      Christy · 5 years ago
      What if I can click on the cell, but it won't let me click on conditional formatting. It is black and is not an option.
  • To post as a guest, your comment is unpublished.
    Torama · 5 years ago
    Tiger,

    For the conditional formatting, make sure you are highlighting the cells where you will see the data in the spreadsheet, not the cells where you wrote the drop down menu options.
    Do that with only one cell, when you got it to work, just drag this cell around to cover your entire spreadsheet.
  • To post as a guest, your comment is unpublished.
    Tiger · 5 years ago
    I tried this but it will not display colours when selecting from the drop down list. What on earth am I doing wrong?
    • To post as a guest, your comment is unpublished.
      eds · 2 years ago
      Hi Tiger,

      I works only if you type the word what is the value or word in cell such like apple or grape etc.
      Meaning, instead of you type the $D$2, it should be you the word apple.

      Try and let me know if still doenst work.