Overview

Develop mastery of advanced Excel techniques and strategies through intensive hands-on training and practical application. Learn to harness the power of Excel's most sophisticated tools, including What-If Analysis capabilities, INDEX-MATCH formula combinations, macro automation, and additional advanced features designed for professional users.

This essential course is specifically designed for working professionals who rely on Excel as a central tool in their daily responsibilities and workflows. You will learn methods for making functions more flexible and adaptable, advance your Pivot Table skills to leverage more sophisticated functionality, and automate repetitive tasks using custom macros that save considerable time. You will develop competency with Excel's core database functions and advanced lookup techniques. If you currently possess a strong working knowledge of Excel fundamentals and seek to optimize your efficiency with additional time-saving shortcuts and advanced capabilities, this course is an ideal fit for your professional development.

Required Prerequisites:

Participants must demonstrate Excel proficiency equivalent to our Intermediate Excel course, including competency with VLOOKUP functions, Pivot Table construction and manipulation, and IF statement logic.

Core Learning Objectives and Topics:

What-if analysis techniques, including goal seek and data table functionality
Advanced cell reference locking and worksheet protection
Create more flexible lookup functions using INDEX-MATCH combinations
Advanced PivotTable techniques and custom calculations
Record and implement relative and absolute macro automation
Advanced nested functions and complex formula creation
Keyboard navigation without mouse dependency using hotkeys

Comprehensive Course Syllabus

Advanced Navigation
- Advanced Navigation techniques and methods
- Fill operations review and practice
Cell Management
- Advanced cell locking and worksheet protection
- Keyboard shortcuts and hotkeys
- Cell Auditing and formula tracing
- Go to special for complex selection
Special Formatting
- Conditional formatting using formulas
- Date functions and calculations
- Custom number formats and display options
Advanced Functions
- Nested IF statements and complex logic
- IF statements with AND and OR operators
What If Analysis
- Goal seek for target calculations
- Data tables for scenario analysis
Advanced Analytical Tools
- Calculation options and settings
- Conditional SumProduct formulas
- PivotTable base fields and set creation
- PivotTable calculations and custom aggregations
- PivotCharts and visual analysis
Advanced Database Functions
- XMATCH function for flexible matching
- INDEX-MATCH combinations for powerful lookups
- INDEX-Double MATCH for complex multi-criteria matching
Introduction to Macros
- Recording and editing macros
Dynamic Arrays
- Dynamic array formulas and usage
Capstone Projects
- End of class projects and real-world applications