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

or

如何在Excel中快速创建动态下拉列表?

我们大多数人可以通过使用Excel中的数据验证功能创建一个下拉列表,但是有时我们需要一个链接或动态的下拉列表,这意味着当您在下拉列表A中选择一个值,并且希望这些值在下拉列表中更新B.在Excel中,我们可以创建动态下拉列表 数据验证 功能和 间接 功能。 本教程将介绍如何在Excel中创建从属下拉列表。

在Excel中创建动态下拉列表

在Excel中轻松插入多个复选框或选项按钮

通常我们只能在单元格中一次插入一个复选框或选项按钮,而且在Excel中的操作繁琐。 但是,Excel的Kutools 批量插入复选框 实用程序和 批量插入选项按钮 实用程序可以帮助Excel用户快速将多个复选框或选项按钮插入到所有选定的单元格中。 全功能免费试用60天!
广告插入复选框选项按钮1

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel - 最佳办公生产力工具将解决您的大部分Excel问题
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。

箭头蓝色右泡 在Excel中创建动态下拉列表

假设我有一张四列表,表示四种类型的食物:水果,食物,肉类和饮料,下面是它们的具体食物名称。 看下面的截图:

现在我需要创建一个包含食物的下拉菜单,如水果,食物,肉类和饮料,第二个下拉菜单中会有特定的食物名称。 如果我选择食物,第二下降将显示米饭,面条,面包和蛋糕。 为此,请应用以下步骤:

1。 首先,我需要为这些列和第一个类别行创建一些范围名称。

(1。)为类别创建范围名称,第一行选择A1:D1,然后键入范围名称 食品名称框,然后按 输入 键。

(2)然后你需要按照上面的步骤命名每个列的范围,如下所示:

小技巧 - 导航窗格:批量创建多个命名区域并在Excel窗格中列出
通常我们只能在Excel中一次定义一个名称范围。 但在某些情况下,您可能需要创建多个命名范围。 重复一个一个地定义名字一定是非常繁琐的。 Kutools for Excel提供了这样一个实用程序来快速批量创建多个命名区域,并在中列出这些命名区域 导航窗格 方便查看和访问。 全功能免费试用60天!
广告导航窗格名称

2。 现在我可以创建第一个下拉列表,请选择一个空白单元格或您想要应用此下拉列表的列,然后单击 数据 > 数据验证 > 数据验证,看截图:

3。 在 数据验证 对话框,单击 设置 标签,选择 名单 来自 下拉列表,然后输入这个公式 =食品来源 框。 看截图:

注意:你需要在公式中输入你所命名的类别。

4. 点击 OK 和我的第一个下拉列表已经创建,然后选择单元格,并将填充柄拖到您想要应用此选项的单元格。

5。 然后我可以创建第二个下拉列表,选择一个空白单元格,然后单击 数据 > 数据验证 > 数据验证 再次,在 数据验证 对话框,单击 设置 标签,选择 名单 来自 下拉列表,然后输入这个公式 =间接(F1)来源 框中,看截图:

注意: F1 表示我创建的第一个下拉列表的单元格位置,您可以根据需要更改它。

6. 然后点击 确定, 并向下拖动单元格内容,依赖下拉列表已成功创建。 看截图:

然后如果我选择一种食品,相应的单元格将只显示其特定的食品名称。

备注:
1。 只有当单元格处于活动状态时,下拉箭头才可见。
2。 如果你想创建第三个下拉列表,你可以继续深入,只需要使用第二个下拉列表即可 来源 第三个下拉列表


演示:在Excel中创建动态下拉列表

在这个视频中, Kutools Kutools Plus 选项卡添加 Kutools for Excel。 如果需要,请点击 60天免费试用,不受限制!

相关文章:

