Restrict Data in Google Sheets with Data Validation - WindowsTips.net - Windows Tips and Tricks with Geek

Saturday, September 18, 2021

Restrict Data in Google Sheets with Data Validation

 Google Sheets Hero image.

How to Use Data Validation in Google Sheets

Fire up your browser, head to the Google Sheets homepage, open a spreadsheet, and highlight the range you want to restrict.

Highlight all the cells to which you want to add some data validation.

Click “Data,” and then click “Data Validation.”

Click Data, and then click Data Validation.

In the data validation window that opens, click the drop-down menu beside “Criteria.” Here, you can set a specific type of input to allow for the selected cells. For the row we’ve selected, we’re going to make sure people put in a four-digit number for the year a movie was released, so select the “Number” option. You can also select other criteria, such as text only, dates, a pre-defined list of options, items from the specified range, or your custom validation formula.

Click the drop-down menu next to "Criteria" and select the form of validation you want to use.

Each option has its own values you need to specify for this feature accurately to validate the data typed into each cell. Since we only want the year a movie was released, we’re going to use the “Between” validation. Then, we’re configuring a minimum value of 1887—the first motion picture created—and a maximum of 2500, which should be sufficient for the needs of this spreadsheet.

Click the type of validation you want to use, and then type the range.

Next, choose whether Sheets should show a warning message or completely reject anything typed and display an error message when the data is invalid.

The warning message notifies the user the number must be between a specific range and keeps the invalid data in the cell with a red notification.

Warning message when invalid data is typed.

If you select “Reject Input,” the user sees a pop-up window with a nondescript error message after he submits the invalid data and whatever he typed is also deleted.

Error message displayed when invalid data is typed.

You can also reject any invalid input, but provide people with some feedback on the type of data required. Click “Reject Input,” click the checkbox for “Show Validation Help Text,” and then type a helpful warning message. Click “Save” to close the validation tool.

Click "Reject Input," and then click "Show Validation Help Text." Type your preferred error message.

Now, when someone attempts to enter invalid data, she’s given a helpful message so that she can fix the error in the data.

Example of a helpful error message that tells users the type of data they need to submit for it to be validated.

No comments:

Post a Comment