Locked lesson.
About this lesson
Learn how to use Data Validation to ensure that users enter valid data in input cells.
Exercise files
Download this lesson’s related exercise files.
Data Validation - Begin.xlsx25.1 KB Data Validation - Complete.xlsx
25.3 KB
Quick reference
Data Validation
Using Data Validation to ensure that users enter valid data in input cells.
When to use
When you create a model and release it to other users it is important to make sure that they are putting the correct data into the input cells in order to avoid issues and errors.
Instructions
Basic Date Validation
- Select any cell, go to the Data tab > Data Validation > Data Validation
- Select to allow a Date, Between
- For the start date enter =TODAY()-31
- For the end date enter =TODAY()
- Click OK and try to enter a date within the last 30 days, and one outside that range
Using Input Messages
- Select your cell, go to the Data tab > Data Validation > Data Validation
- Select the Input Message tab
- For the Title enter “Enter Date”
- For the message enter “Please enter a date within the last 30 days”
- Click Ok and notice that as soon as you select the cell an input message pops up
Using Error Messages
- Select your cell, go to the Data tab > Data Validation > Data Validation
- Select the Error Alert tab
- Choose the Stop style and give it a Title of “Invalid Data!”
- Provide a clear message for your user that helps them fix the problem without calling you (they already couldn’t figure out your input message!)
- Message: “Please enter a valid data within the last 30 days in the yyyy-mm-dd format.”
- Click OK and try to enter a date within the last 30 days, and one outside that range
- Notice that the invalid date triggers a much more helpful error than the default message
Lesson notes are only available for subscribers.