Save Time with Data Validation in Google Sheets

As Google continues to develop its offerings, one of its core applications continues to be Google Sheets (its answer to Microsoft’s Excel). Sheets allows you to work in real-time with other people and store your document online for free. This can be incredibly valuable when needing to track and create spreadsheets for a large team to view and expand upon without having to manage version control.

One of my favorite features in Sheets (it’s the same in Excel) is data validation. This helps restrict cell values, warns people who enter invalid data, and gives people a dropdown from which to choose predefined elements. You can use data validation to control the values that are entered into a cell. Restricting inputs with this feature can be a huge time saver in projects that pass through a lot of people, like Sheets is intended to be used.

As is true for many Sheets features, setting up data validation rules is pretty much the same as in Excel. 
 

1. Create your desired options for your dropdown options.
   
   • Note: put these on a separate sheet if you don't want them to be visible in your main sheet. ​
2. In the menu bar, select Data > Validation
   
   • Note: you can also select your range of cells, and right-click to select "Data Validation"
3. ​From this dialogue box, select the range you would like to be used in your dropdown. Be sure to include the Sheet number as a preface to select the correct group.
4. There are also a few more options you have at this prompt as well:
   
   • Show help - Shows user a warning when they click into the cell with a custom message you can enter (similar to the "show warning" above).
   • Display in-cell button to show list - Create a drop-down.
   • Reject input - Displays a pop-up that states the input does not match the determined validation rules. Use this if you want to be strict in the data input you allow.
   • Show warning - Shows a validation warning to the user when they click into the cell that their contents are invalid. Use this if your data input rules are more lax.
5. Be sure to select "Save" to apply your options.
6. This takes you back to your spreadsheet, where you will see a dropdown option in your cell that populates with the data range you previously selected.
7. Now we want to allow users to select from this dropdown in other cells in the spreadsheet. Right-click the cell with the new dropdown and select "Data Validation". You are now back into the previous prompt.
8. In the "Cell Range" field, enter the range you would like to have access to this newly created dropdown. Select "Save". 
9. You will now see the dropdown is available in more than just one cell for users to select, with the same criteria you established previously. 
10. To remove validation, select a cell range, click into the Data menu, select Validation, and select Remove validation.

You can use data validation to build custom lists, allowing you to quickly sort and fill in data in your workbooks. With custom error messages with the reject input option, you can prompt users to provide the correct information and save you time down the road.

If users are given free rein to input anything, your output can often be inconsistent and require more time for you to go in and correct information. Using data validation helps you get it right the first time.