Advanced Data Analysis, Modeling, and Dashboard Reporting with Advanced Microsoft Excel Training Course

Course Outline

Module 1: Mastering Core Excel Functions for Data Analysis

ü  A review of key functions (SUMIFS, COUNTIFS, AVERAGEIFS)

ü  The power of VLOOKUP and HLOOKUP

ü  The modern XLOOKUP and its advantages

ü  Using INDEX and MATCH for advanced lookups

ü  Practical Session: Using Lookups and Logic Functions to Consolidate Data

Module 2: Data Cleaning and Transformation with Power Query

ü  The purpose and benefits of Power Query

ü  Importing data from multiple sources

ü  Cleaning and shaping messy data

ü  Merging and appending queries

ü  Practical Session: Cleaning and Preparing a Raw Dataset for Analysis

Module 3: Advanced Formulas and Functions

ü  Introduction to array formulas and dynamic arrays

ü  The FILTER, UNIQUE, and SORT functions

ü  Using text functions to manipulate data

ü  Advanced logical functions (IF, IFS, SWITCH)

ü  Practical Session: Building a Dynamic Report with Modern Functions

Module 4: Introduction to Data Modeling with Power Pivot

ü  Understanding the data model and its benefits

ü  Creating relationships between tables

ü  Using Data Analysis Expressions (DAX)

ü  Creating calculated columns and measures

ü  Practical Session: Building a Data Model from Disparate Data Sources

Module 5: Dynamic PivotTables and PivotCharts

ü  Creating a PivotTable from a data model

ü  Grouping, sorting, and filtering PivotTable data

ü  Using slicers and timelines for interactive filtering

ü  Creating dynamic PivotCharts

ü  Practical Session: Building an Interactive PivotTable Dashboard

Module 6: Advanced Charting Techniques

ü  Choosing the right chart for your data

ü  Creating combination charts and sparklines

ü  Using conditional formatting for visual impact

ü  Designing custom charts and templates

ü  Practical Session: Creating a Sales Funnel Chart and a Dashboard with Sparklines

Module 7: Principles of Dashboard Design

ü  The purpose of a dashboard

ü  Understanding key design principles (clarity, simplicity)

ü  Choosing the right key performance indicators (KPIs)

ü  Storytelling with data

ü  Practical Session: Sketching and Planning a Dashboard Layout

Module 8: Building Interactive Dashboard Elements

ü  Using form controls (buttons, drop-down menus)

ü  Linking cells to controls

ü  The camera tool for dynamic images

ü  Creating hidden sheets for data manipulation

ü  Practical Session: Building Interactive Controls for a Dashboard

Module 9: Case Study: Sales Performance Dashboard

ü  Working with a large sales dataset

ü  Creating a dashboard to track KPIs (revenue, units sold)

ü  Analyzing performance by region, product, and sales rep

ü  Using charts to visualize trends

ü  Practical Session: Building a Complete Sales Performance Dashboard

Module 10: Case Study: Financial Reporting Dashboard

ü  Working with financial data

ü  Creating a dashboard to track budget vs. actuals

ü  Analyzing profit and loss by department

ü  Using financial functions and formulas

ü  Practical Session: Building a Financial Reporting Dashboard

Module 11: Case Study: Project Management Dashboard

ü  Working with project data (tasks, timelines, resources)

ü  Creating a dashboard to track project status

ü  Visualizing resource allocation and project timelines

ü  Using Gantt charts in Excel

ü  Practical Session: Building a Project Management Dashboard

Module 12: Advanced Data Validation and Security

ü  Using custom data validation rules

ü  Creating cascading drop-down lists

ü  Protecting worksheets and workbooks

ü  Hiding sensitive formulas and data

ü  Practical Session: Securing a Dashboard Template

Module 13: Introduction to VBA for Automation

ü  The purpose and potential of VBA

ü  Recording and editing macros

ü  Writing a simple script to automate a task

ü  Running a macro from a button

ü  Practical Session: Creating a Macro to Automate a Repetitive Reporting Task

Module 14: Integrating External Data Sources

ü  Importing data from the web, databases, and text files

ü  The purpose of a data refresh

ü  Creating connections to external data

ü  Troubleshooting data import issues

ü  Practical Session: Importing Live Data from a Website

Module 15: Presenting Your Dashboard Findings

ü  The art of storytelling with data

ü  Preparing your presentation

