Advanced Excel Dashboard Development Training Course

This course equips participants with advanced skills to build interactive, automated dashboards in Excel. It focuses on data modeling, PivotTables, formulas, dynamic charts, slicers, Power Query, and design principles that turn raw data into decision-ready insight.

Target Groups

• Data analysts and reporting officers
• Finance and budgeting professionals
• Monitoring and evaluation officers
• Operations and performance managers
• Managers who produce management reports
• Administrative and executive support staff
• Managers and supervisors seeking to strengthen organizational practice in this field
• Consultants and professionals supporting related organizations

Course Objectives

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

• Plan dashboards around audience needs and KPIs
• Structure and clean data for dashboard use
• Apply advanced formulas and functions
• Build PivotTables and PivotCharts for analysis
• Create dynamic charts and visual indicators
• Add interactivity with slicers, timelines, and form controls
• Use Power Query to automate data import and transformation
• Build data models with Power Pivot and DAX basics
• Apply dashboard design and visualization best practices
• Protect, share, and maintain dashboards

Course Modules

Module 1: Dashboard Planning and Design Principles

• Purpose and audience
• Selecting KPIs
• Layout and visual hierarchy
• Choosing chart types
• Color and formatting principles

Module 2: Data Preparation and Structuring

• Tables and named ranges
• Cleaning data
• Data validation
• Handling dates and text
• Normalizing data for reporting

Module 3: Advanced Formulas for Dashboards

• Lookup functions including XLOOKUP and INDEX-MATCH
• Logical and text functions
• Dynamic arrays
• SUMIFS, COUNTIFS, and AVERAGEIFS
• Error handling

Module 4: PivotTables and PivotCharts

• Building PivotTables
• Grouping and calculated fields
• PivotCharts
• Show values as
• Refresh and data sources

Module 5: Dynamic and Advanced Charts

• Combo and dual-axis charts
• Dynamic chart ranges
• Sparklines and conditional formatting
• Gauges and bullet charts
• KPI cards

Module 6: Interactivity with Slicers and Controls

• Slicers and timelines
• Form controls
• Drop-down selectors
• Linking visuals
• Navigation buttons

Module 7: Power Query for Automation

• Importing data
• Transforming and merging queries
• Appending files
• Refresh automation
• Error handling

Module 8: Power Pivot and Data Modeling

• Data model basics
• Relationships
• Measures with DAX basics
• Time intelligence
• Model-based PivotTables

Module 9: Finalizing, Protecting, and Sharing

• Dashboard layout polish
• Protection and permissions
• Printing and PDF export
• Sharing options
• Maintenance and documentation

Module 10: Practical Exercises and Capstone Project

• Defining KPIs and layout for a dashboard
• Cleaning and modeling sample data
• Building PivotTables and charts
• Adding slicers and automation
• Presenting a finished interactive dashboard