Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Your calculations are only as good as the data in your worksheets. You don't have lot of control over the data you get from outside sources, but you can help your staff enter data correctly by creating data validation rules. Now, let's say that you have customers, and one of the rules of your business is that all the customer numbers are higher than one million. In other words, they all start with at least a one, and there are six digits after that. Let's say that you have one of your employees entering an order from a customer, and they type in one, and let's say it's followed by four zeros and a one; in other words, the customer number is 100,001.
When your employee finishes entering data into that cell, even though you can tell visually the number is too short, if you're moving quickly or if you have a large amount of data to work through, you might miss that error, and you'll get a customer number error when you try to process his order. If you want to avoid that sort of mistake, or at least help reduce the possibility that it will happen, you can create what's called a data validation rule. So I'll go back to the cell and delete the data, and instead, I will create a data validation rule. To do that, you go to Data, the Data tab on the Ribbon, and then in the Tools group, you click Validate > Data Validation.
When the Data Validation dialog appears, you can use its tools to create a data validation rule. So, for example, in this case you want to have all of your customer numbers be at least one million. To create that, you specify the type of value that you want to allow. In this case, it will be a whole number. It needs to be greater than or equal to a specific number, and the minimum is one million, which is 1 followed by one, two, three, four, five, six zeros. And you don't want to allow any blank values.
To get rid of those, or to prevent those for being entered, you can clear the Ignore blank check box. Now, I'll just show you what the rule that I've created will do so far. If I click OK and then I try to enter let's just say 100 and I press Tab, then I get a message box saying that the value to be entered must be a whole number greater than or equal to 1,000,000. The user can then either cancel, in which case the value is erased from the cell, or they can click Retry, in which case they can enter it in. So let's say 1,500,000, press Tab, and the value is accepted.
You can do the same thing for dates, decimal values, and so on. But let's suppose that you're here in the Product column, and you want to create a rule that requires every value entered into that column to be exactly seven characters long. You don't care if it's a mix of letters and numbers; all you care about is that it is exactly seven characters long. But you can create that kind of rule. To do that, you open the Data Validation dialog and then under Allow click a Text length rule, that every value is 7 characters long.
Let's say that you want to delete a data validation rule from a cell. To do that, you click the cell and then click Validate > Data Validation and then you can either click Allow: Any Value, which effectively gets rid of the rule, in other words there is no restriction on what could be entered, or if you want to get rid of every data validation rule on your worksheet, you can click Clear All, which will get rid of every validation rule there. When you click OK, the rules are gone and I can enter any value into the cell that I want because there is no longer a data validation rule in effect.
Now, let's say that you apply a data validation rule to a cell that already has data entered. So in this case, I'm going to recreate the rule where I only want Whole number values, greater than or equal to 1,000,000. Click OK. Because I entered the value before I created the data validation rule, Excel doesn't mark it. What it can do, however, is have Excel identify any cells that contain invalid data. To do that, once again on the Data tab of the Ribbon, I click the Validate button's down arrow and click Circle Invalid Data.
When I do, Excel draws a red circle around any cell that contains invalid data. In this case, it's cell A5. If I were to try to edit it and enter another incorrect value and press Tab, it would say the value to be entered must meet the criteria. If I click Cancel, the original value comes back. If you want to remove your data validation circles, you click the Validate button's down arrow and click Clear Validation Circle. Data validation rules help reduce data entry errors, which are the bane of spreadsheet users everywhere. If you already have data entered into your worksheet and you apply new data validation rules, you can check if any of your existing data violates those rules by showing data validation circles.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.