Google 表格是一个免费的基于网络的应用程序,是(Google Sheets)Microsoft Excel的流行替代品。该工具允许轻松创建、更新和修改电子表格。它是一种出色的协作工具,可让您根据需要添加任意数量的人员,并与其他人同时编辑Google 表格(Google Sheets)。无论您身在何处,在线工具都能让您在同一个电子表格中实时共同处理一个项目。要在Google 表格(Google Sheets)上进行协作,只需单击“共享(Share)”按钮,您就可以让您的朋友、同事或家人编辑您的电子表格。
在处理共享的Google 电子表格(Google Spreadsheet)时,您可能希望其他用户仅在其单元格中输入受限数据。为了避免其他人在单元格中输入错误的值,您可能需要添加一个图形控制元素,如下拉菜单(drop-down menu),它类似于允许人们仅输入给定列表中可用的值的列表框。除此之外,下拉列表是一种智能且更有效的数据输入方法。
话虽如此,下拉列表或下拉菜单是一种简化的方式,可确保人们仅以与您期望的方式完全相同的方式将值填充到您的单元格中。与Excel一样, Google 表格(Google Sheets)允许您轻松地为表格创建下拉菜单。此外,它使您可以对下拉列表进行部分更改,以防您要修改单元格中的选择列表。在本文中,我们将详细说明如何在Google 表格(Google Sheets)中创建下拉菜单并对其进行修改。
在Google(Google)表格中创建下拉菜单
启动(Launch)Google 表格
打开一个新的电子表格或打开一个现有的电子表格文件。
选择要在其中创建下拉列表的单元格。您还可以选择一组单元格、一整列或一行。
导航到表格(Sheets)菜单,然后单击选项数据。(Data.)
从下拉菜单中选择数据验证。( Data Validation)弹出一个数据验证窗口,其中包含几个自定义选项。(Data Validation)
数据验证(Data Validation)窗口中的第一个字段是单元格范围(Cell range),它根据选定的单元格自动填充。您只需单击“单元格范围(Cell Range)”字段中的表格图标即可将范围更改为新值。
Data Validation窗口中的第二个字段是Criteria,它在其自己的下拉菜单中包含各种选项的列表。标准(Criteria)包含范围列表、项目列表、数字、文本(List from a range, List of items, Number, Text, )和日期等选项。 ( Date. )
- 范围列表:此选项允许您创建从不同工作表中检索到的值列表或来自同一工作表上不同单元格的值列表。
- 项目列表:这允许我们创建文本值列表。它们在以逗号分隔的编辑字段中输入。
- Number:此选项(Option)不会创建下拉列表,而是确保下拉菜单的条目落在特定的数字范围内。
- 文本:此选项(Option)不会创建下拉列表,而是确保条目采用正确的文本格式。
- 日期:此选项(Option)不会创建下拉列表,而是确保输入的日期是否有效或在特定范围内。
- 自定义公式(Custom Formula)是:此选项不会创建下拉列表,而是确保所选单元格使用用户指定的公式。
输入要包含在列表中的数据后,选择在单元格中显示下拉列表(Show dropdown list)选项。选择此选项可确保值出现在单元格中。
您还可以通过选择带有单选按钮的选项来选择当有人输入列表中不存在的无效数据时必须执行的操作。您可以选择在无效数据上显示警告(Show warning)选项或拒绝输入选项。(Reject input)拒绝输入(Reject Input)选项不允许您输入下拉列表中不存在的任何值。另一方面, 显示警告(Show warning)选项允许您输入不在列表中但在工作表中显示警告消息的无效数据。
设置窗口中的最后一个选项是外观。(Appearance.)此选项向用户提示他们可以在单元格中输入哪些值或数据。要激活此助手,请选择外观(Appearance)字段旁边的选项显示验证帮助文本。(Show validation help text)选择该选项后,键入要向人们提供有关他们可以在单元格范围内选择哪些值的说明。
单击保存(Save)以应用更改。
编辑Google(Google)表格中的下拉列表
要向列表中添加更多值或从下拉菜单中删除项目,请按照以下步骤操作
导航到数据( Data)并从下拉菜单中选择数据验证。( Data validation)
选择单元格并更改条目中的项目。
单击保存(Save)以应用更改。
就这样。
How to create and modify a drop-down list in Google Sheets
Google Sheets is a free web-based application and a popular alternative to Microsoft Excel. The tool allows to easily create, update, and modify spreadsheets. It serves as an excellent collaborative tool allowing you to add as many people as you want and edit Google Sheets with others at the same time. The online tool enables you to work jointly in real-time on a project in the same spreadsheet irrespective of wherever you are located. To collaborate on Google Sheets one has to simply click a Share button and there you are allowing your friends, colleagues, or family to edit your spreadsheet.
When working on a shared Google Spreadsheet, you may want other users to enter only restricted data within its cells. To avoid others entering wrong values within cells, you may want to add a graphical control element like the drop-down menu which is similar to a list box that would allow people to enter only those values available from a given list. Apart from that, the drop-down list serves as a smart and more efficient method to enter data.
That being said, a drop-down list or a pulldown menu is a streamlined way to ensure that people fill only the values into your cells in the exact same manner as your expecting. Like Excel, Google Sheets allows you to easily create a drop-down menu to your Sheets. Additionally, it enables you to make partial changes to the drop-down list in case you want to modify the selection list in the cells. In this article, we explain in detail how to create a drop-down menu in Google Sheets and modify the same.
Create a drop-down menu in Google Sheets
Launch Google Sheets
Open a new spreadsheet or open an existing spreadsheet file.
Select a cell where you want to create a drop-down list. You can also select a group of cells, an entire column, or a row.
Navigate to Sheets menu and click on the option Data.
Select Data Validation from the drop-down menu. A Data Validation window pops up with several options to customize.
The first field in the Data Validation window is the Cell range which is automatically filled based on the selected cells. You can change the range to a new value by simply clicking on a table icon in the Cell Range field.
The second field in the Data Validation window is the Criteria which has a list of various options in its own drop-down menu. The Criteria contains options like List from a range, List of items, Number, Text, and Date.
- List from a range: This option allows you to create a list of values that have been retrieved from different sheets or the list of values from different cells on the same sheet.
- List of items: This allows us to create a list of text values. They are entered in the edit field separated by commas.
- Number: This Option doesn’t create a drop-down list instead makes sure that the entry to the drop-down menu falls within a specific numeric range.
- Text: This Option doesn’t create a drop-down list instead makes sure that the entry is in a proper textual format.
- Date: This Option doesn’t create a drop-down list instead makes sure if the date entered is valid or comes in a certain range.
- Custom Formula is: This option does not create a drop-down list instead makes sure the selected cell uses a user-specified formula.
Once the data that is to be included in the list is entered, select the option Show dropdown list in cell. Selecting this option ensures that the values appear in the cells.
You can also choose what has to be done when someone enters an invalid data which is not present on the list by selecting the options with a radio button. You can choose to have either Show warning option or Reject input option on invalid data. The Reject Input option doesn’t allow you to enter any value which is not present on your drop-down list. On the other hand, Show warning option allows you to enter the invalid data which is not on your list but displays the warning message in the sheet.
The last option in the setting window is the Appearance. This option gives a hint to the user on what values or data they can enter in the cells. To activate this assistant, select the option Show validation help text beside the Appearance field. After you select the option, type the instructions that want to give people about what values they can choose within the cell range.
Click Save to apply the changes.
Edit the drop-down list in Google Sheets
To add more values to the list or remove items from the drop-down menu follow the below steps
Navigate to Data and select Data validation from the drop-down menu.
Select the cells and change the items in the entry.
Click Save to apply changes.
That’s all.