How to Create Google Sheets Drop Down List

Google Sheets drop down lists are very easy to create and offer amazing utility. With a dropdown list, you can forget about entering wrong values in a cell and speed up your work. It becomes even more convenient when the document is shared with multiple people since the chances of entering wrong data increases.

The drop-down list feature allows you to either show a warning on invalid inputs or reject the input altogether. Your productivity also increases many times as you don’t have to manually type the same things over and over again

Not only are we going to show you how to create a drop down list, but we’re also going to discuss different methods to do so. In the end, we’re going to show how you can make the cleanest dropdown menus too.

How to create in-cell drop down list

There are two distinct ways you can use to create a dropdown list. The first is one is by creating a ‘List of items’ and the second one is by creating a ‘List of range’.

The first method lets you create a predefined list of options. The only drawback to this is that it’s static which means the list won’t update itself with new items.

Create a list of items

To create a static list, follow these steps:

Open your your spreadsheet and select the range of cells you want to have an in-cell list in. Then right-click and scroll to the bottom of the menu and select ‘Data validation’.

From the window, select the menu next to ‘Criteria’ and select List of items. Then a new box will appear to the right of the menu labeled ‘Enter items separated by a comma’ in which you can enter your values.

Simply enter your values here and separate them with a comma. Also be careful about not adding spaces between them. While it may not generate an error, spaces are automatically ignored and removed once you click Save.

We added 3 options in our list: Yes, No, and Maybe.

To have these options show as a dropdown menu in the cell, make sure to check the ‘Show dropdown list in cell’ option. If that is left unchecked, users will have to manuall type in the cell.

For invalid data, you have two options: Either show a warning or reject the input. You can choose from either option with the help of the radio buttons. If you check the ‘Show validation help text’ option, you can type in the text that the user will see when they click and hover over the cell.

Once you’re satisfied, click Save.

To use your new list, simply click the downward facing arrow in each cell and select from the options.

This option is good if you have a fixed number of options. But, if you want your options to come from a dynamic list, the second option will work better for you. Read below for it.

Create a list from a range of cells

The second way you can create a dropdown list is from a range of cells. This is especially useful if you have a large number of options that you want in your list and/or your options are changing frequently.

If you want to try this in the same spreadsheet, open up a new sheet by clicking the ‘+’ button on the bottom-left corner of the spreadsheet. Otherwise, create a new spreadsheet from the File menu.

Now, add all your options into a column of the new sheet. In this example, our options are going to be the numbers from 1 to 20. In column C, we’ve selected 7 cells that we want to contain the drop-down list. Right-click the cells and open up the Data validation menu again.

Select ‘List from a range’ in the Criteria box. A new box to the right will appear in which we can enter the range of cells we want to use. In this case, the range will be A2:A21. Before the range, we have to specify from which sheet we want to extract the values from. Therefore, we write the name of the sheet, followed by an exclamation mark (!), and then type the range.

We’re keeping the other settings as default. Click save and you should get the following results. To test if everything is correct, simply click the arrow in any of the cells and confirm if they show the numbers between 1-20.

Add entire column to range

In the above example, we had 20 cells from column A to be our options. What if we wanted to increase our options and have the drop down menu automatically show them if we make edits? Well, you’d be surprised how easy it is to do so.

Select the 7 cells in column C and open the Data validation menu. Simply change ‘A21’ to ‘A’ and now the entire column can be fetched in the options.

To test this change, make any change you want to any cell in column A and watch it magically appear in the drop down list for column C.

Drop down list from another sheet

To make the above example much neater, we can place our options in column A in a different sheet. This has 2 advantages: it keeps your spreadsheet tidy AND prevents anyone from mistakenly editing the options column.

Create a new sheet by clicking the ‘+’ button as before, and cut column A from the previous sheet, and paste it into the new one. To make things cleaner, move column C in place of column A. If you followed our steps from the beginning, you don’t have to make any changes as they are applied when you made the edits.

If you’re following only this section, select the cells you want to have dropdown menus in and open the Data validation tab and type ‘Sheet3!A2:A’ in the right-most box in the Criteria row as below.

As you can see, we have our dropdown cells in Sheet2, while we’re reading the options from Sheet3. Do remember that this practice is possible only with sheets within a single spreadsheet. You can’t do this between different spreadsheets.

What other Google Sheets tricks and tips do you want us to cover? Do let us know in the comments! If you liked this article, be sure to check out how you can add checkboxes to in Sheets and make them interactive.