Google Sheets is a convenient tool you can use to collaborate with your employees, colleagues, students, and friends. Since anyone that has access to a Google Sheet can easily make changes to it, wrong entries and invalid data may be inputted.
For instance, let’s say there’s a date column on a spreadsheet. A user could make an entry in date or text format (that is, 15/10/2019 or 15.10.2019). The latter will make data calculation and sorting difficult.
The good news is, with the Data Validation feature, you can implement hard controls within cells or specific ranges on the sheet to prevent users from entering anything that does not meet the parameters you’ve set.
In this guide, we’ll discuss how to use the feature. You’ll also learn how to use Sheet Protection to lock an entire sheet or selected areas on it.
How to Use Data Validation in Google Sheets
Here’s how to add data validation to a cell or a range in Google Sheets:
- Launch your browser.
- Enter https://docs.google.com/spreadsheets/ in the URL bar and press Enter on your keyboard.
- Open the spreadsheet you want to modify.
- Highlight the cell or range you want to validate.
- Click Data in the menu bar and then click on Data Validation from the context menu.
- In the window that opens, expand the ‘Criteria’ drop-down and select the type of data that users can input in the cells you selected (for example Date, Number, Text, and so on).
- Now, in the “On invalid data:” option, you can choose whether you want a warning to be displayed when a user enters invalid data or to simply refuse the entry and display an error message.
If you select ‘Show warning’, the user will be notified of the instructions set for the cell but the entry will not be rejected. Rather, it will be marked with a red indicator.
But if you select ‘Reject input’, the entry will not be allowed and the user will get an error that says, “The data you entered violates the data validation rules set in this cell.”
- If you choose ‘Reject input’, you can mark the checkbox that says, “Show validation help text:” in the ‘Appearance’ option. Then type a helpful message that will show the user what’s required for the data to be valid.
There you have it. Now you know how to restrict data entry in a worksheet.
How to Lock a Column in Google Sheets
You can lock cells, ranges, or the entire spreadsheet so that they cannot be edited without permission.
Follow these easy steps to do so:
- Launch your browser.
- Enter https://docs.google.com/spreadsheets/ in the URL bar and press Enter on your keyboard.
- Open the spreadsheet you want to protect.
- Highlight the cell, column, or row that you’d like to lock. You could also highlight the entire worksheet if you wish.
- Right-click on one of the selected cells and select ‘Protect range…’ from the context menu.
- In the dialog that appears, enter a description for the cell or range you selected and then click the button that says, “Set permissions.”
- Now, you can choose to ‘show a warning when editing this range’ or ‘restrict who can edit this range.’ If you select the latter, expand the drop-down menu and then select whether you want only you or other users as well to be able to edit the selected fields.
We hope this guide has been useful to you.
Sheet protection is helpful when collecting data from users and when you want to ensure that the entries contained in your spreadsheet are free of mistakes.
Data validation, on the other hand, ensures that users do not submit malformed data. It is particularly useful when you need to set up formulas or perform automation tasks that won’t work properly when the wrong data formats have been used.
On that note, we’ll now leave you with this tip: Always keep your PC safe from malicious items that can cause system crashes and data loss. Use Auslogics Anti-Malware to run a full system scan today. Give yourself the peace of mind you deserve.
Please feel free to leave us a comment in the section below.
We’ll love to hear from you.
