在Excel(Excel)中使用下拉列表可以大大减少将数据输入电子表格(enter data into a spreadsheet)所需的时间。值得庆幸的是，在Excel(Excel)中创建下拉列表非常容易。
有几种方法可以做到这一点，从简单到高级。您将在本文中学习所有方法。
在Excel中创建下拉列表(Drop Down List)：简单方法(Method)
在Excel(Excel)中创建下拉列表的最简单方法是手动输入列表。这对于您只需要一个单元格中的下拉列表并且不需要经常更新它的情况是理想的。
例如，如果您要向人们发送一个文件，您希望他们在其中填写一些字段，并且一个字段有一个简单的选项列表，那么这种方法是理想的。
使用简单方法创建下拉列表：
1. 选择要在其中创建列表的一个或多个单元格。
2.从菜单中选择数据，然后在功能区上选择数据(Data)验证(Data Validation)。
3. 这将打开一个数据验证(Data Validation)窗口。选择Allow(Allow)下的下拉列表，然后选择List。
4. 这允许您在列表中手动键入您想要的项目。只需在(Just)Source字段中键入每个项目，每个项目用逗号分隔。
5. 选择确定(OK)。现在您会看到您选择的单元格旁边有一个下拉箭头。当您选择此箭头时，您将看到您输入的所有项目都包含在此列表中。
虽然这是在Excel(Excel)中创建下拉列表的最快方法，但它也是最难维护的。修改列表需要返回验证设置并更新Source字段。
如果您在Excel(Excel)中创建大量此类下拉列表，则可能需要大量工作来修改它们。这就是为什么仅将此方法用于单个单元格和您不希望更改的列表的重要原因。
在Excel中创建下拉列表(Drop Down List)：使用范围
如果您想要更灵活的解决方案，您可以从Excel 中 的一系列其他单元格中为您的列表提取项目。(a range of other cells)
去做这个：
1.首先(First)，将列表中您想要的所有项目列出到任何单元格列中。您可以在当前电子表格或任何其他工作表上输入这些信息。
2. 重复上述过程以选择单元格并启动数据验证(Data Validation)窗口。将允许(Allow)字段设置为List。这一次，不要在Source(Source)字段中输入任何内容，而是选择该字段右侧的向上箭头图标。这是范围选择选项，可让您选择要从中提取列表项的字段。
3. 您将看到Data Validation窗口折叠，因此您可以看到整个工作表。将鼠标指针向下拖动到包含您要包含的所有列表项的整个单元格范围。完成此操作后，选择选择字段右侧的向下箭头图标。这将再次展开数据验证(Data Validation)窗口。
4. 您将看到您现在选择的范围出现在Source字段中。只需(Just)选择确定(OK)接受这些设置。
5. 现在，当您选择已配置为下拉列表的单元格右侧的下拉箭头时，您将看到刚刚选择的范围内包含的所有项目。
这种方法的好处是您可以通过修改范围中的任何单元格来更改该列表中的任何项目。您在此处所做的任何更改都将更新您在选择此范围作为源时创建的每个下拉列表。
当您想使用相同的列表项将大量单元格格式化(format a lot of cells)为下拉列表时，此方法是最佳选择。您可以让单个范围的内容控制所有这些列表的项目，而不管有多少。
将项目添加到您的列表
除了更改范围中的项目以更新列表之外，您还可以添加新项目。您将无法将项目添加到范围的末尾，因为您的范围选择受到您选择的第一个和最后一个单元格的限制。
相反，您需要在范围中间的某处插入一个新条目。Excel将动态更新您在数据验证设置中选择的范围，以包括您增加了一个单元格的新范围。
去做这个：
1. 右键单击源范围内的任何单元格，然后从下拉菜单中选择插入。(Insert)
2. 在小选择窗口中，选择Shift cells down，然后选择OK。这会将范围内的所有单元格向下移动一个，在您选择的位置插入一个空白单元格。
3. 在刚刚创建的空单元格中键入要添加的新项目。
现在，当您选择您创建的下拉列表单元格右侧的向下箭头图标(down arrow icon)时，您将看到刚刚在该范围中添加的新项目。
这是一种将新项目添加到下拉列表的简单方法，但它确实需要一些额外的步骤。它当然不会像在您的产品系列的末尾添加一个新项目那么简单。
如果您想这样做，您只需要更改将数据验证(data validation)配置到您的范围的方式。您可以在下一节中了解如何执行此操作。
将项目(Items)动态添加到您的列表中
为了更方便地将项目添加到下拉列表中，只需在配置范围的末尾键入新项目，您需要使用OFFSET 函数(function)。
选择下拉列表单元格后，从菜单中选择数据并从功能区中选择(Data)数据验证(Data Validation)。
在Data Validation窗口中，将 Source 更改为以下内容：
=OFFSET($E$1,0,0,COUNTA($E:$E),1)
更改公式中的(the formula)$E$1和$E:$E以使用您输入项目列表的列的字母。选择确定(Select OK)以确认此新的数据验证(Data Validation)配置。
以下是公式的工作原理：
- OFFSET函数中的 0 参数告诉它不要对列或行应用任何偏移量。
- COUNTA函数的输出告诉OFFSET函数范围的高度。
- COUNTA函数计算包含您的区域的列中非空单元格的数量。
现在，每当您向该列添加新值时，OFFSET函数的 height 参数都会增加 1，并且OFFSET函数会返回整个范围，并更新以包含您的新条目。
要查看此操作，只需在您的项目范围中添加一个新条目。选择下拉单元格右侧的下拉箭头，您将看到新条目出现在下拉列表中。
请记住，如果您的项目列表很长，您可能需要使用右侧的滚动条。
在 Excel 中删除下拉列表
最后，如果您想从您设置的任何单元格中删除下拉选项，只需选择该单元格并再次打开“数据验证(Data Validation)”窗口。
将Allow(Allow)下拉列表更改为Any 值(Any value)，然后选择OK。
更改此设置后，您会看到单元格将恢复正常。
在 Excel 中创建下拉列表
在Excel(Excel)中创建下拉列表很容易，您有很多选项可供选择。您选择的方法仅取决于您计划为一个项目列表启用多少个单元格，以及您希望项目列表有多灵活。如果您打算经常更改列表项，那么一定要使用动态方法。
How to Create a Drop Down List in Excel
Using a dropdown list in Excеl can greatly reduce the time it may take to enter data into a spreadsheet. Thankfully, it’s very easy to create a dropdown list in Excel.
There are a few methods to do this, from simple to advanced. You’ll learn every way to do it in this article.
Create a Drop Down List in Excel: Simple Method
The easiest way to create a dropdown list in Excel is by entering the list manually. This is ideal for situations where you only need a dropdown list in a single cell and you don’t need to update it frequently.
For example, if you’re sending people a file where you want them to fill out some fields, and one field has a simple list of choices, this method is ideal.
To create a drop down list using the simple method:
1. Select the cell or cells where you want to create your list.
2. Select Data from the menu, and select Data Validation on the ribbon.
3. This will open a Data Validation window. Select the dropdown list under Allow and select List.
4. This allows you to type the items you want in your list manually. Just type each item into the Source field with each item separated by commas.
5. Select OK. Now you’ll see that the cell you selected has a dropdown arrow next to it. When you select this arrow, you’ll see all of the items you typed out included in this list.
While this is the fastest method to create a dropdown list in Excel, it’s also the hardest to maintain. Modifying the list requires going back into the validation settings and updating the Source field.
If you create a lot of this kind of dropdown lists in Excel, it could take a lot of work modifying these. This is why it’s important to only use this method for single cells, and for lists that you don’t expect to change.
Create a Drop Down List in Excel: Using a Range
If you’d like a more flexible solution, you can pull items for your list from a range of other cells in Excel.
To do this:
1. First, list all of the items you want in your list into any column of cells. You can enter these on the current spreadsheet, or on any other sheet.
2. Repeat the process above to select the cell and launch the Data Validation window. Set the Allow field to List. This time, instead of typing anything into the Source field, select the up arrow icon to the right of this field. This is the range selection option that lets you select the field where you want to pull your list items from.
3. You’ll see the Data Validation window collapse so you can see the entire sheet. Drag the mouse pointer down the entire range of cells that include all of the list items you want to include. When you’re done doing this, select the down arrow icon to the right of the selection field. This will expand the Data Validation window again.
4. You’ll see the range you selected now appears in the Source field. Just select OK to accept these settings.
5. Now, when you select the dropdown arrow to the right of the cell you’ve configured as a dropdown list, you’ll see all of the items included from the range you just selected.
The nice thing about this approach is that you can change any of the items in that list just by modifying any cell in the range. Any change you make there will update every dropdown list you created where you selected this range as the source.
This method is best when you want to format a lot of cells into a dropdown using the same list items. You can have the contents of a single range control the items for all of those lists, and it doesn’t matter how many there are.
Adding Items to Your List
In addition to changing items in your range to update your lists, you can also add new items. You won’t be able to add an item to the end of the range, because your range selection is limited by the first and last cells you’ve selected.
Instead, you’ll need to insert a new entry somewhere in the middle of the range. Excel will dynamically update your range selection in the data validation settings to include the new range that you’ve increased by one cell.
To do this:
1. Right-click any cell in your source range, and select Insert from the dropdown menu.
2. In the small selection window, select Shift cells down, and select OK. This will shift all cells in the range down by one, inserting a blank cell where you’ve selected.
3. Type the new item you want to add into the empty cell you’ve just created.
Now, when you select the down arrow icon to the right of the dropdown list cell you created, you’ll see the new item you just added in the range.
This is an easy way to add new items to your dropdown list, but it does require a few extra steps. It certainly isn’t anywhere as simple as just adding a new item to the end of your range.
If you would like to do that, you’ll just need to change how you’ve configured the data validation to your range. You can learn how to do this in the next section.
Dynamically Adding Items to Your List
For a more convenient way to add items to your dropdown list just by typing the new item at the end of your configured range, you’ll need to use the OFFSET function.
With the dropdown list cell selected, select Data from the menu and Data Validation from the ribbon.
In the Data Validation window, change the Source to the following:
=OFFSET($E$1,0,0,COUNTA($E:$E),1)
Change $E$1 and $E:$E in the formula to use the letter of the column where you’ve entered your list of items. Select OK to confirm this new Data Validation configuration.
Here’s how the formula works:
- The 0 arguments in the OFFSET function tell it not to apply any offset to columns or rows.
- The output of the COUNTA function tells the OFFSET function the height of the range.
- The COUNTA function counts the number of cells that aren’t empty in the column that contains your range.
Now, whenever you add a new value to that column, the height parameter of the OFFSET function increases by one, and the OFFSET function returns the entire range, updated to include your new entry.
To see this in action, just add a new entry to your range of items. Select the dropdown arrow to the right of your dropdown cell, and you’ll see your new entry appear in the dropdown list.
Keep in mind that you may need to use the scroll bar on the right if your list of items is long.
Removing a Drop Down List in Excel
Finally, if you ever want to remove the dropdown option from any of the cells where you’ve set this up, just select that cell and open the Data Validation window again.
Change the Allow dropdown to Any value and select OK.
Once you change this setting, you’ll see that the cell will turn back to normal.
Creating Drop Down Lists in Excel
Creating a dropdown list in Excel is easy, and you have plenty of options to choose from. The method you choose just depends on how many cells you plan to enable with one list of items, and how flexible you’d like the list of items to be. If you plan to change the list items frequently, then definitely use the dynamic approach.
