如何在Excel中创建下拉列表？

I have a column of values in Excel, like this :

我在Excel中有一列值,如下所示:

How do I turn this into a drop-down list, such at each row in the column will have that drop-down list (with dog, cat , fish, bat , toad. .. all inside )

如何将其转换为下拉列表,例如列中的每一行都会有下拉列表(包含狗,猫,鱼,蝙蝠,蟾蜍.......所有内部)

thanks !

5 个解决方案

#1

You do that with list validation in the "Data" ribbon.

您可以使用“数据”功能区中的列表验证来执行此操作。

See here

#2

First step is to make a list of the items you want to see in the drop-down, but anywhere in your spreadhseet , like so:

第一步是列出您想要在下拉列表中看到的项目,但是在您的spreadhseet中的任何位置,如下所示:

Then the next step is to click the "Data Validation" button in the ribbon :

然后,下一步是单击功能区中的“数据验证”按钮:

Ok, step 3 is to click "Setitngs" in the "Data Valid" menu , and choose "List" from drop-drown:

好的,第3步是点击“数据有效”菜单中的“设置”,然后从drop-drown中选择“List”:

Step 4 is to click on that little blue icon in the "List" sub-menu :

第4步是点击“List”子菜单中的那个小蓝色图标:

The next step is to simply highlight the Cells you needs.

下一步是简单地突出显示您需要的单元格。

Finished now. Here is dropdown :

现在完成了。这是下拉列表:

#3

You have to use the data validation command in the data tools group on the data tab and then select a list and enter a reference to your list (assign it to a name range for simplicity).

您必须在数据选项卡上的数据工具组中使用数据验证命令,然后选择列表并输入对列表的引用(为简单起见,将其分配给名称范围)。

Follow this link for complete steps.

请点击此链接查看完整步骤。

#4

To do that, you should use Data Validation as described in Amit answer or in this page (except that for the seventh point, which I used for my case in Excel 2010, I replaced the ',' with ';')

为此,您应该使用Amit答案或本页中所述的数据验证(除了第七点,我在Excel 2010中用于我的情况,我将','替换为';')

#5

Create a hidden drop down list in excel using the Excel data validation options.

使用Excel数据验证选项在Excel中创建隐藏的下拉列表。

1. Create a list of choice. This option of choice will be appear in drop down list.
   (I used 4 option of choice from A1 to A4, user can increase as per their demand)

   创建一个选择列表。此选项将显示在下拉列表中。 (我使用了从A1到A4的4个选项,用户可根据需求增加)

   

2. Select the cell/cells in a column where user wants to appear the drop-down list. I selected the cells in a column D from D1 to D10

   在用户想要显示下拉列表的列中选择单元格/单元格。我从D1到D10的D列中选择了单元格

3. Go to Data -> Data Validation page

   转到数据 - >数据验证页面

4. On Settings page
   A. In Allow field, select the option 'List'.
   B. Ensure Ignore blank & In-cell dropdown option is ticked/selected.
   C. In the Source field, select/enter the range of cells where your original list of choice or your original list of options is present;
   (here in my case, it’s . $A$1:$A$4)
   

   在“设置”页面上A.在“允许”字段中,选择“列表”选项。 B.确保勾选/选中忽略空白和单元格下拉选项。 C.在“源”字段中,选择/输入原始选择列表或原始选项列表所在的单元格范围; (在我的情况下,这是。$ A $ 1:$ A $ 4)

   If you prefer, you can enter this range using the mouse, by clicking on the symbol at the right of the Source field and then selecting the required range of cells with the mouse.
   

   如果您愿意,可以使用鼠标输入此范围,方法是单击“源”字段右侧的符号,然后使用鼠标选择所需的单元格范围。

   D. Click OK.

   D.单击“确定”。

5. Once done, select any cell in column D, from D1 to D10. Your drop-down menu should appear containing your list of choice values.
   

   完成后,选择D列中的任何单元格,从D1到D10。您的下拉菜单应该包含您的选择值列表。

   

Now, if you want to hide the column that contains the original list. Right click on column and select Hide

现在,如果要隐藏包含原始列表的列。右键单击列并选择“隐藏”

Un-Hidding it, by simply typing A1 in the Name Box next to the formula bar.

只需在公式栏旁边的名称框中键入A1,即可取消隐藏。

Reference:
1. http://www.excelfunctions.net/Excel-Drop-Down-List.html
2. https://support.office.com/en-us/article/Show-or-hide-columns-and-rows-659c2cad-802e-44ee-a614-dde8443579f8

参考:1。http://www.excelfunctions.net/Excel-Drop-Down-List.html 2. https://support.office.com/en-us/article/Show-or-hide-columns-and -rows-659c2cad-802E-44ee-a614-dde8443579f8