ü  Tips for engaging your audience

ü  Handling tough questions

ü  Practical Session: Presenting a Final Dashboard with a Narrative

Module 16: Optimizing Excel for Performance

ü  Best practices for working with large files

ü  Minimizing file size and calculation time

ü  The use of named ranges

ü  Array formulas vs. helper columns

ü  Practical Session: Optimizing a Large Workbook for Faster Performance

Module 17: Dashboard Maintenance and Best Practices

ü  Creating a user-friendly dashboard

ü  Documenting your formulas and data sources

ü  Setting up automatic updates

ü  Troubleshooting common dashboard issues

ü  Practical Session: Creating a Dashboard Maintenance Plan

Module 18: Capstone Project: End-to-End Dashboard Build

ü  Participants will be given a complex business challenge

ü  Independently design and build a complete dashboard solution

ü  Present their final dashboard to the class

ü  Receive expert feedback and certification

ü  Practical Session: Presenting a Comprehensive Final Dashboard

About Our Trainers

Our trainers are certified Microsoft Excel experts and data analysts with over 15 years of experience in data management, business intelligence, and financial modeling. They have worked with major corporations across various industries, providing a wealth of real-world knowledge and practical insights. Their expertise ensures that the training is not just theoretical but grounded in real-world, enterprise-level application.

Quality Statement

We are committed to delivering a superior training experience. Our Excel classes feature a meticulously designed curriculum, expert instruction, and a hands-on approach to ensure you leave with tangible, applicable skills. Our commitment to quality in our Excel Dashboard training courses, ensures your professional development is an excellent investment.

Admission Criteria

ü  Participants should be reasonably proficient in English. 

ü  Applicants must live up to Phoenix Center for Policy, Research and Training admission criteria.

Terms and Conditions

1. Discounts: Organizations sponsoring Four Participants will have the 5th attend Free
2. What is catered for by the Course Fees: Fees cater for all requirements for the training – Learning materials, Lunches, Teas, Snacks and Certification. All participants will additionally cater for their travel and accommodation expenses, visa application, insurance, and other personal expenses.
3. Certificate Awarded: Participants are awarded Certificates of Participation at the end of the training.
4. The program content shown here is for guidance purposes only. Our continuous course improvement process may lead to changes in topics and course structure.
5. Approval of Course: Our Programs are NITA Approved. Participating organizations can therefore claim reimbursement on fees paid in accordance with NITA Rules.

Booking for Training

Simply send an email to the Training Officer on training@phoenixtrainingcenter.com and we will send you a registration form. We advise you to book early to avoid missing a seat to this training.

Or call us on +254720272325 / +254737296202

Payment Options

We provide 3 payment options, choose one for your convenience, and kindly make payments at least 5 days before the Training start date to reserve your seat:

1. Groups of 5 People and Above – Cheque Payments to: Phoenix Center for Policy, Research and Training Limited should be paid in advance, 5 days to the training.
2. Invoice: We can send a bill directly to you or your company.
3. Deposit directly into Bank Account (Account details provided upon request)

Cancellation Policy

1. Payment for all courses includes a registration fee, which is non-refundable, and equals 15% of the total sum of the course fee.
2. Participants may cancel attendance 14 days or more prior to the training commencement date.
3. No refunds will be made 14 days or less before the training commencement date. However, participants who are unable to attend may opt to attend a similar training course at a later date or send a substitute participant provided the participation criteria have been met.

Tailor-Made Courses

We understand that every organization has unique challenges and opportunities as well as unique training needs. Phoenix Training Center offers tailor-made courses designed to address specific requirements and challenges faced by your team or organization. Whether you need a customized curriculum, a specific duration, or on-site delivery, we can adapt our expertise to provide a training solution that perfectly aligns with your objectives. We can customize this Course to focus on your industry, specific risk profile, or internal stakeholder dynamics. Contact us to discuss how we can create a bespoke training program that maximizes value and impact for your team. For further inquiries, please contact us on Tel: +254720272325 / +254737296202 or Email training@phoenixtrainingcenter.com

Accommodation and Airport Pick-up

For physical training attendees, we can assist with recommendations for accommodation near the training venue. Airport pick-up services can also be arranged upon request to ensure a smooth arrival. Please inform us of your travel details in advance if you require these services. For reservations contact the Training Officer on Email: training@phoenixtrainingcenter.com or on Tel: +254720272325 / +254737296202