Excel: Data Validation in Depth

Learn how to optimize Excel data validation functions and techniques to help ensure data accuracy and consistency in data sets and reports.

Introduction

• Understanding different validation methods

1. Types of Data Validation Commands for Data Entry

• Controlling data through data validation commands
• Using the Input tab
• Creating error alerts
• Controlling cells to only hold whole numbers
• Controlling cells to only certain text length
• Challenge: Apply data validation and messages
• Solution: Apply data validation and messages

2. Implementing Data Validation for Specific Cells

• Creating drop-down lists
• Naming data in Excel for validation
• Multitiered lists
• Changing lists and limitations
• Challenge: Create lists and drop-downs
• Solution: Create lists and drop-downs

3. Creating Valid Data for Lists and Calculations

• Drop-downs and VLOOKUPs
• Drop-downs and XLOOKUPs
• Using IFERROR for clean values
• Using 3D references to reduce manual calculation
• Leverage custom functions for data validation
• Challenge: 3D reference challenge
• Solution: 3D reference challenge

4. Validating Dates and Times

• Validating dates with data validation
• Validating times with data validation

5. Other Data Validation Techniques

• Requiring entries to be unique
• Find data validation in a workbook
• Formula auditing to validate data
• Removing data validation

6. Data Validation Techniques for Data in Power Query

• Creating data sets from your validated data
• Dealing with duplicated data
• Dealing with bad data and types
• Using data validation to find invalid data.
• Using Column profile and quality to confirm values

7. Continuing Your Excel Data Validation Learning Journey

• Next steps and additional resources