Top Nav

How-To Add Google Docs In-cell Dropdown and Validation in Spreadsheets

imageA few days ago 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 added in-cell dropdown and validation in spreadsheets bringing it 1 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 various 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 simply choose an entry from the dropdown menu) and help prevent typos and errors. There are a couple of different ways to set this up  and a few different options to choose from.

How-To Add Google Docs in-cell dropdown validation {Screencast}

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 choose Data validation… 

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

Step 3

In the Criteria dropdown menu, choose Items from a list.

spreadsheet google drive validation

Step 4

Select Create list from range. 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.

Related Article:  Google Is Changing the Android SDK to a New Studio Environment

criteria range for google docs spreadsheets

Instead of creating a list from range, you can also enter the list items manually. Select Enter list items and simply 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.

The Help text is shown when a user hovers their mouse over a data validated cell. 

google docs data validation enter list items and help text



Check Show list of items in a drop-down menu if you want to allow users to choose valid options from a menu.

google docs prevent invalid entries

Check Allow invalid data, but show warning if you’d like to allow entries that aren’t included in your range. Unchecking 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 off the upper-right hand corner.



The Help text appears as a tool tip 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

Got it? If not, check out this video that walks you through both methods for setting up data validation in Google Docs spreadsheets. [Update: The video is a little outdated. You will now click the Data menu instead of the Tools menu]

More Reading:

, , ,

9 Responses to How-To Add Google Docs In-cell Dropdown and Validation in Spreadsheets

  1. Suresh Rouniyar January 22, 2011 at 5:14 pm #

    But how to use data validation between two google spreadsheets.I could not able to do so.

  2. Ben Smith Lea February 28, 2011 at 8:30 am #

    List validation is a welcome addition. I would like to report a bug and a workaround.

    I have a large spreadsheet with 1055 rows. Recently the List Validation for a column on the spreadsheet stopped working for new entries. When entering a cell in a new row the list was not offered. To fix this simply repeat the Validate Data … steps. Select the column header, open the data validation dialog and resave it.

  3. Erno February 2, 2012 at 11:11 am #

    Great! thank you for the info.

  4. ike eickholdt February 5, 2013 at 10:56 am #

    I WAS going to try and do an Android/Java app to track my archery scores. FIgured I’d just try Drive and use the Spreadsheet. On the web, using my laptop and Chrome, I can put a Validation Rule and pick from a list, in this case, 0, 4, 16 and 20. When I open the file on my Android phone, with the Google Drive app from Google Play, the validation rule is not adhered to. The pick list is not even presented,

  5. Colt November 4, 2013 at 1:11 pm #

    Curious if you have a workaround for allowing user to select more than one of the dropdown items.


  6. John September 23, 2014 at 5:45 am #

    Thanks for the article – useful.

    Curious to know if anyone has tried importing an Excel sheet with dropdown menus into Google sheets. Does it convert well enough? Including any data validation and/or lists?


  7. Rajendra April 25, 2016 at 6:49 am #

    I am using office 13, how can i do that in ms office 2013

  8. Stephanie Chrystal July 25, 2016 at 9:44 am #

    Thanks! Super helpful!

Leave a Reply