How-To Add Google Docs In-cell Dropdown and Validation in Spreadsheets
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.
Step 2
Click Data and choose Data validation…. Alternately, you can right-click your selected cells and select Data Validation…
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 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.
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.
Check Show dropdown list in cell if you allow users to choose valid options from a menu.
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.
The help text appears as a tooltip when you select the cell.
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.
Got it? Good. Go forth and validate!
Do you use cell validation? Tell us how in the comments below!
21 Comments
Leave a Reply
Leave a Reply
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.
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.
Erno
February 2, 2012 at 11:11 am
Great! thank you for the info.
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,
Austin Krause
February 5, 2013 at 11:02 am
Great observation ike erickholdt!
This is true. Google even knows about this problem, and they have yet to fix it after addressing that they were of aware of it back in December. Here’s the Google support post on the matter: https://productforums.google.com/forum/?fromgroups=#!topic/drive/0JGgz5VpK7I
Jack Busch
January 26, 2017 at 6:01 pm
Looks like this was fixed in iOS, sometime in the interceding 4 years since this comment…
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.
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
Rajendra
April 25, 2016 at 6:49 am
I am using office 13, how can i do that in ms office 2013
Stephanie Chrystal
July 25, 2016 at 9:44 am
Thanks! Super helpful!
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!
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!!
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?
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!
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.
MJ
April 3, 2017 at 5:17 pm
Can you add a drop down list in a Google Doc table (not sheets)?
Flavius
June 8, 2017 at 6:07 pm
Very helpful, thank you!
Claire Conlon
September 20, 2017 at 1:48 pm
Hi!
I hope I am not super late to comment… but… Any idea on how to pre-populate a ‘default’ value within the data validation?
For exmaple, I have a google sheet that is using data validation as a list with just ‘yes’ and ‘no’ for an Area Sales Manager to include (or not) certain opportunities in their Forecast.
However, I would like the data validation to pre-populate as ‘Yes’ when a column states ‘Converted Orders’ – so all their Converted Orders will dynamically be included.
Thank you!
Rob Hopkins
June 11, 2019 at 11:04 am
I validate entry cells on one sheet against a range of library values on another sheet – the library has several thousand items (food types) so the idea is not to scroll down the validation list but to simply type some characters into the entry cells and have the drop down gradually collapse to those matching the entered text at which point you click the one you want …
… so on a Chromebook, typing in “egg” collapses to a drop down of “egg custard”, “eggs, all varieties, no oil”, “fried eggs”
… typing a further “s” to make “eggs” collapses the list to “eggs, all varieties, no oil”, “fried eggs”
… all good so far – but accessing the same cloud-based sheet on iOS (various iPads) behaves differently
… typing in “egg” collapses to a drop down of “eggs, all varieties, no oil” – the iOS Sheets app appears to only offer validation entries ‘beginning with text’ whereas the ChromeOS offers validation entries ‘containing text’ – the former is useless for large validation lists whereas the latter is perfect
… has anyone else come across this, is it a known bug, has anyone found a workaround ????
Thanks for any feedback, Rob …