如果您正在创建交互式电子表格，您可能需要一个下拉列表，以便用户可以在选项之间进行选择。为此，您可以按照本教程在 Microsoft Excel 或 Google 表格中创建一个下拉列表(create a drop-down list in Microsoft Excel or Google Sheets)。您可以在本指南的帮助下创建单个下拉菜单和嵌套下拉菜单。
与各种编程语言一样，也可以在Excel(Excel)电子表格中包含 if-else 语句。假设您正在为应该根据各种标准选择不同选项的人创建一个电子表格。在这种时候，明智的做法是使用下拉列表，这样您就可以为人们提供多个选择。
如何在Excel(Excel)中创建下拉列表
要在Excel(Excel)中创建下拉列表，请按照以下步骤操作 -
- 选择要在其中显示下拉菜单的单元格。
- 转到数据 > 数据验证。
- 从允许(Allow)菜单中选择列表。
- (Write)在“来源(Source)”框中写下您的选项。
- 保存更改。
首先，您需要在电子表格中选择要显示下拉列表的单元格。之后，从主页(Home )选项卡切换到数据(Data )选项卡。在数据工具(Data Tools)部分，单击数据验证(Data Validation )按钮，然后再次选择相同的选项。
现在，展开Allow下拉列表，然后选择List。然后，您需要一个接一个地写下所有选项。如果要显示 AA、BB 和 CC 作为示例，则需要像这样编写它们-
AA,BB,CC
无论您要提供多少选项，都需要用逗号分隔。完成后，单击确定按钮。现在，您应该找到一个像这样的下拉列表-
您也可以添加错误消息。当用户尝试输入与给定选项不同的值时，它会出现。为此，请切换到错误警报(Error Alert )选项卡，然后写下您的消息。按照本教程在 Excel 中添加错误消息(add error messages in Excel)。
如何在Excel(Excel)中创建嵌套下拉列表
如果您想从一些现有的下拉菜单或单元格中获取数据并在不同的单元格中相应地显示选项，您可以执行以下操作。
您需要打开相同的数据验证(Data Validation )窗口并在允许(Allow )菜单中选择列表。(List)这一次，您需要在Source框中输入一个范围，如下所示 -
=$A$1:$A$5
根据此范围，新的下拉列表将显示写入 A1 到 A5 单元格中的相同选项。
如何在Google(Google)表格中创建下拉列表
要在Google 表格(Google Sheets)中创建下拉列表，请按照以下步骤操作 -
- 选择一个单元格并转到Data > Data验证。
- 选择项目列表。
- 写下您的项目或选项。
- 保存更改。
首先，在电子表格中选择一个单元格，然后单击顶部导航栏中的数据(Data )。之后，从列表中选择数据验证(Data validation )选项。
现在，展开Criteria下拉菜单，然后选择List of items。接下来，您需要在空框中写下所有选项或项目。
最后，单击“保存(Save )”按钮以在单元格中显示下拉列表。
与Excel一样，Google 表格(Google Sheets)会在输入无效数据时显示警告或错误消息。默认情况下，它会显示警告消息并允许用户编写自定义文本。如果要防止用户输入无效数据，则需要在数据验证(Data validation)窗口中选择拒绝输入(Reject input )选项。
如何在Google(Google)表格中创建嵌套下拉列表
它与Excel(Excel)几乎相同，但选项的名称不同。您需要从Criteria列表中选择(Criteria )List from a range选项，然后根据需要输入一个范围。您可以输入这样的字段-
=$A$1:$A$5
它将在此下拉列表中显示从 A1 到 A5 单元格的所有文本。
就是这样！希望它会帮助你。
How to create a drop-down list in Excel and Google Sheets
If уou аre creating an interactive spreadsheet, you may need a drop-down list so that users can choose between options. For that, you can follow this tutorіal to create a drop-down list in Microsoft Excel or Google Sheets. You can create a single as well as a nested drop-down menu with the help of this guide.
Like various programming languages, it is possible to include the if-else statement in an Excel spreadsheet as well. Let’s assume that you are creating a spreadsheet for people who should select different options according to various criteria. At such a moment, it is wise to use a drop-down list so that you can provide more than one choice to the people.
How to create a drop-down list in Excel
To create a drop-down list in Excel, follow these steps-
- Select a cell where you want to show the drop-down menu.
- Go to Data > Data Validation.
- Select the List from the Allow menu.
- Write down your options in the Source box.
- Save your change.
To get started, you need to select a cell in your spreadsheet where you want to show the drop-down list. After that, switch from the Home tab to the Data tab. In the Data Tools section, click the Data Validation button, and select the same option again.
Now, expand the Allow drop-down list, and select List. Then, you need to write down all the options one after one. If you want to display AA, BB, and CC as the examples, you need to write them like this-
AA,BB,CC
No matter how many options you want to provide, you need to separate them by a comma. After going that, click the OK button. Now, you should find a drop-down list like this-
You can add an error message as well. It appears when users try to enter a different value other than the given options. For that, switch to the Error Alert tab, and write down your message. Follow this tutorial to add error messages in Excel.
How to create a nested drop-down list in Excel
If you want to obtain data from some existing drop-down menus or cells and display options accordingly in a different cell, here is what you can do.
You need to open the same Data Validation window and select List in the Allow menu. This time, you need to enter a range in the Source box like this-
=$A$1:$A$5
According to this range, the new drop-down list will show the same options that are written in the A1 to A5 cells.
How to create a drop-down list in Google Sheets
To create a drop-down list in Google Sheets, follow these steps-
- Select a cell and go to Data > Data validation.
- Select the List of items.
- Write down your items or options.
- Save your change.
At first, select a cell in a spreadsheet and click the Data from the top navigation bar. After that, select the Data validation option from the list.
Now, expand the Criteria drop-down menu, and select List of items. Next, you need to write down all the options or items in the empty box.
At last, click the Save button to show the drop-down list in a cell.
Like Excel, Google Sheets shows a warning or error message for entering invalid data. By default, it shows a warning message and allows users to write custom text. If you want to prevent users from entering invalid data, you need to choose the Reject input option in the Data validation window.
How to create a nested drop-down list in Google Sheets
It is almost the same as Excel, but the name of the option is different. You need to select the List from a range option from the Criteria list and enter a range according to your needs. You can enter a field like this-
=$A$1:$A$5
It will show all the texts from A1 to A5 cells in this drop-down list.
That’s it! Hope it will help you.
