Adding a drop-down list to a spreadsheet in Sheets is a way of limiting what can be entered into a specific cell or cells. It is a way of ensuring that the data entered is correct, as the user has to choose their entry from a drop-down list (for example, January, February, March etc.). There will be fewer mistakes entering data, if users are restricted with the sort of data they can actually enter. This is helpful particularly for shared sheets.
How to add a drop-down in Google Sheets (using the example below)
In the example above, I want to add a drop-down list to column B, that allows the user to choose a day of the week. (Monday – Friday). I also want to allocate their department – the five different departments are listed in column G.
Typing in a List
So, highlight cells B2 to B7.
Click on the Data Tab, and choose Data Validation.
Make sure the cell range here is correct. (If it’s not, click on the select data range icon, highlight the cells you want to add a drop-down to, then click OK)
Click on the drop-down next to Criteria and choose List of Items.
Type in the options you want to display in your drop-down, separated by commas. (For example, Monday,Tuesday,Wednesday,Thursday,Friday)
Make sure there is a tick in the Show drop-down list in cell box
Decide how you want to handle invalid data. If you don’t want any invalid data entered (and I don’t!) choose the Reject Input option. If you think there might be a time when someone needs to input an option that isn’t part of the drop-down list, then choose Show Warning. This means that the user will be warned that they are entering data that isn’t expected.
Finally, you can add some validation help text. (For example “Choose day from list please” or “Click and enter a value from the list of items”)
Click on Save.
You should now see your list when you click on the drop-down in cell B2.
If you try and enter incorrect data, as soon as you press enter you’ll see the help text appear:
Using an Existing List
We are now going to add a dropdown to the Department Column. But this time, we don’t have to type in the departments – they are listed in column G.
So, highlight cells D2 to D7.
Click on the Data Tab, and choose Data Validation.
Make sure the cell range here is correct. (If it’s not, click on the select data range icon, highlight the cells you want to add a drop-down to, then click OK)
Click on the drop-down next to Criteria and choose List from a Range.
You now have to select the data range. So select G2 to G6 (the list of departments) and click OK.
Note – Sheets will make this range absolute for you. (This means the references are fixed).
Click on Save.
You should now see a drop-down for department.
Note – if you update any of your departments, the drop-down list will update automatically. In the example below, I changed Marketing to Advertising & Marketing. The drop-down automatically updated.
You can also insert a new row into your table of departments (by going to the Insert tab, and choosing Cells and Shift Down) and add another department. The drop-down will automatically update and include your new department. However, if you try and add a new department onto the end of the list, it won’t automatically be included (you’ll have to update the Data Validation to include it).
How to add a drop-down in Google Sheets
Time needed: 3 minutes
How to add a drop-down list in Google Sheets
- Select the cell(s) that you want to add validation to
- Click on the Data tab
- Choose Data Validation
- Make sure the cell range is correct
(If not, click on the icon, highlight the cells you want to add a drop-down to, then click OK)
- Choose the criteria (is there a list already in a spreadsheet or do you want to type in a list)
- Make sure that “Show drop-down list in cell” is ticked
- Decide whether you want to just warn the user if they have inputted incorrect data (but it will still be accepted), or whether to reject any incorrect data
- Choose if you want any validation help text
(For example “date must be within the range Jan-Jun 2021” or “Choose from the list of departments”)
- You have created a drop-down list! 🙂
How to remove a drop-down list
To remove a drop-down list:
- Highlight the cells containing the validation
- Do to the Data Tab, and choose Data Validation
- Click on Remove Validation
- Validation will be removed (but any text already entered will still be there)
Video Tutorial of adding a drop-down list in Sheets
To read more tutorials on Google Sheets, click here or to see more videos, just click here. Thank you!