Excel: Data Validation in Depth

Course details

Data validation is a process that occurs within the way data is entered but also as it is extracted from data systems via reporting methods. As the old computer science saying goes, “garbage in/garbage out,” and this course shows Excel power users how to optimize Excel data validation functions and techniques in order to help ensure data accuracy and consistency in data sets and reports. Excel's powerful functions for data validation help eliminate hassles—or worse—that can occur when data inputs are poorly regulated and produce inaccurate data outcomes. Instructor Robin Hunt covers: the types of data validation commands for data entry in Excel; implementing data validation for specific cells; leveraging functions for creating valid data for lists and calculations; validating dates and times; non-standard data validation techniques; and data validation techniques for data in Power Query.

Concepts

0. Introduction

01 - Understanding different validation methods

1. Types of Data Validation Commands for Data Entry

02 - Controlling data through data validation commands
03 - Using the Input tab
04 - Creating error alerts
05 - Controlling cells to only hold whole numbers
06 - Controlling cells to only certain text length
07 - Challenge - Apply data validation and messages
08 - Solution - Apply data validation and messages

2. Implementing Data Validation for Specific Cells

09 - Creating drop-down lists
10 - Naming data in Excel for validation
11 - Multitiered lists
12 - Changing lists and limitations
13 - Challenge - Create lists and drop-downs
14 - Solution - Create lists and drop-downs

3. Creating Valid Data for Lists and Calculations

15 - Drop-downs and VLOOKUPs
16 - Drop-downs and XLOOKUPs
17 - Using IFERROR for clean values
18 - Using 3D references to reduce manual calculation
19 - Leverage custom functions for data validation
20 - Challenge - 3D reference challenge
21 - Solution - 3D reference challenge

4. Validating Dates and Times

22 - Validating dates with data validation
23 - Validating times with data validation

5. Other Data Validation Techniques

24 - Requiring entries to be unique
25 - Find data validation in a workbook
26 - Formula auditing to validate data
27 - Removing data validation

6. Data Validation Techniques for Data in Power Query

28 - Creating data sets from your validated data
29 - Dealing with duplicated data
30 - Dealing with bad data and types
31 - Using data validation to find invalid data.
32 - Using Column profile and quality to confirm values

7. Continuing Your Excel Data Validation Learning Journey

33 - Next steps and additional resources