how to add a drop down list in google sheets

Data validation drives consistency in your spreadsheets and makes spreadsheets more user-friendly.

Previously, we reviewed the Data Validation Feature in Excel 2010, and today I'm going to review the same feature in Google Docs. On August 26, 2010, Google Docs spreadsheets (aka Google Sheets) added in-cell dropdown and validation in spreadsheets bringing it one step closer to feature parity with Excel.

So, what's all this data validation jazz do? Simple – it constrains the values of a cell to a pre-set list or specific range.

For example, if you had a "Status" column in a spreadsheet keeping track of your various projects for your multiple clients, you could limit the valid entries for that column to "Completed," "In Progress," "Cancelled," etc., etc. In this way, you make your Google Docs spreadsheet more user-friendly (less typing—you can choose an entry from the dropdown menu) and help prevent typos and errors. There are many different ways to set this up and a few different options to choose from.

Update:This post was originally published in 2010. Some of the setting names and menus have changed. I've updated the screenshots and some of the steps below accordingly. Enjoy!

How-To Add Google Docs In-cell Dropdown Validation {Screencast}

Editor's note : This video is pretty outdated, but it will give you the overall idea of how validation works in Google Docs. See the step-by-step instructions to see the latest screenshots—the names of some of the options and settings have changed.

How-To Add Google Docs in-cell Dropdown Validation {Step-by-Step}

Step 1

Select the cell or cells to which you'd like to add data validation. You can select multiple cells by holding Shift and clicking a range of cells.

google docs cell validation

Step 2

Click Data and choose Data validation…. Alternately, you can right-click your selected cells and select Data Validation…

how to add a drop-down list of options in google docs

Step 3

In the Criteria drop-down menu, chooseList from range.

spreadsheet google drive validation

Step 4

Type in the range of cells you'd like to use as your entry options. Alternately, you can click the button to the right of the box and select a range with your mouse (this option is easier). You can even choose a range from a different spreadsheet within the doc.

Instead of creating a list from range, you can also enter the list of items manually. Select Enter list items and type them in the box to the right. Use this option when your valid entries aren't already included on your spreadsheet.

google xls xlsx data validation cells

Step 5

Fill out the additional options.

If you want to show help text when a user hovers their mouse over a data validated cell, checkShow validation help textand enter text in the field below.

google docs data validation enter list items and help text

Check Show dropdown list in cell if you allow users to choose valid options from a menu.

google docs prevent invalid entries

If you'd like to allow invalid data but show a warning, select Show warningfor the "On invalid data" setting. With this setting, you can type whatever you want here, but you'll be notified that it's not in the list of valid data. SelectingReject input for this option will cause invalid entries to be rejected.

Click Save when you are done.

After you set up data validation, the cell will have an arrow on the upper-right-hand corner.

SNAGHTML1a6834

The help text appears as a tooltip when you select the cell.

google docs validation of data

If you have allowed invalid entries, you'll see a warning if you type in data that isn't included in the list or specified range. This appears as an orange triangle in the upper-right-hand corner.

Google Docs Spreadsheets Data Validation