Microsoft Excel Intensive III: Workshop Outline
Duration: 1 Day
Level: Advanced (Requires Microsoft Excel Intensive II or equivalent experience)
Format: Resident Human Online with Hands-on Workshop
Event Schedule
Topic 1: Power Query & Data Transformation
Break
Topic 2: Advanced Analytics & What-If Analysis
Break
Topic 3: Macros, Automation & Dashboard Design
Wrap-up & Next Steps
Event Description
Master enterprise-level Excel capabilities with Microsoft Excel Intensive III, an advanced workshop for professionals who have completed Excel Intensive II or possess comparable intermediate Excel expertise and want to become true Excel power users. This 5-hour intensive session focuses on professional-grade data management and automation techniques including Power Query for connecting and transforming data from multiple sources, advanced analytical tools like Solver and scenario analysis, macro recording and VBA basics for automation, and building executive-level interactive dashboards that drive business decisions. Through complex real-world business cases and hands-on challenges, you’ll develop the advanced skills that distinguish Excel experts who can handle enterprise-scale datasets, automate repetitive workflows, and create sophisticated analytical tools used by Fortune 500 companies.
This advanced Excel workshop covers critical enterprise skills including importing and cleaning data from databases, text files, and web sources using Power Query’s ETL capabilities, combining data from multiple tables with merge and append operations, using advanced array formulas and dynamic arrays for complex calculations, implementing Goal Seek and Solver for optimization problems, creating scenario manager models for forecasting and planning, recording and editing macros to automate repetitive tasks, and designing professional interactive dashboards with form controls, slicers, and dynamic visualizations. You’ll work with large-scale datasets containing thousands of records, learning to handle data that would crash basic spreadsheets, optimize workbook performance, and create self-service analytical tools that empower business users to explore data independently.
Whether you’re preparing for roles in business intelligence, financial modeling, data analytics, operations management, or Excel consulting, Excel Intensive III provides the advanced capabilities that define expert-level Excel professionals and separate you from 95% of Excel users. This workshop completes your transformation from beginner to Excel master, equipping you with sophisticated automation tools, professional dashboard portfolios, and the confidence to tackle any Excel challenge in enterprise environments. All participants receive advanced workbook templates, Power Query transformation scripts, VBA code libraries, dashboard design patterns, optimization guides, and exclusive preparation materials for Microsoft Office Specialist Expert certification.
Topic Areas
Topic 1: Power Query & Data Transformation
Master Excel’s powerful Power Query tool (Get & Transform Data) to import, clean, and reshape data from multiple sources including CSV files, databases, web pages, and other Excel workbooks without writing formulas. You’ll learn to connect to external data sources, remove duplicates and errors, filter and sort data before loading, split and merge columns, pivot and unpivot data structures, and create reusable query steps that automatically refresh when source data updates. This session covers advanced transformation techniques including appending multiple files from a folder automatically, merging queries to combine data from different tables like SQL joins, using conditional columns and custom columns with M language basics, handling data type conversions and date formatting inconsistencies, grouping and aggregating data for summary reports, and building efficient data pipelines that transform raw data into analysis-ready datasets, eliminating hours of manual data preparation work.
Topic 2: Advanced Analytics & What-If Analysis
Explore Excel’s sophisticated analytical capabilities including Goal Seek to find input values that achieve desired results, Scenario Manager for comparing multiple forecasts and planning alternatives, and Solver for complex optimization problems with constraints and multiple variables. You’ll learn to create sensitivity analyses that show how changing inputs affect outcomes, build Monte Carlo simulations for risk assessment using random number generation and data tables, perform regression analysis and forecasting with Excel’s Analysis ToolPak, and use advanced array formulas and dynamic array functions like FILTER, SORT, UNIQUE, and SEQUENCE for powerful data manipulation. This session covers financial modeling techniques including NPV and IRR calculations, loan amortization schedules, break-even analysis, and creating flexible models with named ranges and structured references that adapt to changing business conditions, enabling you to provide strategic insights and recommendations based on quantitative analysis.
Topic 3: Macros, Automation & Dashboard Design
Learn to automate repetitive tasks and create custom Excel solutions by recording macros, editing VBA code in the Visual Basic Editor, and understanding fundamental programming concepts like variables, loops, and conditional statements. You’ll discover how to assign macros to buttons and keyboard shortcuts, create user forms for data entry, automate report generation and formatting, and write simple VBA procedures that manipulate ranges, worksheets, and workbooks programmatically. This session culminates in designing professional interactive dashboards that combine PivotTables, dynamic charts, slicers, timelines, and form controls into cohesive analytical tools that update automatically and allow users to explore data through intuitive interfaces. You’ll master dashboard design principles including layout and visual hierarchy, choosing appropriate KPIs and metrics, implementing drill-down capabilities, using conditional formatting for alert systems, optimizing dashboard performance for large datasets, and creating self-documenting, user-friendly tools that empower stakeholders to make data-driven decisions independently without requiring advanced Excel knowledge.
