Your calculations are only as good as the data in your worksheets. You can’t do a lot to control the data you get from outside sources, but you can help your staff enter data correctly by creating data validation rules.
- [Tutor] Your calculations are only as good as the data in your worksheets. You can't do a lot to control the data you get from outside sources, but you can help your staff enter data correctly by creating data validation rules. I will show you how to limit the data that can be entered into a worksheet using validation rules in this movie. My sample file is the Validation workbook and you can find it in the Chapter Three folder of your Exercise Files collection. In this worksheet I have a table that has customer numbers and the quantity of a given order; let's say that they're ordering light-bulbs.
What I'd like to do is to set a rule that prevents customers from ordering more than 100 light-bulbs in a single order. I would need to create the validation rule in the Quantity column of this table. To select that column I will move my mouse pointer over the Quantity header which is in column C, and when the mouse pointer changes to a downward pointing black arrow, I will click to select the entire column. Now on the Data tab of the ribbon, I will click the Data Validation button.
Doing so displays the Data Validation dialog box. On the Settings tab which appeared for me because it's the last one I used, I can set my validation criteria. Right now it's set for Any Value so I will click the Allow controls down arrow and I will allow a Whole Number. Now I can have a number between two other numbers or I can have it be for example, above, less than, greater than, whatever. In this case I'll say that I want it between with a minimum of one, and a maximum of 100.
Now I could just click Okay to create my rule but instead I'd like to provide an Input Message. An Input Message indicates the acceptable quantities in this case. So I will click the Input Message tab and make sure the, "Show input message when cell is selected" checkbox is selected, and when the cell is selected it will show this Input Message. So I'll say in Title, Quantity, and then for the Input Message, "Input a quantity from one to 100." I can also have an Error Alert which indicates that an error has occurred, and also determines the behavior of the validation rule.
The default or at least what appeared for me is the Stop Style and what that does is prevents the user from entering an invalid value. I'll stay with that but I could also click the Style control's arrow and have Warning, which allows the data to come in or information indicating that a value was entered that's incorrect. So I click away, stay with Stop, and then for Title I will say Incorrect Value, and for the Error Message I will say, enter or I'll say, "Only quantities from one to 100 are valid." Alright everything looks good, I'll click Okay, and I have my rule.
If I click in cell A2, I don't get the rule, however if I click in cell C5, you'll see that I do have my Input Message. So I want to input a quantity from one to 100 but let's say that I'm a contrarian and I will enter 150, and Return. I get my dialog box saying, "Only quantities from one to 100 are valid", I can either cancel or click Retry. In this case I'll click Cancel and stop text entry.
I now understand that I can only input a quantity from one to 100 so I'll type 100, Enter or Return, and there we go. Now let's say that I want to circle all of the invalid data that is currently in my worksheet. In other words, I created the validation rule, extra data was already there, so I want to see if there are any invalid values. I can see that there is one invalid value in cell C2 but that's harder to spot in busier worksheets. So I will go to the Data tab of the ribbon, click the down arrow to the right of the Data Validation button, and click Circle Invalid Data, and you can see that the value in C2 has been identified.
If I want to remove the circles I can click the Data Validation button's down arrow again, click Clear Validation Circles. There are other types of rules that you can create such as limiting data entry to lists of approved values and I encourage you to use them. However in the interest of time, I'll just show you how to remove Data Validation rules. In this case I have validation rules in the Quantity column so I'll select that again, and go to the Data Validation button - I'm clicking the main body of the button this time - and then go back to Settings, then I will click the, "Apply these changes to all other cells with the same settings" box; in this case, every sell is selected but I'll do this just to be careful, and then click Clear All, and the validation criteria is set back to the way it was before.
And then I'll click Okay and you see if I click on cell C5 and enter 150, I'm able to do so because the validation rule has been removed.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks