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.

This is under construction.

Click this link for the course's start

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

1. Read Apply data validation to cells
2. Complete the Lab
3. Complete Quiz

Quiz

1. Users expect us to prevent them from making silly mistakes
  1. TRUE
  2. FALSE
2. Bad user entries can wreck a model as thoroughly as bad calculations

- 1. TRUE
  2. FALSE

1. Use data validation only for mandatory entries:

- 1. TRUE
  2. FALSE

1. Data validation Input Messages can be used to document any cell, not just entry cells.

- 1. TRUE
  2. FALSE

1. Input and Error message titles should match cell labels.

- 1. TRUE
  2. 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.

Data Synchronization

Introduction

Styles and Protection