Using Conditional Formatting and Data Validation in Excel
-
Speaker : BOB UMLAS
-
When : Monday, February 24, 2025
-
Time : 01 : 00 PM EST
-
Add To Calendar
Refer a Friend
Bob Umlas worked for a major tax and accounting firm, using Microsoft Excel® 8 hours a day, writing custom applications for staff and clients from 1998 to 2018.
He has been using Excel since 1986 - version 0.99 (on the Macintosh)! He was a contributing editor to Inside Microsoft Excel for many years, a magazine devoted exclusively to Microsoft Excel and published by The Cobb Group and later Ziff-Davis. At the time, most issues contain either an article by Mr. Umlas on using VBA (Visual Basic for Applications) or some tip or technique from him on using Excel. He has had more than 300 articles published on subjects ranging from beginner to advanced macros, and on tips, shortcuts, and general techniques using virtually all aspects of Excel.
Mr. Umlas was voted an “MVP” (Most Valuable Professional) by Microsoft each year from 1994-2018 (25 years!) for his contributions to the various online Forums about Excel and is known world-wide for his contributions in Excel. As an MVP, he met yearly with his fellow-MVPs at Microsoft’s headquarters in Redmond, where he had access to the product developers. He has been a beta tester for new versions of Excel since version 1.5, and was asked by Microsoft for his input for newer versions of Excel. In 1995 he led a session called "Maximizing Excel Development Using Array Formulas" at Microsoft's Tech Ed Conference in New Orleans, and he led a session called Tips and Tricks at a Microsoft convention in New York City. He has hosted London’s Global Excel Summit each year since 2021, and in person in 2024, as well as led a session on tips and tricks. He also led a session on tips and tricks at the Las Vegas’ Financial Modeling World Championship in December, 2023. He has led online sessions all over the world including Africa, India, England, Canada and of course, the USA.
He is also the author of “This isn’t Excel, it’s Magic!” which is available from http://www.iil.com/publishing as well as from Amazon.com. Another book, “Excel Outside the Box” is available from http://www.mrexcel.com and is for the very advanced Excel user. Another book, “More Excel Outside the Box” is also available from mrexcel.com. One more, “Cool Excel Sh*t” is more tips & tricks. Lastly, published for Kindle, is “Excel Preschool: Understanding Microsoft Excel, finally!” – a book for the very beginner!
He is also the technical editor of many of Bill Jelen’s books.
Mr. Umlas used to co-lead the New York PC User's group on Excel every month for about 20 years. He started leading this group again in 2018. He has been teaching Excel to individuals and corporations for several years.
At the completion of the 2013 world Excel Modeloff competition on 12/8/13, this was noted (see the last sentence): “The winners were announced at Microsoft’s Manhattan offices Sunday afternoon. Four teams of finalists competed in four rounds of Excel Golf, trying to get the shortest formula to solve four particular problems. The Australia/New Zealand team won this event, sharing a $4000 prize. I’ll note that Excel MVP Bob Umlas was in the audience and twice offered formulas that were shorter than the winning formula”
This session on Excel's data validation and conditional formatting covers a comprehensive range of techniques designed to enhance data accuracy, consistency, and visualization. The training begins with an introduction to data validation, explaining its importance in ensuring that only correct and meaningful data is entered into a spreadsheet. Participants learn to set up basic validation rules, such as restricting entries to whole numbers, decimals, dates, and text lengths. The session then explores more advanced validation options, including creating custom formulas for validation, setting up drop-down lists for consistent data entry, and applying error messages and input prompts to guide users.
The next segment focuses on conditional formatting, starting with an overview of its purpose and benefits. Participants are shown how to use predefined formatting rules to highlight cells based on their values, such as coloring cells that contain specific text, fall within a certain range, or exceed a particular threshold. The session also covers the creation of custom conditional formatting rules using formulas, allowing for more complex and tailored formatting options.
Attendees learn to apply conditional formatting to entire rows or columns based on specific criteria, enhancing the ability to quickly identify trends, outliers, and key data points.
Additionally, the session provides best practices for using data validation and conditional formatting in tandem to create robust, user-friendly spreadsheets that minimize errors and maximize data clarity. By the end of the session, participants are equipped with the skills to effectively manage and present their data, making them more efficient and effective in their roles.
Areas Covered
1. Conditional Formatting
- Highlight Cells Rules
- Greater than, Less than, Between, Equal to
- Text that contains
- A Date Occurring
- Duplicate Values - Top/Bottom Rules
- Top/bottom 10 Items - Above Average, Below Average
- Data Bars
- Color Scales
- Icon Sets
- Custom Rules - Formula-based rules
- Managing rules (edit, delete, prioritize)
- Clearing Rules - Clear rules from selected cells
- Clear rules from entire sheet
2. Data Validation
- Validation Criteria
- Whole number
- Decimal
- List
- Date
- Time
-Text length - Custom formula
- Input Message
- Error Alerts
- Drop-down Lists:
- Handling Invalid Data
- Handling Invalid Data
- Removing Validation: - Clearing validation rules from selected cells
Who Should Attend
Anyone using Excel.
Why Should You Attend
Attending a training session on Excel's data validation and conditional formatting is invaluable for anyone seeking to enhance their data management and analysis capabilities. Data validation ensures accuracy and consistency by restricting the type of data that can be entered into a cell, reducing errors and maintaining data integrity. Learning to implement drop-down lists, date restrictions, and custom validation rules can streamline data entry processes and improve overall efficiency.
Conditional formatting, on the other hand, allows users to automatically highlight cells based on specific criteria, making it easier to identify trends, outliers, and key insights at a glance. Mastering these tools enables users to create dynamic, visually engaging spreadsheets that convey information effectively.
Overall, this training empowers individuals to manage large datasets more effectively, improve data quality, and present data in a way that supports better decision-making. These skills are crucial for professionals in any industry, enhancing their analytical abilities and boosting productivity.
-
$160.00
-
