Advanced Excel Modeling - Data Validation
Post date: Jul 17, 2018 12:02:17 AM
For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.
In the previous section we discussed how to avoid data sync errors. In this section we will explore how to avoid data entry errors.
Lesson Objectives
At the end of this lesson you will be able to:
Explain what data validation is and why it is important
Apply data validation to cells
About Data Validation
You may have heard developers say "Garbage in. Garbage out" which is our way of saying results are only as good as the input. User entry is, when left unchecked, perhaps the easiest way to turn a good model into rubbish. To prevent bad user entry Excel provides Data Validation.
Excel's data validation provides two important features:
Input Messages
Validation Criteria
This displays a small text box in which we can place the cell's purpose and allowable entries when the user selects the cell.
This limits what can be entered in the cell. Using this we can prevent users from entering bad dates, text into numeric fields, negative values where negatives make no sense, etc.
How To Add Data Validation
We add data validation by first selecting the cell we want to add validation to then using menu path: Excel's ribbon Data tab > Data Tools group > Data Validation icon (near the right end of this figure).
Clicking the Data Validation icon launches Excel's Data Validation dialog which contains three panels.
Settings
This panel sets the validation rule type and parameters.
Input Message
This gives us the opportunity to briefly communicate this cell's purpose and, perhaps, allowable entries.
Show...
Title
Input message
When checked, displays a small text box with this title and input message.
This is the text box's title. It should match the cell's title.
A brief description to explain the cell's purpose and, if appropriate, allowable entries.
Error Alert
This tells Excel what to do when an invalid entry is made.
Lab
Use the table below to add data validation to cells we want to let users change.
Make all entries mandatory (uncheck Ignore blanks)
Apply validation to only the Value cells just right of each of these Property labels.
For properties with Error Messages, check Show Error... and use style Stop.
Assignment
Complete the Lab
Complete Quiz
Quiz
Users expect us to prevent them from making silly mistakes
TRUE
FALSE
Bad user entries can wreck a model as thoroughly as bad calculations
TRUE
FALSE
Use data validation only for mandatory entries:
TRUE
FALSE
Data validation Input Messages can be used to document any cell, not just entry cells.
TRUE
FALSE
Input and Error message titles should match cell labels.
TRUE
FALSE
Summary
Data validation helps prevent one of the most common spreadsheet problems: bad entries. Helping users make correct entries avoids user frustration with our model.
We can use data validation's input message to document formula cells instead of cell comments. Data validation's advantage over comments is data validation is stored once and automatically extends in tables.