如何在Excel中插入下拉列表?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
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.
    Ferat · 1 years ago
    Why the dependent list doesn't change instantly?
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi Ferat,
      Have you followed the tutorial to create your dynamic drop down list? If followed the tutorial, the dependent list will change immediately as the main choice (in Cell F1) changes.
      More detailed information can help me understand your problem. If possible, upload some screenshots will be helpful!
  • To post as a guest, your comment is unpublished.
    Vup · 3 years ago
    So I tried this with dynamic ranges. It's a no go. It does work with static ranges though.
  • To post as a guest, your comment is unpublished.
    Rahul · 3 years ago
    I understand but how can I repeat the dropdown formatting in all rows of excel
    • To post as a guest, your comment is unpublished.
      H · 3 years ago
      Have you received an answer to your question? I'm wondering the same thing..
  • To post as a guest, your comment is unpublished.
    Andy · 3 years ago
    Lara, you are probably missed the second half of ste p 4.

    4. Click OK and my first drop down list have been created, then select the cell [u][b]and drag the fill handle to the cell that you want to apply this option[/b][/u].
  • To post as a guest, your comment is unpublished.
    Lara · 4 years ago
    I keep getting an error that says " the source currently evaluates to an error", any reason why? I followed the steps above. Any guidance would be greatly appreciated.
  • To post as a guest, your comment is unpublished.
    Sattam · 4 years ago
    Hi,
    I am looking to do something similar but the only problem being my dynamic parameter and value combination list is structured as below

    Parm_nm Val_list
    Parent1 Item01
    Parent1 Item02
    Parent2 Item01
    Parent2 Item03
    Parent2 Item11
    Parent3 Item32
    Parent3 Item02
    Parent4 Item09
    Parent4 Item01

    And I want to choose appropriate drop down depending on the header definition
    Eg: If the header definition is Parent3 then the drop down for the cells under the column should have Item32 and Item02. If the header is changed to Parent02 then the drop down list becomes Item01, Item3 and Item11. It is exactly what is being done here apart from the fact the source data structure is completely different and I do not have freedom to restructure it. In such a scenario I am not being able to [i][b]Name the range[/b][/i] of the second level list as I cannot just select and name them. Any help in this regard will highly appreciated.
    Regards,
    Sattam
  • To post as a guest, your comment is unpublished.
    mayank · 4 years ago
    in case the second drop down has to pick a list that is Dynamic, then how will this work. As in if fruit food meat and drink is for month one and the names for month two changes to fruit2 meat2 food2 and drink2 along with the selection below this headings then how do we use thius formula.
  • To post as a guest, your comment is unpublished.
    Sundar · 5 years ago
    Thank you it worked like a magic
  • To post as a guest, your comment is unpublished.
    Bob Frinder · 5 years ago
    When I create a drop-down list using data validation, how can I select the valid entries from the keyboard, I want to type the number and have it auto-populate.
    Data Validation List looks like this :
    1_Ready
    2_On Hold
    3_ Rejected
  • To post as a guest, your comment is unpublished.
    PradeepRS · 5 years ago
    Quite useful. Thank you.
  • To post as a guest, your comment is unpublished.
    Shafeeq · 5 years ago
    This is great. I also wanted to know how to add a dynamic list through Offset or other function.
    What exactly I am looking for is, if I add "Fried Rice" under Food column below Cake, it should automatically need to considered.
  • To post as a guest, your comment is unpublished.
    Cindy J · 5 years ago
    Great explanation and example, thank you - but I'm having a problem. Using the example above, you select "Meat" and then select "Chicken" from the dependant drop-down list. If you change "Meat" to "Fruit", "Chicken" is still displayed. Is there a way to invalidate the "Chicken" selection and force you to re-select from the "Fruit" drop-down list?
    • To post as a guest, your comment is unpublished.
      Marc A · 5 years ago
      Hello Cindy,

      Did you ever get a response to your question? I am wondering the same thing.

      Thanks!
  • To post as a guest, your comment is unpublished.
    Bean Counter · 5 years ago
    Best tutorial of this I have found yet.

    Thank you!
  • To post as a guest, your comment is unpublished.
    Vishal Chauhan · 5 years ago
    Hi Team,

    It's perfectly working.

    :-)

    Thanks
    Vishal
  • To post as a guest, your comment is unpublished.
    Manjunath N · 5 years ago
    Hi Team,

    Thank you so much, its working. :-)

    Regards,
    Manjunath N
  • To post as a guest, your comment is unpublished.
    Emilykells · 5 years ago
    Hi how can I delete the columns that I used to create my drop downs from my sheet that way I only have the dropdown lists.
  • To post as a guest, your comment is unpublished.
    Neha · 5 years ago
    i tried this & working... :D
  • To post as a guest, your comment is unpublished.
    Jeffrey · 5 years ago
    I tried this on my 2013 Excel and its not working. Help
    • To post as a guest, your comment is unpublished.
      Wyman · 5 years ago
      Hi,

      I think the error sign was referring to "This current selection evaluates to error... " ???

      Just click Ok and ignore the error sign.
  • To post as a guest, your comment is unpublished.
    David Amble · 5 years ago
    Steps 5 through 6 produced an error. No link. It gave me a drop down cell with nothing in it.
    • To post as a guest, your comment is unpublished.
      Raiez Moideen · 5 years ago
      I think error can happen in the following cases(from my experience)...

      1)if you use simply "indirect(F1)" in Source box, instead of the cell location for the first drop down list...

      2)The name putting for each ranges in the Name box for the items in Second drop down list must exactly match the items in the first drop down list...
      • To post as a guest, your comment is unpublished.
        Johnathan Dunker · 4 years ago
        Also, make sure that the drop down list that the indirect list is pulls data from has something selected.
    • To post as a guest, your comment is unpublished.
      Natalia · 5 years ago
      I was getting an error too but then I figured out that I can't have two words, I was using "Home Improvement" and I had to change to "HomeImprovement" also make sure your "F1" cell es the cell you need (Mine would be B37)
      That's all I know