How to Create a Drop-down list in Google Sheets

Creating a drop-down list in Google Sheets can serve many different purposes depending on what you’re looking to achieve. The good news is that they’re super easy to make with Data Validation and even easier to use.

We’ll take a look at the example used in the previous tutorial where we looked at How to Bulk Translate with Google Sheets, as this is probably the most common scenario.

We're going to select a country from a drop-down list that's extracted from a list of countries on another sheet. In this example, it will enable our Google Translate function to translate some text from English to the chosen language from the country that has been selected from the drop-down list.

We have two sheets; the main sheet where we can enter text to be translated and the second sheet which contains the list of countries we would like to collect data from to display in our drop-down list.

Data Validation

Drop-down lists are created using Data Validation, so let's begin by placing your cursor on the cell you would like to have the drop-down list located. In this case, the cell B2, then click the Data option in the menu at the top and choose Data Validation.

Alternatively, another way to get to the Data Validation is by right-clicking on the highlighted cell and then choosing ‘View more cell actions’ > ‘Data validation’.

Both ways will get you to the same place. You’ll see this pop-up window appear where we can set up our list.

Data Validation Criteria

Under the Criteria heading, if you click this drop-down, you’ll see a bunch of options to choose from, including the possibility to select a Date.

The Date allows you to add Data Validation to enter dates, which, I have to say, is really useful, but for now, we’re interested in the option ‘List from a range’, which is selected by default.

Next to the Criteria drop-down field is where we will enter our range. The range is where we will collect the list of countries to display in our drop-down menu.

We’ll need to click on the little icon of a grid to choose our range. When you click this icon, another pop-up will appear asking us to select a data range.

At this point, we simply click on the second tab in the spreadsheet called Country List, or if you had the list somewhere else on the same sheet, you’d go there instead. The pop-up window will remain open as you move across to the second sheet.

To display the list in the drop-down correctly, we must only choose the items we want to show there; therefore, we’re not interested in the header row, only the countries themselves.

Pro Tip: The best thing to do with long lists like this example is simply select the first few rows, as you can see below. Click on the first cell in the range, drag your mouse down three or four rows, and then let go.

Once you’ve selected them, we can just make a small change to the range to make it an open-ended range. Right now, we have this selected:

'Country List'!A2:A5

Open-Ended Range

If we drop the ‘5’ off the end to make it 'Country List'!A2:A, the range will extend to the bottom of the sheet.

Creating an open-ended range has many benefits; it saves time and avoids dragging your mouse endlessly to find the bottom. Plus, since the range will now go all the way to the bottom of the sheet, you could add more items to this list if you need to; the drop-down menu will automatically collect them.

Now that we’ve selected our list of countries, we can click the ‘OK’ button to accept it. You’ll be taken back to the Data Validation window again, which now shows our selected open-ended range.

'Country List'!A2:A

A tick box is already checked in this Data Validation window to ‘Show drop-down list in cell’, which is precisely what we need, so leave that checked.

Invalid Data

At this point, you have a few optional things to consider. The options shown next to ‘On invalid data’ is currently set to ‘Show a warning’, but you can change it to ‘Reject input’ if you wish. Leaving it as ‘Show a warning’ means that if you or someone else who has access to the Spreadsheet types something into this drop-down field that is not found in the country list, a warning will appear. You can modify the warning message by checking the Appearance checkbox.

The ‘Reject input’ option won’t allow you to enter something that doesn’t belong in the country list. However, with that said, the ‘Reject input’ option is the safest bet. Again, it’s optional, and it really depends on how important the result is to determine whether you should use a warning or rejection. Personally, I typically just leave it as a warning most of the time. It’s one less thing to think about : )

That’s it, just hit the ‘Save’ button, and you’re done. If you jump back over to the first sheet, you will see the drop-down menu has been added.

Viewing a Drop-down list

When you click on the little grey triangle, the list will expand to display all the options from the other sheet where the countries are stored.

If you type something into this drop-down field, as mentioned earlier, that does not exist in the country list; you’ll see the warning message appear.

To find something in the list, you can either start typing out the country name to filter the results as shown below or just scroll down in the list and find what you're looking for manually.

Here’s what the scroll looks like:

Ta-da, a dynamic drop-down list!

Remove Data Validation

If you want to remove this for whatever reason, the process is exactly the same to bring up the Data Validation window; just ensure your cursor is highlighted on the drop-down cell. Then, when the Data Validation window appears, just click the ‘Remove validation’ button and say farewell.

That wraps up this tutorial, but in case you’re wondering, we’ll explore dependent drop-down lists in another post. These are great as the lists will dynamically change depending on what was selected in another drop-down list. Stay tuned.

Here is the Google Translate Tool used in this tutorial; feel free to make a copy and use it as you wish, or have a go and make one yourself.

As mentioned earlier, to put context to this concept, you should check out this post on How to Bulk Translate with Google Sheets.