Ultimate PC optimizer: Save 30% on Auslogics BoostSpeed right now!

How to use Data Validation to restrict data in Google Sheets?

By Tobenna Nnabeze | January 29, 2020 |

greater than 3 minutes

Link copied

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:

  1. Launch your browser.
  2. Enter https://docs.google.com/spreadsheets/ in the URL bar and press Enter on your keyboard.
  3. Open the spreadsheet you want to modify.
  4. Highlight the cell or range you want to validate.
  5. Click Data in the menu bar and then click on Data Validation from the context menu.
  6. 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).
  7. 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.”

  1. 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.
  2. If you cannot resolve the
    problem yourself, you can
    ask our certified PC technicians for immediate assistance in the chat right on this page.

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:

  1. Launch your browser.
  2. Enter https://docs.google.com/spreadsheets/ in the URL bar and press Enter on your keyboard.
  3. Open the spreadsheet you want to protect.
  4. Highlight the cell, column, or row that you’d like to lock. You could also highlight the entire worksheet if you wish.
  5. Right-click on one of the selected cells and select ‘Protect range…’ from the context menu.
  6. In the dialog that appears, enter a description for the cell or range you selected and then click the button that says, “Set permissions.”
  7. 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.

RECOMMENDED

Protect PC from Threats with Anti-Malware

Check your PC for malware your antivirus may miss and get threats safely removed with Auslogics Anti-Malware

DOWNLOAD NOW

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.

Fed up with your slow PC? Tired of waiting for Windows to start up? Take a look at the most common reasons behind poor performance and the best ways to deal with them here.

GET LIVE HELP WITH PC ISSUES

Call us toll-free

US & Canada 1-888-257-4137

UK 1-800-041-8199

Australia 1-800-370-543

Chat with us online

Prefer us to call you back? Give us your phone number via chat

Fix your PC in THREE easy steps

Step 1

Call us or chat with us. Our agents are online around the clock

Step 2

We will remotely access your device, provide you with free diagnostics, and discuss repair options

Step 3

Sit back and watch. Most problems will be fixed immediately within less than an hour

Watch how it works or learn more about our service here

Ad Blocker Detected

Ad blockers may interfere with some important blog features, such as comments, images, etc.

Please consider disabling your ad blocker so you can have the best experience on this website.

Got it