Course Aims

• Empower learners to handle large, complex datasets confidently.
• Teach advanced functions for analysis and dynamic reporting.
• Introduce automation tools like macros and advanced PivotTables.
• Prepare students to produce professional dashboards and reports.

Course Outline
Session 1: Advanced Functions & Nested Formulas, Advanced PivotTables & PivotCharts

• Nested IF and IFS functions
• SUMIFS and COUNTIFS for multi-criteria analysis
• INDIRECT, OFFSET, and dynamic range creation
• Using TEXT and DATE functions for cleaning and formatting
• Grouping data (by dates, numbers, text)
• Calculated fields and custom calculations
• Using slicers and timelines for dynamic filtering
• Creating PivotCharts and customizing layouts

Session 2: Dashboard Elements & Dynamic Reporting, Lookup Power & Data Tools

• Linking charts and tables to summary data
• Using form controls (dropdowns, checkboxes) for interactivity
• Conditional formatting for KPIs and alerts
• Best practices for clean, readable dashboards
• Using INDEX & MATCH for flexible lookups
• XLOOKUP (if available) for advanced matching
• Consolidating data from multiple sheets
• Using Flash Fill and advanced Text to Columns

Session 3: What-If Analysis & Data Scenarios, Automation, Protection & Final Project

• Goal Seek for target-driven calculations
• Scenario Manager to compare different business models
• Data Tables for sensitivity analysis
• Using Solver (intro only) for optimization problems
• Recording and running macros
• Saving workbooks as macro-enabled (.xlsm)
• Protecting worksheets and workbooks (locking cells, password protection)
• Final project: build a multi-sheet report or dashboard with advanced analysis