Learn how to use data validation in Google Spreadsheets to enter information faster and with fewer errors.
Image: Andy Wolber / TechRepublic
Inconsistent data can cause problems in Google Spreadsheets. A misspelled word, incorrect input, or input error can result in reports – or types – that are messy or misleading. Especially in larger lists, differently entered names (for example ‘touchscreen’ or ‘touchscreen’), numbers (for example ’10’ versus ‘ten’) or dates (for example ‘January 2020’, ‘2020-01’, ‘or’ 5 January 2020 “) can cause problems.
What’s hot at TechRepublic
If you have a Google page with inconsistently entered data, it may take some time to make the cell content consistent. A series of sorting actions (Data | Sorting) or Search-and-replace (Edit | Search and replace) can help turn a jumble of entries into a standardized set of cells.
Data validation ensures consistency when people enter information. In Google Spreadsheets, the function lets people select data from a list or lets the system compare entered data with a specified format. The first function, the drop-down list in the cell, works well when the potential data entry options for a cell are both known and contain a not-too-long list. The last function, Data Validation, works best when cell content must meet specific conditions (for example, a valid date or a valid date before or after a defined date, offer a check box, etc.).
SEE: How to package text in Google magazines (free PDF) (TechRepublic)
You can add data validation to a cell in Google Spreadsheets in a desktop class browser (such as Chrome on a laptop or desktop or Safari on iPadOS) or in the Google Spreadsheets app. The Google Sheets iOS app supports selection of items from a list and data validation upon entry, but does not offer the option to add new data validations to a cell.
In both cases below, you must open the Google sheet that you want to edit and select the cell (or cells) that you want to change.
Create a drop-down list that is entered in a cell of Google Spreadsheets
The following steps provide a list of options that a person can select within a cell. You can use the data validation function to enter the list of options or enter the options in cells elsewhere in your Google spreadsheet and then point the data validation function to that cell range.
1. In a browser, select Data | Data validation. In the Google Spreadsheets app, tap the menu with the three vertical dots (top right) and then Data validation (Figure A).
Figure A
Select information Data validation in Google Spreadsheets in a desktop-class web browser to create a drop-down list or to validate entered data.
In the Google Spreadsheets app, tap the menu with the three vertical dots (top right) and then Data validation (Figure B).
Figure B
In the Google Spreadsheets app, tap Android on the three-dot vertical menu, and then tap Data validation to configure drop-down lists or validation options for selected cells.
2. Next to Criteria, select List of range (default) or List of items.
3. If you have selected List from range, enter the range of cells elsewhere in your Google spreadsheet that contains the list of items that you want to display as a drop-down list.
4. If you chose List of Items, type the drop-down lists exactly as you want them to appear, with each item separated by a comma. In the Android Google Spreadsheets app, tap Add on the line below the List of items option and then enter your list with each item separated by a comma.
5. View the Invalid Data option. Although the default setting is Show Warning, when non-standard data is selected, you can change it to Reject Entry to check if the data in the cell is valid.
6. If desired, you can select the check box next to Appearance and then enter text that explains which type of cell entry is valid. In the Android Google Spreadsheets app, move the slider under Appearance to the right and then tap Edit, enter text to explain which entry is valid, and then tap OK. If you do not do this, when a person enters data that does not validate, a system issues a standard “There was a problem” message and indicates the cell that violates the validation rules (Figure C).
7. Select Save.
Figure C
Enable the Appearance option and add validation help text (top). When people enter incorrect data, this text indicates how the input may need to be corrected. If the help text is not enabled, a generic message is displayed after an invalid entry (below).
How to validate data entered in a Google Spreadsheets cell
- Select Details | in a browser Data validation. In the Google Spreadsheets app, tap the menu with the three vertical dots (top right) and then Data validation.
- Next to Criteria, select an option other than List of range or List of items. The options shown vary. On the internet, the system offers Number, Text, Date, Custom formula is and Check box. In the Google Spreadsheets app for Android you can choose from a much longer list of options.
- You may need to provide one or more values. For example, if you choose to validate that a date occurs within a range, the system will prompt you to enter two date values - one before the start of the range and one before the end of the range. With other options you may also have to provide a validation value or formula.
- View the Invalid Data option. Optionally, you can change this to Reject entry (from the default value of Display warning) to ensure a valid data entry.
- In most cases, you want to add explanatory text to transfer potentially valid values to people entering data. To do this, select the check box next to Appearance and then enter text that explains which type of cell entry is valid. Or in the Android Google Spreadsheets app, move the slider under Appearance to the right, tap Edit, enter text to explain which entry is valid, and then tap OK. Without this additional information, a person trying to enter data does not necessarily know which input is accepted as valid.
- Select Save.
What is your practice?
If you use Google Spreadsheets, do you use the Data Validation feature to ensure valid data entry, especially on shared sheets? And if you enter data into the Google Spreadsheets mobile app on Android or iOS, does a drop-down list make data entry faster and more efficient? Let me know how you use the data validation functions of Spreadsheets, with a comment below or on Twitter (@awolber).
Weekly newsletter from Google
Discover how you can get the most out of Google Docs, Google Apps, Chrome, Chrome OS, Google Cloud Platform and all other Google products used in business environments.
Delivered Friday
Register today
