ProspenAfrica | Training and Consulting Services Provider

Advanced Excel and Dashboards Training

3 day Training

Dates: 13 – 15 May 2024
Locations: Johannesburg, South Africa
Platform: Available In-Class / Online

Price: Available on Request

Group Bookings get a discount

This is a Non-accredited Course

Course Introduction

This comprehensive 3 Day Advanced Excel and Dashboards Training training program is designed for individuals who possess a solid understanding of Excel basics and are eager to elevate their skills to an advanced level. The course delves into intricate Excel functionalities and focuses on creating dynamic, interactive dashboards for insightful data visualization.


Participants will engage in a hands-on learning experience, combining theoretical knowledge with practical applications. Through a series of sessions, they will explore advanced Excel techniques, data analysis methods, and the art of crafting impactful dashboards. By the end of the training, participants will possess the expertise to design and develop sophisticated dashboards that empower data-driven decision-making.

Course Objectives

Upon completion of this Advanced Excel and Dashboards Training course, participants will be able to:

  • Master Advanced Excel Techniques: Expertly clean and transform data using advanced functions and Power Query. Employ complex text, logical, and array formulas to solve intricate challenges.

  • Conduct In-Depth Data Analysis: Manipulate data using functions to extract meaningful insights. Create scenarios for sensitivity analysis and decision modelling.

  • Harness the Power of Visualizations: Develop sophisticated charts and graphs that communicate complex data clearly.

  • Automate Tasks with Macros and VBA: Record, edit, and apply macros for automation.

  • Introduce VBA coding to streamline processes and enhance Excel capabilities.

  • Craft Interactive Dashboards: Grasp principles of effective dashboard design for enhanced user experience.

  • Integrate Multiple Data Sources: Combine data from diverse sources, including databases and web queries.

  • Polish and Share Dashboards: Apply design principles to create visually appealing and user-friendly dashboards.

  • Demonstrate proficiency by completing a comprehensive dashboard project.

Who should attend?

  • Business Analysts

  • Operations Managers

  • Management Accountants

  • Accountants

  • Auditors

  • Financial Analyst

  • Project Managers

  • IT Professionals

  • Researchers

  • Operational and Sales Managers

  • Anyone Handling Data: Anyone who works extensively with data and wants to improve their Excel skills to handle complex data manipulation, analysis, and visualization tasks.

Training methodology

Training Methodology

Our diverse instructional approaches ensure effective learning:

– Lectures & Presentations: Engage with expert-driven, stimulating content.
– Course Material: Access well-crafted supporting resources.
– Group Work: Collaborate on discussions and case studies for practical insights.
– Workshops & Role-Play: Participate in immersive, scenario-based activities.
– Practical Application: Focus on applying theoretical knowledge in real situations.
– Post-Training Support: Receive extensive support after training for skill implementation.

Training Outline

Day 1

Session 1: Data Cleaning and Preparation

  • Review of data importing techniques (CSV, databases, web data)

  • Data cleaning and transformation using functions (Text, Date, Logical)

  • Removing duplicates and handling missing values

  • Introduction to Power Query for data shaping

Session 2: Advanced Functions and Formulas

  • Advanced text functions (CONCATENATE, TEXTJOIN, SUBSTITUTE)

  • Array formulas and functions (INDEX, MATCH, TRANSPOSE)

  • Logical functions (IF, AND, OR) in complex scenarios

  • Nested functions and formula auditing

Session 3: PivotTables and Pivot Charts

  • Creating PivotTables for dynamic data analysis

  • Grouping and summarizing data in PivotTables

  • Introduction to Pivot Charts for visualizing PivotTable data

  • Slicers and Timelines for interactive filtering

Day 2: Data Analysis and Visualization

Session 4: Advanced Charting Techniques

  • Customizing chart elements (axes, titles, labels)

  • Combination charts for displaying multiple data types

  • Sparklines for compact data visualization

  • Dynamic charting using named ranges and OFFSET function

  1. Advanced Power BI Features

  • Utilizing advanced features like bookmarks, tooltips, and dynamic visuals.

  • Page & Bookmarks Navigators                              

Implementing row-level security for data access control.

  • Static Row level security

  • Dynamic Row level security

Session 5: Data Analysis with Functions

  • Introduction to Data Analysis Tool Pak

  • Descriptive statistics (mean, median, standard deviation)

  • Regression analysis and forecasting using LINEST

  • Using scenario manager for sensitivity analysis

Session 6: Introduction to Macros and VBA

  • Recording and playing back macros

  • Introduction to the Visual Basic for Applications (VBA) editor

  • Writing simple VBA code for automation

  • Using VBA to manipulate Excel objects and perform tasks

Day 3: Creating Interactive Dashboards

Session 7: Introduction to Excel Dashboard Design

  • Principles of effective dashboard design

  • Identifying key metrics and target audience

  • Wireframing and planning the dashboard layout

Session 8: Building Interactive Dashboards

  • Using slicers, timelines, and PivotTables for interactivity

  • Creating drop-down menus and dynamic data selection

  • Incorporating form controls (buttons, checkboxes) for user interaction

  • Introduction to Conditional Formatting for visual cues

Session 9: Advanced Dashboard Techniques

  • Data validation and data entry forms for controlled input

  • Using GETPIVOTDATA for extracting specific PivotTable information

  • Creating dynamic data tables using structured references

  • Incorporating external data sources using Web Queries

Session 10: Final Project and Sharing Dashboards

  • Participants work on a comprehensive dashboard project

  • Combining multiple data sources and advanced formulas

  • Polishing dashboard appearance and layout

  • Saving and sharing dashboards as interactive files

Course Conclusion: Recap and Next Steps

Review of key concepts covered in the training

Encouraging participants to practice and explore advanced features independently

Request a call back

Related Courses

Advanced Excel and Dashboards Training
Dates: 18 - 19 Apr | 18 - 19 Jun | 22 - 23 Aug 2024

A Complete Introduction To Power BI Desktop

View Course
Basic Microsoft Word
Dates: 03 May | 19 Jul 2024

Microsoft Word Basic

SAQA ID: 117924, NQF L2

View Course
Intermediate Microsoft Word: SAQA ID: 119078, NQF L3
Dates: 11 - 12 Apr | 16 - 17 May 2024

Microsoft Word Intermediate

SAQA ID: 119078, NQF L3

View Course
Basic Microsoft Word
Dates: 12 Jun | 19 Aug 2024

Microsoft Word Advanced

SAQA ID: 116942, NQF L3

View Course

Open chat
Need Help? Chat with Us
Scan the code
Powered by Prospen Africa
Welcome to Prospen Africa!
Check out our 15% Off sale when you purchase QCTO Training Material