Course Information
Excel Formulas and Techniques for Excel Power Users
Parking for SAO, Professional Development courses is in Garage B (1511 San Jacinto Blvd.). The Garage signage may read 1511 San Jacinto or Garage B. The elevator in Garage B is not reliable. If you are unable to walk the stairs, please contact the professionaldevelopment@sao.texas.gov for alternate parking arrangements. Handicapped parking is free at the meters around the downtown area.
A course coordinator will email you a parking permit prior to the course start date. A permit must be displayed or you will be ticketed.
Course Description
This course is designed for advanced Excel users who want to master complex data manipulation through sophisticated functions and formula nesting. It covers high-level techniques such as advanced lookups, logical tests, and dynamic ranges to automate workflows and enhance analytical precision.
Course Objectives
Objectives
Master advanced logical and conditional functions, such as nested IFS, SUMIFS, and COUNTIFS, to perform complex data analysis.
Implement high-performance lookup and reference formulas including XLOOKUP, INDEX, and MATCH to retrieve data across multiple worksheets.
Utilize dynamic array functions like FILTER, SORT, and UNIQUE to create responsive reports that automatically update as source data changes.
Apply advanced text and date-time functions to clean, manipulate, and standardize inconsistent datasets for professional reporting.
Build sophisticated financial and statistical models by combining multiple function libraries into robust, reusable formulas.
Troubleshoot and audit complex formulas using the Trace Precedents, Trace Dependents, and Evaluate Formula tools.
Develop custom named ranges and utilize the LET function to improve formula readability, organization, and calculation speed.
Outline
The Foundation of Formulas
Exploring and reviewing relative referencing
Exploring and reviewing absolute referencing
Creating a name for an absolute reference
Aggregate Functions
Overview of using the IF, SUMIF, SUMIFS, COUNTIF functions
Nested IF statements vs the IFS function
Incorporating arrays
The IFERROR Function
Purpose of the IFERROR functions
Understanding which errors IFERROR evaluates
Creating the IFERROR function
Lookups
Purpose of Lookups
Creating V and H Lookups
Creating X Lookup
Creating X Lookups (Excel 2019, 365, or later)
Using arrays in Lookups
Incorporating COLUMN and ROW functions in Lookups
Text Functions
Purpose of CONCATENATE
New techniques to perform CONCATENATE functions
Overview of LEFT
Overview of RIGHT
Overview of MID
Conclusion and Bonus Demo (in Excel 365)
Prerequisites
All attendees must have prior knowledge of basic Excel formulas.
Instructors
Edgar has been working as a software skills instructor for more than 20 years with Power BI, Microsoft SQL Server, and MS Office as primary areas of expertise. As a professional software educator, Edgar realizes that being able to effectively conduct training requires a unique and specialized skillset. He utilizes the Adult Learning Method to foster knowledge transfer in an easy-to-follow manner. Edgar has very strong communication skills and many years of public speaking experience. His communication skills coupled with vast technical experience allow him to easily present potentially complex topics in an easy-to-understand manner. Whether working with non-technical workers or highly skilled developers/administrators, Edgar can adapt the communication approach to convey knowledge and foster understanding. He has delivered training to a broad range of audiences that extend from companies with smaller specialized teams to enterprise customers. A key mantra he abides by is to never stop learning.