Advanced Excel and Dashboards Training
3 day Training
Locations: Grayston Ridge Office Park, Sandton
Platform: Available In-Class / Online
Price: Available on Request
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
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
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
Related Courses
