如何在Excel中将下拉列表添加到单元格

r囧r小猫 2021-07-26 19:13 796阅读 0赞

![00\_lead\_image\_creating\_drop\_down\_lists][00_lead_image_creating_drop_down_lists]

Drop-down lists are very useful data entry tools we see just about everywhere, and you can add custom drop-down lists to your own Excel worksheets. It’s easy and we’ll show you how.

下拉列表是非常有用的数据输入工具，我们随处可见，您可以将自定义下拉列表添加到自己的Excel工作表中。 这很容易，我们将向您展示如何操作。

Drop-down lists make it easier and more efficient to enter data into your spreadsheets. Simply click the arrow and select an option. You can add drop-down lists to cells in Excel containing options such as Yes and No, Male and Female, or any other custom list of options.

下拉列表使您可以更轻松，更有效地将数据输入电子表格。 只需单击箭头并选择一个选项。 您可以将下拉列表添加到包含选项(例如“是”和“否”，“男性”和“女性”)或任何其他自定义选项列表的Excel单元格中。

It’s easy to add a drop-down list to a cell in Excel, but the process is not intuitive. Drop-down lists are created using the Data Validation feature. We’re going to create a drop-down list with a selection of age ranges to show you how it’s done.

将下拉列表添加到Excel中的单元格很容易，但是过程并不直观。 使用数据验证功能创建下拉列表。 我们将创建一个包含年龄范围选择的下拉列表，以向您展示其效果。

To begin, enter the list of age ranges into sequential cells down a column or across a row. We entered our age ranges into cells A9 through A13 on the same worksheet, as shown below. You can also add your list of options to a different worksheet in the same workbook.

首先，将年龄范围列表输入到列中或行中的连续单元格中。 我们在同一工作表的A9到A13单元格中输入了年龄范围，如下所示。 您还可以将选项列表添加到同一工作簿中的其他工作表中。

![01\_entering\_items\_for\_drop\_down\_list][01_entering_items_for_drop_down_list]

Now, we’re going to name our range of cells to make it easier to add them to the drop-down list. To do this, select all the cells containing the drop-down list items and then enter a name for the cell range into the Name box above the grid. We named our cell range Age.

现在，我们将命名单元格的范围，以使其更轻松地添加到下拉列表中。 为此，选择包含下拉列表项的所有单元格，然后在网格上方的“名称”框中输入单元格范围的名称。 我们将细胞范围命名为Age。

![02\_naming\_cell\_range][02_naming_cell_range]

Now, select the cell into which you want to add a drop-down list and click the “Data” tab.

现在，选择要在其中添加下拉列表的单元格，然后单击“数据”选项卡。

![03\_clicking\_data\_tab][03_clicking_data_tab]

In the Data Tools section of the Data tab, click the “Data Validation” button.

在“数据”标签的“数据工具”部分中，单击“数据验证”按钮。

![04\_clicking\_data\_validation][04_clicking_data_validation]

The Data Validation dialog box displays. On the Settings tab, select “List” from the Allow drop-down list (see, drop-down lists are everywhere!).

显示“数据验证”对话框。 在“设置”选项卡上，从“允许”下拉列表中选择“列表”(请参阅下拉列表无处不在！)。

![05\_selecting\_list\_from\_allow][05_selecting_list_from_allow]

Now, we’re going to use the name we assigned to the range of cells containing the options for our drop-down list. Enter `=Age` in the “Source” box (if you named your cell range something else, replace “Age” with that name). Make sure the “In-cell dropdown” box is checked.

现在，我们将使用分配给包含下拉列表选项的单元格区域的名称。 在“源”框中输入`=Age` (如果您将单元格范围命名为其他名称，请用该名称替换“ Age”)。 确保选中“ In-cell dropdown”框。

The “Ignore blank” check box is checked by default. This means that the user can select the cell and then deselect the cell without selecting an item. If you want to require the user to select an option from the drop-down list, uncheck the Ignore blank check box.

默认情况下，“忽略空白”复选框为选中状态。 这意味着用户可以选择单元格，然后取消选择单元格而无需选择项目。 如果要要求用户从下拉列表中选择一个选项，请取消选中“忽略空白”复选框。

![06\_entering\_source][06_entering_source]

You can add a popup message that displays when the cell containing the drop-down list is selected. To do this, click the “Input Message” tab on the Data Validation dialog box. Make sure the “Show input message when the cell is selected” box is checked. Enter a Title and an Input message and then click the “OK” button.

您可以添加一条弹出消息，在选择包含下拉列表的单元格时显示。 为此，请单击“数据验证”对话框上的“输入消息”选项卡。 确保选中“选中单元格时显示输入消息”框。 输入标题和输入消息，然后单击“确定”按钮。

