Microsoft Excel — Expert

Course Overview

This expert-level course is designed for professionals who want to build sophisticated Excel solutions — automating complex processes with VBA, analysing multi-table data models with Power Pivot and DAX, building advanced financial models, and creating enterprise-grade dashboards. You will write VBA macros from scratch, build a full data model using Power Pivot, write DAX measures equivalent to Power BI calculations, model financial scenarios with data tables and Goal Seek, and protect and audit complex workbooks. This is the most advanced Excel training available — suitable for Excel power users, financial analysts, and business intelligence professionals.

What You Will Learn

• Write VBA macros from scratch — variables, loops, conditions, user forms
• Build multi-table data models in Power Pivot with relationships
• Write DAX measures: CALCULATE, SUMX, RELATED, time intelligence functions
• Create advanced financial models with data tables, scenarios, and Goal Seek
• Build dynamic array solutions using FILTER, SORT, UNIQUE, SEQUENCE, LAMBDA
• Protect workbooks professionally — structure protection, cell locking, digital signatures
• Audit complex formulas — trace precedents/dependents, watch window, error checking
• Build enterprise-grade multi-layer interactive dashboards

Course Modules

• VBA fundamentals: Sub vs Function, variables, data types, Option Explicit
• VBA control structures: For/Next, Do/While, If/Then/Else, Select Case
• VBA working with objects: Workbook, Worksheet, Range, Cells — reading and writing data
• VBA user interaction: MsgBox, InputBox, UserForms with controls
• VBA events: Worksheet_Change, Workbook_Open — triggered automation
• Error handling in VBA: On Error GoTo, On Error Resume Next, Err object
• Power Pivot: enabling add-in, importing tables, creating relationships (star schema)
• DAX basics: calculated columns vs measures, CALCULATE, SUMX, RELATED, RELATEDTABLE
• DAX time intelligence: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD
• Dynamic arrays (Excel 365): FILTER, SORT, SORTBY, UNIQUE, SEQUENCE
• LAMBDA functions: creating custom reusable functions without VBA
• LET function: variable assignment within formulas — readable complex formulas
• Financial modelling: data tables (1-variable, 2-variable), scenario manager, Goal Seek, Solver
• What-if analysis: sensitivity analysis, Monte Carlo concepts in Excel
• Advanced PivotTable: GetPivotData, multiple consolidation ranges, PowerPivot-based PivotTables
• Workbook protection: structure, window, cell locking, formula hiding, digital signatures
• Formula auditing: trace precedents/dependents, evaluate formula, watch window
• Advanced dashboard: dynamic named ranges, form controls, KPI gauges, scroll bars
• MO-201 Expert exam strategy and full practice paper

Who Is This Course For?

This course is for financial analysts, business intelligence professionals, management accountants, operations managers, data analysts, and Excel power users who need to build sophisticated automated solutions in Excel. Previous VBA exposure is helpful but not required.

Prerequisites

Completion of BITA Excel Advanced (or equivalent expert knowledge of PivotTables, Power Query, advanced formulas, and macro recording). Participants should be fully comfortable with everything in the Advanced course before attending.

Delivery & Access

• Blended, online, onsite or on-demand delivery — your choice of format
• Hands-on labs with real environments
• Instructor Q&A support for 30 days
• Verified certificate of completion by BITA
• Study guides, practice questions and reference materials included

Related Courses