How To Create Drop Down List In Google Sheets

Here’s how to create a drop-down list in Google Sheets

How To Create Drop Down List In Google Sheets

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

When you create a drop-down list in Google Sheets, you can ensure that users are only allowed to select from a list of already defined options. This can be essential for creating consistent data that is easy to use. Instead of allowing them to type a variation of an answer, only the options you decide on should be chosen.

Let’s take a look at how you can do this today!


Using A Range Of Cells

If you already have the data inputted on your Google Sheet spreadsheet then you can select it and then decide where you want to put the drop-down list.



Select Cells

First, ensure the right amount of cells for your drop-down list to contain.

For instance, maybe you want to select four months in the middle of the year and allow four people to choose which month is their favorite to go on a summer vacation. With that in mind, the drop-down list will need a total of four cells.



Data Validation

Click on the ‘Data’ option, then click on ‘Data validation’ in the menu that appears.

data validation



Data Validation Dialog Box

The ‘Data validation’ dialog box should open and here you can dictate the data you want to include and where you want it to appear on your spreadsheet.

data validation dialog box



Select A Data Range

To select the range of cells where you want the answers to come from in your drop-down list, simply highlight the cells as you would normally.

For instance, click on May then drag down until May, June, July, and August are all selected. You should notice that the ‘Select a data range’ dialog appears with the range you selected which includes cells A6 to A9, in this case, known as A6:A9.

select a data range



Select A Cell

Click ‘OK’ and your range of choices has been created in the Criteria field as a list from a certain range.

You still need to select where you want the drop-down list to select an empty cell in your spreadsheet and drag it down until you have selected a total of four cells.

In this case, we will choose the cell range of D2 to D5 (D2:D5) so select D2 then drag it down to highlight the cells below to D5.

select a cell




Click ‘Save’ and you will notice that the four cells now have a downward arrow on each one.




Drop Down Options

When you select an individual cell, you can select one of four options; the four months that you selected as your pre-defined criteria.

Drop Down Options



Select From Options

In those cells, you can either select from one of the options or type it in.




Due to deciding what the criteria was, the data validation should only allow you to answer within one of those months.

If you want to answer that September is your favorite summer month then it may allow you to type it in but the answer will not be valid as it is not in the specified range that you already defined.

The cell will also be highlighted with a red triangle to display the error.




Select From Pre-Defined Options

When you do select one of the pre-defined options, the data validation will allow you to let that answer remain. In this case, May has been chosen as one of the answers.

predefined options



Disable Input Option

If you do not want anyone to be allowed to type in their answer then you can disable this option. In the Data Validation settings, you can select ‘Reject input option’ in the ‘On invalid data’ option instead of simply showing a warning.

disable input option

Creating Drop-Down List In Google Sheets

By creating a drop-down list, you can make your data consistent and manageable. This is handy if you only want certain answers to apply, for instance in a survey.

This narrows down the available options and you can even make sure that users are notified when their answer is invalid.

Andrew is one of three co-founders of BGFG, the parent company of PC Guide. A keen gamer and PC enthusiast, Andrew dabbles in a bit of writing sometimes - when he gets the chance to!