Prevent Errors Using Data Validation

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