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
Prevent Errors Using Data Validation
3:52 with Tyler TallonOne of the best ways to keep errors out of your spreadsheet is to prevent them from ever happening in the first place. Data validation is a good tool to use to lessen the likelihood of input errors.
Downloads
Please note that this is a new spreadsheet, different from the one you used in the previous stage:
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
In a later video,
we'll go over how to check for errors, but
0:00
one of the best ways to keep errors out of
your spreadsheet is to prevent them from
0:03
ever happening in the first place.
0:07
Data validation is a good tool to use to
lessen the likelihood of input errors.
0:09
It's especially valuable when multiple
people are making inputs on your
0:14
spreadsheet.
0:17
For instance, if your company is
working on forecasting revenue for
0:19
the rest of the year.
0:22
And each store manager will be
entering data into the budget file,
0:23
it's often a good idea
to use data validation.
0:27
Which can control the type
of data that is entered,
0:29
provide instruction to the end user and
0:32
even provide an error response when
someone tries to enter data incorrectly.
0:35
Let's look at our spreadsheet and walk
through a few data validation examples.
0:39
Here we have store sales by state for
the first half of the year.
0:44
And we want each store manager to
forecast sales for the rest of the year.
0:48
First, let's highlight the input
cells by using the color
0:52
option here on the home tab.
0:57
These are the cells we want the managers
to input their forecast into.
0:59
Now, let's go a step further and
add some guidance and restrictions for
1:04
the forecast section.
1:08
Select the cells where the managers
will input their forecast, and
1:10
then go up to the data tab and
then Data Validation.
1:14
Now click on data validation and
a box will pop up.
1:17
The default allows any value, but let's
click on the drop down under Allow and
1:20
you can see all the different
options you have.
1:24
Let's choose Whole number and then let's
click on the drop down under Data and
1:27
there's several options here.
1:31
Let's choose between and then for
minimum let's put 0 since we don't want
1:33
any managers to forecast
a negative revenue.
1:37
And then for maximum, let's look
at the previous month's sales and
1:40
come up with a range that is reasonable.
1:44
I think it's safe to put the maximum at
500,000 since our highest month at any
1:46
of the stores this year has been 144,000.
1:51
That's probably a pretty safe maximum.
1:54
All right, now I hit OK and
let's check to see if it worked.
1:56
Let's say a manager accidentally
try to key in a negative number.
2:00
You'll see an error message pops up and
it doesn't allow it.
2:08
So it worked.
2:11
Now let's say a manager accidentally
added an extra zero and
2:12
then put 1 million instead of 100,000.
2:16
Yep, another error.
2:19
You can change the error message if
you click on the error alert tab.
2:21
And you can type your own message,
2:25
whatever you want to pop up
when invalid data is entered.
2:27
You can also change the option under Style
from Stop to Warning or information.
2:30
If stop is selected,
it won't let you enter the data at all.
2:36
But if you choose Warning or Information,
you can still enter the data but a message
2:40
will pop up giving you a warning that it's
outside of the criteria that you selected.
2:44
If you choose Warning or Information
you can aso choose Circle invalid data.
2:49
Under Data Validation, there you can
see the negative of 50,000 is circled
2:54
since it's below zero which
was the criteria we set.
2:59
If you ever want to clear the data
validation, you can go back and
3:02
just hit Clear All at the bottom left,
and then hit OK.
3:06
Another cool thing you can do,
is enter an input message for
3:10
when one of the selected
cells is clicked on.
3:14
If we go back to Data Validation and
then click on the input message,
3:16
you can type in a message here that will
pop up when someone clicks on that cell.
3:20
So lets type Forecast Instructions for
the title, and
3:24
then type please enter whole
numbers between 0 and 500,000.
3:27
Now hit OK.
3:42
Now you'll see when you click on one of
the cells in the forecast section that
3:44
message will pop up.
3:48
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