Learn tips on how to use information validation in Google Sheets to enter info sooner, and with fewer errors.

Image: Andy Wolber/TechRepublic
Inconsistent information can create issues in Google Sheets. A misspelled phrase, an inaccurate entry, or enter error might end in reviews—or kinds—which can be messy or deceptive. Especially in bigger lists, in a different way entered names (e.g., “touchscreen” or “touch screen”), numbers (e.g., “10” versus “ten”), or dates (e.g., “January 2020,” “2020-01,” or “Jan. 5, 2020”) can produce issues.

What’s Hot at TechRepublic
If you might have a Google Sheet full of inconsistently entered information, it might take a while to make cell contents constant. A sequence of kinds (Data | Sort) or find-and-replace (Edit | Find And Replace) actions may help rework a jumble of entries right into a standardized set of cells.Data Validation helps guarantee consistency as folks enter info. In Google Sheets, the characteristic both lets folks choose information from a listing or lets the system evaluate entered information to a specified format. The first characteristic, the in-cell drop-down listing, works nicely when the potential information entry choices for a cell are each recognized and comprise a not-too-long listing. The latter characteristic, Data Validation, works greatest when cell content material should meet specified situations (e.g., be a sound date, or a sound date earlier than or after an outlined date, provide a checkbox, and so forth.).SEE: How to wrap textual content in Google sheets (free PDF) (TechRepublic)

You can add Data Validation to a cell in Google Sheets in a desktop-class browser (equivalent to Chrome on a laptop computer or desktop, or Safari on iPadOS) or within the Google Sheets Android app. The Google Sheets iOS app helps number of objects from a listing and information validation on entry, however doesn’t embody the flexibility so as to add new information validations to a cell.

In each circumstances under, you may have to open the Google Sheet you need to edit and choose the cell (or cells) you need to modify.How to create a drop-down listing entered in a Google Sheets cellThe following steps provide a listing of choices that an individual might choose inside a cell. You might both enter the listing of choices with the information validation characteristic, or enter the choices in cells elsewhere in your Google Sheet after which level the information validation characteristic to that vary of cells.1. In a browser, choose Data | Data validation. In the Android Google Sheets app, faucet the three-vertical dots menu (higher proper), then Data Validation (Figure A).Figure AChoose Data | Data validation in Google Sheets in a desktop-class net browser to create a drop-down listing or validate entered information.In the Android Google Sheets app, faucet the three-vertical dots menu (higher proper), then Data Validation (Figure B).Figure BIn the Google Sheets app on Android, faucet the vertical three-dot menu, then faucet Data Validation to configure drop-down objects or validation choices for chosen cells.2. Next to Criteria, choose both List From A Range (the default) or List Of Items.3. If you selected List From A Range, enter the vary of cells elsewhere in your Google Sheet that accommodates the listing of things you need to show as drop-down choices.4. If you selected List Of Items, kind within the drop-down choices precisely as you need them to show, with every merchandise separated by a comma. In the Android Google Sheets app, faucet Add on the road under the List Of Items choice, then enter your listing with every merchandise separated by a comma.5. Review the On Invalid Data choice. While the default is to Show Warning, when non-standard information is chosen, you could change it to Reject Input to make sure information within the cell is legitimate.6. Optionally, you could choose the checkbox subsequent to Appearance then enter textual content that explains what kind of cell enter is legitimate. In the Android Google Sheets app, transfer the slider under Appearance to the fitting, then faucet Edit, enter textual content to elucidate what enter is legitimate, then faucet OK. If you do not do that, when an individual enters information that does not validate, the system shows a normal “There was a problem” message and signifies the cell that violates validation guidelines (Figure C).7. Select Save.Figure CEnable the Appearance choice and add validation assist textual content (high). When folks enter incorrect information, this textual content conveys how the entry might should be corrected. Without the assistance textual content enabled, a generic message shows after an invalid entry (backside).How to validate information entered in a Google Sheets cellIn a browser, choose Data | Data validation. In the Android Google Sheets app, faucet the three-vertical dots menu (higher proper), then Data Validation.Next to Criteria, choose any choice apart from List From Range or List Of Items. The displayed choices range. On the online, the system provides Number, Text, Date, Custom Formula Is, and Checkbox. In the Android Google Sheets app, you could choose from a for much longer listing of choices.You might have to specify a number of values. For instance, in the event you select to validate {that a} date happens inside a variety, the system will immediate you to enter two date values–one for the beginning of the vary and one for the top of the vary. Other choices might equally require you to specify a validation worth or formulation.Review the On Invalid Data choice. Optionally, you could change it to Reject Input (from the default of Show Warning) to make sure legitimate information entry. In most circumstances, you’ll want to add explanatory textual content to convey doable legitimate values to individuals who enter information. To do that, choose the checkbox subsequent to Appearance, then enter textual content that explains what kind of cell enter is legitimate. Or within the Android Google Sheets app, transfer the slider under Appearance to the fitting, faucet Edit, enter textual content to elucidate what enter is legitimate, then faucet OK. Without this extra info, an individual who makes an attempt to enter information might not essentially know what enter will probably be accepted as legitimate.Select Save.What’s your apply? If you employ Google Sheets, do you employ the Data validation characteristic to make sure legitimate information entry—particularly on shared sheets? And, in the event you enter information within the Google Sheets cell app on Android or iOS, does a drop-down listing make information entry sooner and extra environment friendly? Let me understand how you employ Sheets’ information validation options, both with a remark under or on Twitter (@awolber). 

Google Weekly Newsletter

See tips on how to get essentially the most out of Google Docs, Google Apps, Chrome, Chrome OS, Google Cloud Platform, and all the opposite Google merchandise utilized in enterprise environments.
Delivered Fridays

Sign up right this moment

Also see