Course info

In this presentation author and Excel expert David H. Ringstrom, CPA, shows you how to harness the full power of Data Validation to guide data entry and reduce errors in your spreadsheets. David explains how to create standard drop-down lists—including lists for all twelve months and self-expanding lists based on Excel tables—as well as how to craft dependent lists that adapt based on prior selections. You’ll learn how to apply validation for whole numbers, dates, and times, along with techniques for preventing duplicate entries and limiting text length. David also demonstrates how to clarify and extend existing validation rules, apply visual cues with Warning and Information styles, and protect your rules by locking cells or identifying overrides. Throughout, you’ll gain practical strategies for working with and troubleshooting Data Validation like a pro. 

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations. 

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Professionals seeking to use Microsoft Excel more effectively.

Topics typically covered:

• Creating named tables that match dropdown choices to enable dependent list validation.
• Improving the user experience by adding input messages and custom error prompts to Data Validation drop-down lists.
• Rewriting data validation input and error messages to eliminate ambiguity.
• Limiting the number of characters a user can enter in a worksheet cell.
• Adding totals and filters that automatically adjust based on which rows are visible.
• Restricting users to enter times within a given time window.
• Most features and functions work in Excel for Mac as well but expect differences.
• Removing data validation from a worksheet when needed.
• Employing the information style to inform users of an overridden data validation setting.
• Identifying data validation overrides by way of the Circle Invalid Data command.
• Creating validation rules without input or error messages, leaving users unclear on accepted values.
• Creating a Data Validation rule that prevents users from entering the same data more than once in a list.