Excel 2016: Data Validation in Depth

1h 35mAdvanced2025-02-11

Authors

Dennis Taylor

Excel expert with 25+ years of experience

Course details

With Excel's data validation tools, you can control how users enter data into workbooks, ensuring that data is consistent and accurate. You can control dates, times, even the length of the text they enter, or simply provide a list of acceptable choices to eliminate any possible mistakes. Here, Dennis Taylor explores how the data validation tools in Excel 2016 can be used to set dropdown lists and control numeric, text, date, and time entries.

Learning objectives
Explain the difference between an error alert and an input message.
Apply drop-down lists to save time and ensure consistency.
Explore different ways to manage data with multitiered lists.
Name the function that allows the placement of multiple restrictions within the same data validation.
Review the steps necessary to create time limitations.

Skills covered

Microsoft OfficeSpreadsheetsMicrosoft ExcelData AnalysisData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftDeep Dive (X:Y)

Concepts

0. Introduction

01 - Welcome
02 - Using the exercise files

1. Controlling the Limits of Numeric Data

03 - Whole number vs decimal limitations
04 - Using the Input Message box
05 - Using the Error Alert tab

2. Setting Up Drop-Down Lists

06 - Creating drop-down lists
07 - List location and order
08 - Multitiered lists

3. Date Controls

09 - Date limitations with basic controls
10 - Date limitations using formulas

4. Time Controls

11 - Time limitations with basic controls
12 - Time limitations using formulas

5. Text Length Controls

13 - Text length limitations with basic controls
14 - Text length limitations using formulas

6. Specialized Custom Formula Controls

15 - Requiring entries to be unique
16 - Locating cells with data validation rules
17 - Identifying cells that violate data validation rules
18 - Data validation shortcomings

Conclusion

19 - Goodbye