Drop-Down Select Boxes in Google Sheets

There are some occasions when you would like to limit the possible values that can be entered into in a Google Sheets spreadsheet cell to a list of specific, pre-defined values.

Those values can themselves be conveniently stored in another range on the same, or a different sheet.

Suppose we have this spreadsheet, in which we want to record the name of the person leading a particular project:

Click image to enlarge

Click image to enlarge

We want to restrict the names that can be entered into cell B1 to a list of valid possibilities. These are stored, in this example, in a range on a second sheet:

Click image to enlarge

Click image to enlarge

First, let’s name this range for convenience. Highlight the range, right-click and choose Name and protect range …, then choose a handy name for the range:

Click image to enlarge

Click image to enlarge

Here I’ve called the range ProjectLeaders. I should probably protect it too, by checking the Protect checkbox, though that’s optional.

Returning now to Sheet1, I can right-click in the cell B1 and select Data Validation…. Here’s the dialog that opens:

Click image to enlarge

Click image to enlarge

As you can see, in the Criteria line I’ve used the option to List from a range and entered the name of our range containing the Project Leaders’ names. Once these options are saved, cell B1 has a down-arrow at the right hand side, which, when clicked, opens a drop-down menu:

Click image to enlarge

Click image to enlarge

We can now only select a valid entry from those provided.