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.
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! 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. 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 Thanks. 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 Very useful guideline! P.S. in selection from “Enter list items” you should use commas as separator and no spaces instead semicolon ;) … 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 … Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
