MICROSOFT EXCEL-INTERMEDIATE TO ADVANCED LEVEL

Course Overview

Microsoft Excel – Intermediate To Advanced Level : Modern organizations rely heavily on accurate reporting, fast analysis, and data-driven decision-making. Professionals are increasingly expected to work with large datasets, automate reporting processes, create dashboards, and generate analytical insights efficiently.

This intensive 2-day course is designed as the continuation pathway from the “Microsoft Excel – Basic to Intermediate Level” program. The course focuses entirely on advanced analytical, dashboarding, automation, and data transformation techniques without repeating foundational topics.

Participants will work with practical business scenarios and advanced Excel tools commonly used in finance, HR, operations, administration, and management reporting.

Course Objectives

By the end of this course, participants will be able to:

• Apply advanced lookup and dynamic formulas
• Analyze and summarize large datasets
• Build interactive dashboards and reports
• Use advanced PivotTable techniques
• Perform What-If analysis
• Create advanced charts and KPI reporting
• Automate repetitive reporting tasks
• Transform and consolidate data using Power Query
• Improve reporting speed and analytical accuracy

Course Content

DAY 1 – Advanced Data Analysis & Dashboarding

Module 1: Advanced Formula Techniques

• Nested IF and IFS
• Advanced IFERROR applications
• XLOOKUP
• INDEX & MATCH
• Dynamic Array Functions:
  • FILTER
  • SORT
  • UNIQUE
• TEXTJOIN and advanced text functions

Module 2: Advanced Data Preparation

• Advanced filtering techniques
• Text-to-Columns
• Flash Fill advanced applications
• Managing inconsistent datasets
• Data cleanup strategies
• Data auditing and error checking

Module 3: Power Query Fundamentals

• Introduction to Power Query
• Importing data from multiple files
• Transforming datasets
• Appending and merging data
• Cleaning and reshaping data
• Refreshing queries

Module 4: Advanced PivotTables & Analytical Reporting

• Calculated fields
• Advanced grouping
• Multi-level analysis
• Slicers and timelines
• Interactive Pivot dashboards
• Advanced PivotCharts

DAY 2 – Forecasting, Visualization & Automation

Module 5: Dashboard Design

• KPI selection techniques
• Interactive reporting concepts
• Dynamic dashboard design

Module 6: Advanced Charting Techniques

• Combination charts
• Secondary axis charts
• Dynamic charts
• Sparklines
• Waterfall charts

Module 7: What-If Analysis

• Goal Seek
• Scenario Manager
• Data Tables

Module 8: Excel Automation Techniques

• Introduction to Macros
• Recording and running Macros