![07\_entering\_input\_message][07_entering_input_message]

When the cell containing the drop-down list is selected, you’ll see a down arrow button to the right of the cell. If you added an input message, it displays below the cell. The down arrow button only displays when the cell is selected.

选择包含下拉列表的单元格后，您将在该单元格的右侧看到一个向下箭头按钮。 如果添加了输入消息，它将显示在单元格下方。 仅当选择单元格时，才显示向下箭头按钮。

![08\_drop\_down\_list\_in\_cell][08_drop_down_list_in_cell]

Click the down arrow button to drop down the list of options and select one.

单击向下箭头按钮以下拉选项列表，然后选择一个。

![09\_selecting\_an\_option][09_selecting_an_option]

If you decide you want to remove the drop-down list from the cell, open the Data Validation dialog box as described earlier and click the “Clear All” button, which is available no matter which tab is selected on the dialog box.

如果您决定要从单元格中删除下拉列表，请如前所述打开“数据验证”对话框，然后单击“全部清除”按钮，无论该对话框上选择了哪个选项卡，该按钮均可用。

![10\_clearing\_drop\_down\_list][10_clearing_drop_down_list]

The options on the Data Validation dialog box are reset to their defaults. Click “OK” to remove the drop-down list and restore the cell to its default format.

数据验证对话框上的选项被重置为其默认值。 单击“确定”以删除下拉列表，并将单元格恢复为其默认格式。

![11\_clicking\_ok\_after\_clearing][11_clicking_ok_after_clearing]

If there was an option selected when you removed the drop-down list, the cell is populated with the value of that option.

如果在删除下拉列表时选择了一个选项，则将使用该选项的值填充单元格。

![12\_selected\_option\_in\_cell\_after\_clearing][12_selected_option_in_cell_after_clearing]

Follow this process to add drop-down lists to other cells as needed. You can use the same list for multiple drop-down lists. If you have many drop-down lists you need to add on a worksheet, you may want to put the lists of options on another worksheet in the same workbook. You can [hide the worksheet][] containing your lists of options to prevent them from being changed.

按照此过程，根据需要将下拉列表添加到其他单元格。 您可以将同一列表用于多个下拉列表。 如果您需要在工作表上添加许多下拉列表，则可能需要将选项列表放在同一工作簿中的另一个工作表上。 您可以[隐藏][hide the worksheet]包含选项列表[的工作表][hide the worksheet] ，以防止更改它们。

> 翻译自: [https://www.howtogeek.com/290104/how-to-add-a-drop-down-list-to-a-cell-in-excel/][https_www.howtogeek.com_290104_how-to-add-a-drop-down-list-to-a-cell-in-excel]

[00_lead_image_creating_drop_down_lists]: /images/20210726/09e145b9e3be475692b6d365135f6909.png
[01_entering_items_for_drop_down_list]: /images/20210726/080a914cfce44e939bad62b973e45290.png
[02_naming_cell_range]: /images/20210726/5375c4ecd0d84b7da518064426b985d0.png
[03_clicking_data_tab]: /images/20210726/890785e6b18a4dca9d928a607f674e02.png
[04_clicking_data_validation]: /images/20210726/e0f2898d5e824569a46de7c3dcecf220.png
[05_selecting_list_from_allow]: /images/20210726/b651e3b0c66345599875076b3432c4c6.png
[06_entering_source]: /images/20210726/e06b9f0213024c9f99f8ba0ecadb0156.png
[07_entering_input_message]: /images/20210726/575e0181b55544ea9b009b62a59f7b0e.png
[08_drop_down_list_in_cell]: /images/20210726/1a50cf3b456c4f8ab6e3e157f7e806bd.png
[09_selecting_an_option]: /images/20210726/01ad618173104b69b77514b7c3db4ab0.png
[10_clearing_drop_down_list]: /images/20210726/064afe736e5740dca203f3e8a024ab19.png
[11_clicking_ok_after_clearing]: /images/20210726/8e2cf6c6fc5e4ad9bfb1d9b904edabd6.png
[12_selected_option_in_cell_after_clearing]: /images/20210726/4d2713d7324b4dbcbb6653c715389c9a.png
[hide the worksheet]: https://www.howtogeek.com/218596/how-to-hide-worksheetstabs-and-entire-workbooks-in-excel/
[https_www.howtogeek.com_290104_how-to-add-a-drop-down-list-to-a-cell-in-excel]: https://www.howtogeek.com/290104/how-to-add-a-drop-down-list-to-a-cell-in-excel/