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)

Example spreadsheet

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.

Highlight column

Click on the Data Tab, and choose Data Validation.

Data, 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)

Select Range

Click on the drop-down next to Criteria and choose List of Items.

Select Criteria

Type in the options you want to display in your drop-down, separated by commas. (For example, Monday,Tuesday,Wednesday,Thursday,Friday)

Type in List

Make sure there is a tick in the Show drop-down list in cell box

Show drop down list in cell

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.

Reject Input

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.

Save

You should now see your list when you click on the drop-down in cell B2.

drop-down list in Sheets

If you try and enter incorrect data, as soon as you press enter you’ll see the help text appear:

Help text

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.

List from range

You now have to select the data range. So select G2 to G6 (the list of departments) and click OK.

Select data range

Note – Sheets will make this range absolute for you. (This means the references are fixed).

Click on Save.

From a list

You should now see a drop-down for department.

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

  1. Select the cell(s) that you want to add validation to

  2. Click on the Data tab

  3. Choose Data Validation

  4. 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)

  5. Choose the criteria (is there a list already in a spreadsheet or do you want to type in a list)

  6. Make sure that “Show drop-down list in cell” is ticked

  7. 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

  8. 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”)

  9. 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)
Remove validation

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!