Google Sheets is an online spreadsheet platform that works as an efficient alternative to Microsoft Excel, providing several additional tools to make your workflow even smoother. It also features unique options that enable users to do much more than conventional spreadsheet software.
One such useful tool available in Google Sheet is a drop-down list, and here we explain how to create, edit and delete one on your PC or Android and iOS app.
Create a drop-down list in Google Sheets (PC)
If you want to create a drop-down list in Google Sheet while working on a computer, follow the steps mentioned below.
Step 1: Open Google Sheets and then sign in with your Google Account. After that, click on Blank to start a new spreadsheet. Step 2: Now, click on the cell where you want to place the drop-down list.Step 3: After you have clicked on the cell, select the Data option from the Sheets menu. From the drop-down menu, select Data validation. Step 5: In this option, you will see various configurations, which are explained.
- Cell Range: This is the location where the drop-down list will be placed. Usually, this is the cell reference to the current cell that you have clicked.
- Criteria: Criteria contains several other configurations such as:– List from a range, which will retrieve values from a specific range of cells that can either be from the current sheet or some other sheet of the same workbook. Similarly, when you select
– List of items option, you can write down a list of your own separated by a comma and without space.
– Number option will let you enter a range of numbers with a range set by you.
– Text option enables the user to control the text, URL or an email in the selected cell.
– Date option is similar to the previous two and lets you control the range of date that you will enter in the cell.
– Custom formula is option enables you to customise the cell to utilise the formula that you have selected.
– Tick option lets you add a tick mark in the aforementioned cell.
Note that the Number, Text, Date, Custom formula is, and Text option won’t create a drop-down list but rather perform the functions as previously mentioned.
- Show drop-down list in the cell: If you have selected List from a range or List of items option from above which will provide a drop-down list, this option will automatically be displayed. Tickmark the option if you want the end-user to see and choose the options from the list. If not, you can untick this option.
- On invalid data: Here, the users can specify what message will be displayed when an invalid range is inserted in the cell. Users can select the Show warning or Reject input option. The former will show a warning while the latter will reject the input altogether.
- Appearance: Click on the box beside Show validation help text to allow the end-user to see a validation text that will appear if they have entered the correct data. You can also change the text.
Step 6: When you are done, click on the Save button. Also read: How to find circular reference in Excel?
Create a drop-down list in the Google Sheets app (Android and iOS)
- Open Google Sheet and then tap on the Plus icon at the bottom to start with a new spreadsheet.
- Tap on the cell and then tap on the three-dot button at the top right of the screen.
- From the drop-down list, select Data Validation.
- Here, you will see a list of options which are more or less the same as we have mentioned in the heading above. Most of these options are understandable by name and user will have no difficulty in configuration the settings. To add items tap on the drop-down menu in Criteria and then select List of items. After that, tap on Add to add the items one by one.
- When you are done, tap on Save at the top right of the screen.
Also read: How to display and hide formulas in Excel?
Delete or change a drop-down list
You can delete the drop-down list in computer and the app.
Delete or change drop-down list in Google Sheets (PC)
- Select a cell or a range of cells where you want the deletion.
- Click on Data and then select Data Validation from the drop-down list.
- If you want to change the drop-down list, select the Criteria option. In case, you want to delete the list, click on Remove Validation at the bottom.
- Click on Save.
Delete or change drop-down list in Google Sheets app (Android and iOS)
- Open Google Sheet and then tap on a cell or a range of cells where you want to modify or remove the drop-down list.
- Tap on the three-dot button at the top right of the screen and then select Data Validation from the drop-down.
- To change the list, tap on Criteria and then make the required changes. In case you want to delete the drop-down list, tap on Remove Rule from the bottom right.
Also read: How to record a macro in Excel?