The integrity of your data when performing calculations is incredibly important. Google Sheets data validation can help you ensure all of the values you input meet certain criteria to make sure all your calculations aren’t for naught.
Using Google spreadsheet validation puts light restrictions on the entered data to provide accurate and consistent work from all parties.
If you choose not to use data validation, you can end up with a spreadsheet full of incorrect data that leads to an unusable mess. This can be a particular issue for collaborative tasks. You don’t just have to worry about your own potential mistakes for these projects but those of others (that may have a terrible work ethic).
You can use a few restrictive Google Sheets validation types to make sure only specific values are available for input. These include:
- Checkboxes
- Date pickers
- Cell Checks
- Drop-down lists
- Value restrictions (dates, numbers, text, monetary values, etc.)
This article takes a deep dive into how to add data validation in Google spreadsheets. Read on to master this practice and keep your spreadsheets free from any frustrating errors.
This Article Covers:
Getting Started With Google Sheets Data Validation
To open up the Google spreadsheet validation, you just have to navigate to:
Data>Data validation
Doing so will open up a dialog box with a number of different parameters to fill and apply to your cell range.
Let’s take a look at what each of these do:
Cell range – This is the cells that you can select before navigating to the data validation menu. They’re the ones you want to make sure there’s no errors in. You can also type them in manually in this box rather than preselecting them.
Data validation – Is how you want your data to be validated. There is a dropdown menu with seven choices.
Each of these will apply a different validation requirement to the range of cells.
On invalid data – This option defines what you would like Google Sheets to do if it encounters invalid data. There are two options.
- Show Warning – This will allow the input to occur but every time you over the cell it will show a box stating that the data is invalid.
- Reject input – Instead of allowing the input into the spreadsheet, this option opens up an overlay stating that the input has been rejected. This one is great to prevent any invalid data from entering the spreadsheet in the first place.
Appearance – This option allows you to customize the message to send when invalid data is entered into the spreadsheet. (We recommend something a little more helpful than in our sample.) You need to have Reject input checked as well for this setting to work.
Removing Data Validation From a Google Sheet
Removing Google Sheets data validation is very simple. You just have to:
- Navigate to Data>Data validation
- Enter the details for the validation you want to remove in the pop-up menu (the last validation you made for that cell range should automatically be there)
- Click Remove validation
Example 1 – Basic Google Sheets Conditional Data Validation
One of the simplest validations you can do in a Google Spreadsheet is to only allow certain numbers into a group of cells. This is a single simple condition that has to be met by the user to avoid getting an invalid warning or reject input pop-up box. They won’t be able to put in letters, unique characters, or numbers outside the range you specify. Let’s dive into how to set this up.
- Open the Data validation menu
- In the Criteria dropdown menu, select number
- Select the numerically based criteria you want your inputs to meet. For this example, we selected between
- Provide the range for the accepted inputs. In our example we chose between 1 and 10. This means any number outside of that range will be rejected
- Select whether you want to Show warning or Reject input
- (Optional) if you selected Reject input, check Show validation help text and enter a helpful message to help users use a number in the correct range
Example 2 – Adding a Data Validation Custom Formula in Google Sheets
To make things simple, let’s consider the following data set and pretend we couldn’t just use the SUM function to get the total and had to enter the Total manually.
We can use a Google Sheets data validation custom formula to show whether or not our inputs are correct or not. We would have to use the following steps to achieve this:
- Select the first single cell that you want to apply the custom data validation formula to. In our example, the cell we are going to use is F5 (the first Total cell)
- Navigate to Data>Data validation
- In the Criteria section dropdown list in the Data validation menu, find Custom formula is
- In our example, we are going to use the formula:
=F5=D5+E5
This formula means that the number we input into F5 must be equal to the number in the cell of D5 plus the number inside E5
- Click Save
- When the data validation menu closes, click and drag the small blue box from the cell that we just added the restrictions to cover all of the relevant cells.
Doing this will move the data validation downwards but with the corresponding cells from the adjacent columns. I.e instead of =F5=D5+E5, the next cell down will instead validate for =F6=D6+E6 etc. This is the reason we selected a single cell instead of the entire applicable range in the first step.
- Enter values into the total column to check the added Google Sheets custom formula data validation is working
As you can see in the screenshot, the cells with invalid inputs have small red triangles next to them, indicating that they have violated the custom Google Sheets validation formula.
Google Sheets Data Validation FAQ
If you’ve skipped the meat of this text down to the FAQ here, we’ll answer your most burning questions and help you navigate to the section that will be the most helpful to you.
How Do You Do Data Validation in Google Sheets?
The short answer to this question is:
- Highlight the cells you want to run validation on
- Navigate to Data>Data validation
- Choose how you want your data input to be restricted
If you’re good at figuring things out on your own, that’s enough to get you started. If you struggle from this point. Come back to this article and read the whole thing.
How Do I Use Conditional Data Validation in Google Sheets?
Once you have set the parameters, the google sheets conditional formatting will be in place. You don’t have to do anything extra to make the validation work past setting the validation in the first place. Follow the steps above to put them in place.
What Is a Validation Rule in Google Sheets?
A validation rule is a parameter you set that prevents (or warns against) any incorrect input into your spreadsheet. They could be anything from restricting numbers outside a range through to Google Sheets data validation in custom formulas.
How Do I Apply Data Validation to Multiple Sheets in Google Spreadsheet?
This is not possible to do all at once without running a Google Script. And honestly, it is just easier and quicker to apply the validation one sheet at a time. To apply the validation to the entire sheet:
- Click on the gray box in the corner of the spreadsheet to select the whole sheet
- Follow the standard procedure to apply validation to a range of cells
How Do I Filter Data Validation in Google Sheets?
You’ll need to use the FILTER function before you do your data validation. Alternatively, you can use the Named ranges menu before entering the Data validation menu. This process is also known as creating a dependant drop-down list. It’s quite a lengthy explanation so check out that link to get the full low down.
The Bottom Line
In this article, we have shown you how Google Sheets data validation works in the simplest way possible. Of course, the custom formulas can get a lot more complex than the example we gave. Still, it’s essential to understand how the validation itself works before you add challenging formulas into the mix. If you have any questions, let us know in the comments.
Related:
