In a later video, we'll go over how to check for errors, but

one 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.

It's especially valuable when multiple people are making inputs on your

spreadsheet.

For instance, if your company is working on forecasting revenue for

the rest of the year.

And each store manager will be entering data into the budget file,

it's often a good idea to use data validation.

Which can control the type of data that is entered,

provide instruction to the end user and

even provide an error response when someone tries to enter data incorrectly.

Let's look at our spreadsheet and walk through a few data validation examples.

Here we have store sales by state for the first half of the year.

And we want each store manager to forecast sales for the rest of the year.

First, let's highlight the input cells by using the color

option here on the home tab.

These are the cells we want the managers to input their forecast into.

Now, let's go a step further and add some guidance and restrictions for

the forecast section.

Select the cells where the managers will input their forecast, and

then go up to the data tab and then Data Validation.

Now click on data validation and a box will pop up.

The default allows any value, but let's click on the drop down under Allow and

you can see all the different options you have.

Let's choose Whole number and then let's click on the drop down under Data and

there's several options here.

Let's choose between and then for minimum let's put 0 since we don't want

any managers to forecast a negative revenue.

And then for maximum, let's look at the previous month's sales and

come up with a range that is reasonable.

I think it's safe to put the maximum at 500,000 since our highest month at any

of the stores this year has been 144,000.

That's probably a pretty safe maximum.

All right, now I hit OK and let's check to see if it worked.

Let's say a manager accidentally try to key in a negative number.

You'll see an error message pops up and it doesn't allow it.

So it worked.

Now let's say a manager accidentally added an extra zero and

then put 1 million instead of 100,000.

Yep, another error.

You can change the error message if you click on the error alert tab.

And you can type your own message,

whatever you want to pop up when invalid data is entered.

You can also change the option under Style from Stop to Warning or information.

If stop is selected, it won't let you enter the data at all.

But if you choose Warning or Information, you can still enter the data but a message

will pop up giving you a warning that it's outside of the criteria that you selected.

If you choose Warning or Information you can aso choose Circle invalid data.

Under Data Validation, there you can see the negative of 50,000 is circled

since it's below zero which was the criteria we set.

If you ever want to clear the data validation, you can go back and

just hit Clear All at the bottom left, and then hit OK.

Another cool thing you can do, is enter an input message for

when one of the selected cells is clicked on.

If we go back to Data Validation and then click on the input message,

you can type in a message here that will pop up when someone clicks on that cell.

So lets type Forecast Instructions for the title, and

then type please enter whole numbers between 0 and 500,000.

Now hit OK.

Now you'll see when you click on one of the cells in the forecast section that

message will pop up.