Microsoft Excel - Data Validation Techniques

A date typed in a phone number column, a birthdate with this year in it, a misspelled state abbreviation: Excel Data Validation can stop errors like these before they reach your spreadsheet. This 60-minute Excel data validation training shows you how.

You'll learn what Data Validation is and why it helps, then set rules that require whole numbers, decimals, dates or times, block negative numbers or future dates, and limit entries to a dropdown list, including dependent dropdowns such as category and subcategory.

The session also covers rejecting malformed phone numbers, Social Security numbers and zip codes, building formula-based rules, showing input prompts and friendly error messages, and marking invalid data that's already in a sheet.

• Explain what Data Validation is and how it improves data quality
• Restrict entries to whole numbers, decimals, dates or times
• Build dropdown lists and dependent dropdowns
• Reject badly formatted phone numbers, SSNs and zip codes
• Create custom validation rules with formulas
• Add input prompts and error messages, and flag invalid data

• What Data Validation is and its benefits
• Requiring whole numbers, decimals, dates or times
• Rules such as no negative numbers or no future dates
• Restricting input to values in a dropdown menu
• Creating a dependent dropdown, such as category and subcategory
• Rejecting malformed phone numbers, SSNs and zip codes
• Setting up a rule based on a formula
• Displaying an input prompt so users know what to enter
• Showing a friendly message when data is invalid
• Marking invalid data

Spreadsheets are only as good as the data people enter, and cleaning up bad entries wastes time. Data Validation makes files easier to use and keeps the data accurate from the start.

The session suits new Excel users, data analysts, office workers, administrators, managers, business owners, executives, salespeople, trainers, bankers and anyone who builds spreadsheets that other people fill in.

• Office Workers
• Administrators
• Data Analysts
• Managers of All Levels
• Business Owners
• CEO's
• CFO's
• CTO's
• Salespeople
• Trainers
• Bankers
• Anybody who needs to enhance their spreadsheets to collect more accurate data

Tom Chandler started teaching Excel and other Microsoft Office (now Microsoft 365) applications in Silicon Valley in 1996. In 2010 he started a computer training and consulting company named TechMentors. He has had the pleasure of teaching and consulting with many small and large companies including Stanford Medical Center, Visa, Seagate, and the City of San Jose.
Tom received his Bachelors of Science in Finance and Accounting from Brigham Young University. He also received a MBA with a concentration on Computer/Information Technology Administration and Management from Western Governors University.
Tom and his wife, Madeline, are very family oriented. They have seven (7) children and currently thirteen (13) grandchildren with two more on the way. Consequently in 2020 Tom and Madeline moved to northern Utah so they could be near many of their children and grandchildren.