Top Nav

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

imagePreviously, 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 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.

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 out of date, 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 the 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, choose List from range.

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 items manually. Select Enter list items and just type them in the box to the right.Use this option when your valid entries aren’t already included on your spreadsheet.

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, check Show validation help text and enter text in the field below.

google docs data validation enter list items and help text

Check Show dropdown list in cell if you want to 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 warning for 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. Selecting Reject 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

Got it? Good. Go forth and validate!

Do you use cell validation? Tell us how in the comments below!

More Reading:

, , ,

18 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.

    Thanks.

  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?

    Regards,
    John

  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!

  9. Jim Treloar December 6, 2016 at 7:53 pm #

    This was a perfect tutorial. Exactly what I needed. Clear instructions. I was able to do this in less than 5 minutes. THANK YOU!

  10. Orest Bats February 1, 2017 at 8:36 am #

    Thanks mate!

    Very useful guideline!

    P.S. in selection from “Enter list items” you should use commas as separator and no spaces instead semicolon 😉

    • Jack Busch February 4, 2017 at 12:06 pm #

      Gadzooks, you’re right! Semicolons do not work. Fixed the screenshot. Thanks for the catch!!

  11. Andrew Davies February 28, 2017 at 2:39 pm #

    I was wondering if there is a way to do this but have each option color coded to be a different color?

  12. Jeffrey Thomas March 1, 2017 at 9:53 pm #

    Apparently they recently shifted the validationdropdown arrow into the cell itself; now, a narrow cell will display the arrow rather than the value!

  13. Jessica W March 24, 2017 at 1:11 pm #

    This was perfect, just what I needed. And A+++ for the examples used!

    • Steve Krause March 24, 2017 at 2:29 pm #

      Awesome! Glad you found us Jessica! Welcome to the site.

  14. MJ April 3, 2017 at 5:17 pm #

    Can you add a drop down list in a Google Doc table (not sheets)?

Leave a Reply

 

Free Learning

 

Don't miss a single tip, how to or tech news update. Subscribe to my free newsletter and receive updates, right to your inbox.

You have Successfully Subscribed!

10 Shares
+1
Share
Share
Email
WhatsApp