Training Course on Advanced Data Analysis and Dashboard Reporting with Microsoft Excel

Course Outline

Module 1: Advanced Excel Functions for Data Analysis

ü  Understanding logical functions (IF, SUMIFS, COUNTIFS)

ü  Leveraging lookup functions (VLOOKUP, XLOOKUP, INDEX-MATCH)

ü  Working with text and date functions

ü  Statistical analysis with Excel functions

ü  Practical Session: Solving real-world business problems using advanced functions

Module 2: Data Cleaning and Transformation with Power Query

ü  Introduction to Power Query and its benefits

ü  Importing and shaping data from various sources

ü  Removing duplicates and handling missing data

ü  Merging and appending queries

ü  Practical Session: Cleaning and transforming a messy dataset

Module 3: Mastering PivotTables and PivotCharts

ü  Creating and customizing PivotTables

ü  Grouping and filtering data effectively

ü  Calculated fields and items

ü  Designing impactful PivotCharts

ü  Practical Session: Analyzing sales data with PivotTables

Module 4: Introduction to Power Pivot and Data Modeling

ü  Understanding data models and relationships

ü  Creating calculated columns and measures with DAX

ü  Handling large datasets efficiently

ü  Combining multiple tables in Power Pivot

ü  Practical Session: Building a simple data model

Module 5: Advanced Data Visualization Techniques

ü  Choosing the right chart types for different data

ü  Customizing charts for clarity and impact

ü  Using conditional formatting for insights

ü  Sparklines and data bars for quick analysis

ü  Practical Session: Designing a financial performance dashboard

Module 6: Building Interactive Dashboards in Excel

ü  Dashboard design principles

ü  Incorporating slicers and timelines

ü  Creating dynamic charts and tables

ü  Protecting and sharing dashboards

ü  Practical Session: Developing an interactive sales dashboard

Module 7: Automating Reports with Macros and VBA Basics

ü  Introduction to Excel macros

ü  Recording and running simple macros

ü  Writing basic VBA scripts

ü  Automating repetitive tasks

ü  Practical Session: Creating a macro for report automation

Module 8: Integrating External Data Sources

ü  Connecting Excel to databases and web sources

ü  Using Power Query for real-time data updates

ü  Refreshing and managing external connections

ü  Best practices for data integration

ü  Practical Session: Building a live data report

Module 9: Dynamic Reporting with Slicers and Timelines

ü  Enhancing interactivity with slicers

ü  Using timelines for date-based filtering

ü  Creating connected dashboards

ü  Optimizing performance in large reports

ü  Practical Session: Adding interactivity to an existing dashboard

Module 10: Best Practices for Data Analysis and Dashboard Design

ü  Ensuring data accuracy and consistency

ü  Designing user-friendly dashboards

ü  Avoiding common pitfalls in Excel reporting

ü  Presenting data insights effectively

ü Practical Session: Peer review and optimization of dashboards

About the Trainers

Our trainers are certified Excel experts with over 10 years of experience in data analysis, business intelligence, and corporate training. They have successfully trained professionals across various industries, ensuring practical and actionable learning.

Requirements

ü  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 / +254737566961

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

This training course can also be customized for your institution upon request for a minimum of 5 participants. You can have it conducted at our Training Centre or at a convenient location.

For further inquiries, please contact us on Tel: +254720272325 / +254737566961 or Email training@phoenixtrainingcenter.com  

Accommodation and Airport Transfer

Accommodation and Airport Transfer is arranged upon request and at extra cost. For reservations contact the Training Officer on Email: training@phoenixtrainingcenter.com or on Tel: +254720272325 / +254737566961