Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
In this video, we'll implement data validation rules to identify and prevent common data errors in Google Sheets.
Updates
Part 1
Instead of looking for
Numberin the Criteria dropdown menu, you can selectIs betweeninstead.The
Show validation help textbox now readsShow help text for a selected cell.Savebutton now readsDone.
Part 2
When right clicking (CTRL+clicking on Mac) the cells E23-E25, you'll need to first click
View more cell actions, then selectData validation, thenAdd Rule.List from a rangeis now calledDropdown (from a range).Click
Advanced optionsat the bottom of the menu and checkShow help text for a selected cell.
Data Validation
Data validation is the process of ensuring the accuracy and quality of data using validation rules. To accomplish this, we can:
- provide a list of valid values
- display a prompt for users to enter valid data
- display an error message when invalid data is entered
- reject invalid data when entered
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
We're near the finish line.
0:00
In this video, we'll implement data
validation rules to identify and
0:02
prevent common data
errors in Google Sheets.
0:05
Let's set the stage.
0:09
This workbook is for different state
reps to enter monthly sales totals and
0:11
projections for the next quarter.
0:15
Arizona entered their totals in Row 4.
0:18
California in Row 5, etc.
0:21
They'll each come back to enter sales
projections for July, August, and
0:25
September.
0:28
So more than a dozen different
people will access this spreadsheet.
0:30
Our biggest concern is making
sure the data is valid.
0:34
For example, based on these values,
0:38
we should expect numbers
within a specific range.
0:40
Anything too high or
too low is a red flag.
0:44
We can control this using data validation.
0:48
What is data validation?
0:51
Data validation is the process
of ensuring the accuracy and
0:54
quality of data using validation rules.
0:58
To accomplish this in Google Sheets,
we can provide a list of valid values.
1:01
Display a prompt for
users to enter valid data.
1:08
Display an error message when
invalid data is entered.
1:12
Or reject invalid data when entered.
1:17
For this exercise, select the State Totals
tab at the bottom of the workbook.
1:21
Quarter 1 and Quarter 2 values
have been entered for each state.
1:26
And Quarter 3 projections
should come in soon.
1:30
So let's do this.
1:33
Select cells D4 through L17.
1:35
This covers all the states
from Arizona to Virginia and
1:44
the months from January to September.
1:47
With the cells selected, click the Data
menu and choose Data Validation.
1:50
Click the Cell Range
grid icon to verify that
2:00
State Totals D4:L17 is the selected range.
2:04
Click OK.
2:10
For Criteria, click the dropdown arrow and
choose Number.
2:13
The new dropdown menu should be between.
2:21
And at a glance, all the totals
are between 20,000 and 50,000.
2:24
Let's allow for some flexibility.
2:29
We might have a stellar month or
an emergency that might affect sales.
2:32
So between 5,000 and 90,000.
2:37
We can't see the entire 90,000,
but we'll verify it shortly.
2:48
On invalid data, select Reject Input.
2:53
We don't wanna keep the data
if it's outside this range.
2:58
Click the Show validation help text
checkbox, and a new text box will appear.
3:01
Here's where we can verify that
we entered 90,000 properly.
3:07
If not,
make sure you fix it in the Criteria box.
3:11
The validation text will
update automatically.
3:16
Click Save, and now cells D4 through
L17 have this new validation rule.
3:20
Let's try it.
3:31
Go to cell J4 and
enter a projection for Arizona in July.
3:32
Type 120,000, Enter.
3:41
We have a problem.
3:45
And notice that it didn't keep our
entry because it was out of bounds.
3:48
It was rejected.
3:52
Okay, maybe 40,000 is more reasonable.
3:55
Then 42,000 for August,
and 45,000 for September.
4:04
Great, Arizona is done.
4:12
Other states will follow suit.
4:14
Let's double-check our existing data.
4:17
Hmm, March sales in California
were only about 4,500.
4:24
That doesn't seem right.
4:29
And notice the red flag in Cell F5.
4:31
When I hover over F5,
it tells me that the value is invalid.
4:36
The input must be between 5,000 and 90,000.
4:40
Applying a validation rule doesn't remove
invalid entries that were already there.
4:45
So the next best thing is to
bring them to our attention.
4:50
I went back to our pivot table and
realized that sales in California,
4:54
for March was $45,440.
5:00
Someone forgot the 0 at the end.
5:02
Easy fix, click F5,
5:05
Add a 0, hit Enter.
5:11
Boom, the red flag is gone.
5:16
Last thing before we wrap up.
5:20
We want to create a list of
states to open new stores.
5:23
The only requirement is that we can only
open a new store in a state with existing
5:26
stores.
5:30
And our list of states that have
stores is in cells B4 to B17.
5:32
So let's create a new validation rule.
5:39
Select E23 to E25.
5:42
Let's access data validation differently.
5:48
Right-click to select the cells or
Ctrl + Click on a Mac.
5:51
Choose Data Validation at
the bottom of the list.
5:56
Let's make sure we selected
the correct range.
6:00
State Totals E23:E25, click OK.
6:03
For Criteria, we want lists from
a range from the dropdown menu.
6:10
Next, in Enter a range or formula, click
the grid icon to select your data range.
6:14
We'll type B4: B17.
6:21
Click OK.
6:31
Show dropdown list in cell
should remain checked.
6:34
On invalid data, select Reject input.
6:37
Click the Show validation
help text check box.
6:42
Let's change the text.
6:46
Select and delete what's there.
6:48
Now enter, Select a state from the list.
6:56
Click Save.
7:02
And you'll notice there is a dropdown
list in cells E23 to E25.
7:06
In cell E23,
click the arrow to see the list of states.
7:13
Pick one. I'll choose New York.
7:18
In cell E24, we can also type, and
it uses your input to narrow your choices.
7:23
C-A-L, only one state contains CAL.
7:30
So press Enter to accept California.
7:36
In cell E25, let's try to enter
a state not in our list, I-O-W-A.
7:41
Oops, we can't enter Iowa
because it's not in our list.
7:50
Here's our validation text.
7:54
Select the state from the list.
7:56
Let's go back to E25, dropdown or type,
7:59
it doesn't matter, Arizona, done.
8:03
So now this worksheet has two
different types of data validation.
8:09
Great job, I hope this was helpful.
8:13
If you have any questions, feel free to
post them in the questions section for
8:16
any of the videos in this workshop.
8:20
You can also reach out to other
learners in the community, or
8:22
contact the amazing Treehouse staff.
8:25
Until next time, Professor T, out.
8:28
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up