This advanced Excel course focuses on mastering formulas and functions used for multi-sheet data analysis and complex decision-making. Participants will learn to create logical, lookup, and array formulas that streamline reporting and automate calculations. Through practical, hands-on exercises, learners will gain the skills to link worksheets, use conditional functions, and build dynamic formulas for analyzing large datasets with accuracy and efficiency.
This four-hour course covers the Excel lookup functions and IF formulas used for multi-sheet analysis and reporting, in a live instructor-led class. You start by building formulas across multiple worksheets and using grouped worksheets to enter and update data. The class then covers logical tests with the Excel IF function, AND, OR and nested IF statements, and conditional totals with SUMIF and SUMIFS using mixed cell references.
Next you retrieve data with VLOOKUP, HLOOKUP and LOOKUP, use named ranges and table fields in formulas, and combine INDEX and MATCH for more flexible lookups, with an optional section on error-proof lookups using IFERROR. The final module introduces array formulas, SUMPRODUCT for multi-condition and weighted calculations, and IF-based array formulas for advanced logical analysis.
This intermediate course suits people who already build basic formulas and want faster, more reliable ways to look up and summarize data. The course fee is $225 for 4 CPE hours of instructor-led training. Organizations can also book it as private training for a team, and the form on this page lets you ask about upcoming dates or a group session.
Create and apply multi-sheet formulas to link and summarize data across worksheets.
Use grouped worksheets to enter, edit, and calculate data efficiently across multiple sheets.
Apply logical functions such as IF, AND, OR, and Nested IF statements for conditional analysis.
Use conditional aggregate functions (SUMIF and SUMIFS) to calculate totals based on specific criteria.
Incorporate mixed cell references to control formula behavior during copying or filling.
Utilize lookup functions (VLOOKUP, HLOOKUP, LOOKUP) to retrieve data from structured tables.
Define and apply named ranges and table fields within formulas for clarity and flexibility.
Combine INDEX and MATCH functions to perform powerful, flexible lookups.
Create and use array formulas and the SUMPRODUCT function for advanced multi-condition calculations.
Build IF array formulas to perform conditional logic across ranges of data.
Creating and using formulas across multiple worksheets
Grouping worksheets for efficient data entry and updates
Managing links and references between sheets
Understanding logical tests in Excel
Using the IF function for conditional results
Combining conditions with AND and OR functions
Building Nested IF statements for multi-level logic
Calculating conditional totals with SUMIF and SUMIFS
Applying multiple criteria in summary formulas
Managing and troubleshooting mixed cell references
Understanding lookup concepts and when to use them
Using VLOOKUP and HLOOKUP for vertical and horizontal lookups
Applying the LOOKUP function for flexible data retrieval
Creating dynamic formulas with Named Ranges and Table Fields
Combining INDEX and MATCH for powerful, flexible lookups
Comparing INDEX/MATCH with VLOOKUP for performance and flexibility
Building error-proof lookups using IFERROR and ISNA (optional extension)
Understanding the purpose and structure of array formulas
Creating and editing array formulas for multi-cell calculations
Using SUMPRODUCT for conditional and weighted calculations
Creating IF-based array formulas for advanced logical analysis
