How to easily add drop-down menus in Google Sheets
Drop-down menus in Google Sheets allow the user to select specific options in a cell which controls what they input into the cells. It also makes it easier and quicker to input the data.
When you click on the cell, you will be presented with lovely looking options to click on.
Previously, to add a drop-down menu to a cell you had to select the Data Validation menu. This had a couple of problems, firstly, it wasn’t immediately obvious from the name that data validation allowed you to create drop-down menus. Secondly, the interface wasn’t very user-friendly and was based on the old Sheets styling.
Fortunately, there is a new easy way to add drop-downs to your cells. There are two ways to access this. First of all, select the range of cells you want to add drop-down menus to.
- Right-click on the cell or range you want to add the drop-down menu in and select “Drop-down” from the menu.
- Select the cell or range you want to add the drop-down menu in and click on the Insert menu and click on “Drop-down”.
This will add the drop-down menus to the cells you selected but you will need to set the options in them.
Data validation rules
It will also open the ‘Data validation rules’ sidebar, where you will set the options, colours, etc.
Apply to range — This shows the cells you have selected. Note, you can select an individual cell or a range of them to add multiple drop-down menus which have the same options. You can also change the range here.
Criteria — By default, the Drop-down is selected. You can change this to other types of data validation but in this post we’re focussing on the drop-downs. We’ll see later on an extra option for drop-downs.
Next, you have the options you want the drop-down menu to show. Type in the options, where it says Option 1, Option 2, etc. To add extra options, just click “Add another item”. To delete an option click on the trash can.
For each of the options, you can also set a colour.
You’re given 16 default colours but you can add your own ones by clicking on “Customise” and either selecting a colour or by entering a colour hex code.
Advanced options — You can choose to add help text, to help the user.
You can also choose whether only the options can be inputted into the cell, or whether you just show a warning flag if something different is written.
Display style — By default, the menus will use the “chip” style, but you can change it to just an arrow, or no menu icon by selecting “Plain text”. For this last option, you would need to double-click on the cell to see the options.
Finally, to remove the menus, click on “Remove rule”, which removes the data validation rule.
Click “Done” once you’ve finished editing them.
Getting options from a range of cells
Instead of entering the options manually in the sidebar, you can also refer to a range of cells in your spreadsheet.
To do this, in the Criteria sections, select “Drop-down (from a range)” and then enter the range or click on the grid icon and select the range on the sheet.
As an example, on a different sheet I have the four names I want to add to the menus.
Select them and click “OK”.
You will see in the Criteria will now show the four options.
Editing the menus
If you need edit the menus, click on the range of cells you want to edit which contain the menu and click on the pencil icon. This will open the sidebar again allowing you to edit them.
You can find more posts on Google Sheets here.
If you’d like to learn more check out my books on Google Workspace & Apps Script available on Amazon: https://amzn.to/3ehe4lP
