Advanced Microsoft Excel

Course Overview

The “Advanced Microsoft Excel” program is meticulously designed to equip learners with the advanced skills and in-depth knowledge necessary to master Excel for complex data analysis, visualization, and automation. This comprehensive course not only covers advanced Excel functions, data analysis tools, data visualization techniques, and automation with macros and VBA, but also emphasizes their practical application through hands-on exercises and real-world projects.

Course Structure

Duration: 2 months
Sessions per week: 3
Hours per session: 2
Total hours: 48

Module 1: Advanced Formulas and Functions (12 hours)

Session 1: Logical Functions
Using IF, AND, OR, and NOT functions
Nested logical functions for complex conditions
Session 2: Lookup and Reference Functions
Mastering VLOOKUP, HLOOKUP, and LOOKUP
Using INDEX and MATCH for advanced lookups
Session 3: Text Functions
Text manipulation with LEFT, RIGHT, MID, CONCATENATE, and TEXT
Using TEXTJOIN and other advanced text functions
Session 4: Date and Time Functions
Working with DATE, TIME, YEAR, MONTH, DAY, and NETWORKDAYS
Advanced date and time calculations
Session 5: Array Formulas and Functions
Introduction to array formulas
Using dynamic arrays and the new Excel functions (FILTER, UNIQUE, SORT)
Session 6: Hands-On Project: Advanced Formulas and Functions
Applying advanced formulas to real-world scenarios
Developing a complex Excel workbook using advanced functions

Module 2: Data Analysis Tools (12 hours)

Session 7: PivotTables and PivotCharts
Creating and customizing PivotTables
Analyzing data with PivotCharts
Session 8: Data Analysis Techniques
Using data tables for scenario analysis
Implementing goal seek and solver for optimization
Session 9: Advanced Filtering and Sorting
Custom and advanced sorting techniques
Using advanced filters and criteria ranges
Session 10: Conditional Formatting
Applying conditional formatting rules
Using formulas in conditional formatting
Session 11: Data Validation
Creating data validation rules
Using custom data validation formulas
Session 12: Hands-On Project: Data Analysis
Conducting comprehensive data analysis
Developing a data analysis project using advanced tools

Module 3: Data Visualization Techniques (10 hours)

Session 13: Advanced Charting Techniques
Creating and customising advanced charts (Waterfall, Radar, Funnel, etc.)
Using combination charts for data comparison
Session 14: Interactive Dashboards
Designing interactive dashboards with slicers and timelines
Integrating PivotTables and PivotCharts into dashboards
Session 15: Using Sparklines for Data Trends
Creating and customising sparklines
Using sparklines for data visualisation in reports
Session 16: Power Query for Data Transformation
Introduction to Power Query
Transforming and cleaning data with Power Query
Session 17: Power Pivot for Data Modelling
Introduction to Power Pivot
Creating data models and relationships
Session 18: Hands-On Project: Data Visualization
Developing advanced charts and dashboards
Applying visualisation techniques to real-world data

Module 4: Automation with Macros and VBA (12 hours)

Session 19: Introduction to Macros
Recording and running macros
Editing and managing macros
Session 20: Basics of VBA Programming
Introduction to VBA and the Visual Basic Editor (VBE)
Writing simple VBA scripts
Session 21: Advanced VBA Programming
Working with variables, loops, and conditions
Creating user-defined functions
Session 22: Automating Tasks with VBA
Automating repetitive tasks
Interacting with Excel objects (workbooks, worksheets, ranges)
Session 23: Creating User Forms and Controls
Designing user forms for data input
Adding controls and handling events
Session 24: Hands-On Project: Automation with Macros and VBA
Developing a comprehensive automation project
Implementing VBA solutions for complex tasks

Conclusion

The “Advanced Microsoft Excel” program is not just a course. It’s a transformative journey that provides an in-depth and comprehensive understanding of advanced Excel functions, data analysis tools, data visualization techniques, and automation with macros and VBA. By blending theoretical knowledge with practical experience, this course equips learners with the skills needed to leverage Excel for complex data analysis and business intelligence. By the end of the course, participants will be empowered to handle advanced Excel tasks and significantly enhance their productivity in various professional settings.

Join us at CTTI and embark on this transformative journey to mastering advanced Microsoft Excel skills.