Microsoft Excel Data Analytics and Dashboard Reporting Training Course

Course overview

Business teams often hold critical operational, financial and customer data in Excel workbooks that are difficult to trust, slow to update and hard for decision-makers to interpret. Analysts and managers need more than charts: they need a repeatable process for importing data, checking quality, calculating meaningful measures and presenting exceptions, trends and drivers in a dashboard that can be refreshed without rebuilding it each reporting cycle. This course addresses that practical gap using Microsoft Excel’s analytics and reporting features.

Participants learn to structure datasets as Excel Tables, clean and combine source files with Power Query, apply formulas for analysis, build data models with Power Pivot and create PivotTable-led reports. The course covers lookup and logic functions, date analysis, conditional formatting, KPI design, slicers, timelines, chart selection and interactive dashboard layout. Participants will calculate measures such as revenue variance, margin, attainment, ageing and period-to-date performance, then turn those measures into management-ready reporting views.

Delivery combines instructor demonstration with guided workbook builds, individual data exercises and group critique of dashboard designs. Each participant works through a realistic reporting case involving monthly sales and operational data, identifying data-quality issues and defining the measures a manager needs to see. By the end of the week, participants leave with a completed Excel dashboard workbook containing a cleaned data query, documented calculations, PivotTables, charts, filters and a refresh process they can adapt to their own reporting responsibilities.

The course is suited to professionals who already use Excel for reporting but need a more disciplined analytics workflow, as well as capable spreadsheet users moving into analyst, reporting or business intelligence responsibilities. Managers gain staff who can produce more consistent reporting, explain how figures were derived and focus attention on decisions rather than manual workbook maintenance.

Course objectives

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

• Import, profile and transform reporting data with Excel Power Query
• Build structured Excel Tables with defined fields, data types and validation rules
• Apply XLOOKUP, SUMIFS, COUNTIFS, IF and date functions to calculate business measures
• Create PivotTables and PivotCharts that analyse performance by period, category and owner
• Develop a Power Pivot data model with relationships and DAX measures
• Design KPI cards, variance indicators and exception views for management dashboards
• Build an interactive Excel dashboard using slicers, timelines, charts and conditional formatting
• Document a refreshable reporting workflow and deliver a stakeholder-ready dashboard workbook

Benefits of attending

Target competencies

Who should attend

• Reporting Analysts — who need to convert recurring spreadsheet reports into refreshable management dashboards
• Business Analysts — who analyse operational data and must present findings clearly to stakeholders
• Finance Analysts — who prepare budget, forecast, variance and performance reporting in Excel
• Operations Managers — who monitor service, productivity, capacity or quality measures across teams
• Sales Operations Specialists — who track pipeline, attainment, territory and customer performance
• Project Coordinators — who consolidate delivery data and communicate status, risks and trends

Requirements and prerequisites

Participants should be comfortable entering data in Excel, navigating worksheets and workbooks, using basic formulas such as SUM and AVERAGE, sorting and filtering lists, and creating simple charts. Experience with PivotTables is useful but not essential; Power Query, Power Pivot, DAX and dashboard design are taught from the ground up. Participants need access to a laptop with Microsoft Excel for Microsoft 365 or Excel 2021 for Windows. No programming, SQL, statistics degree, Power BI experience or prior data-model design experience is required. Complete beginners to Excel should first build core spreadsheet skills before attending.

Training methodology

The instructor builds each technique in Excel before participants reproduce it in guided exercises using realistic sales, service and finance-style datasets. Short demonstrations are followed by hands-on work with Tables, formulas, Power Query, PivotTables, Power Pivot and dashboard components. Participants compare alternative KPI and chart choices in small groups, diagnose deliberately flawed source data and receive feedback on dashboard usability. The final sessions use an end-to-end reporting case, followed by an application plan identifying a live workplace report to redesign, its source data and its refresh owner.

Course outline

Day 1: Structuring and analysing reliable Excel data

• Excel Tables, structured references and dataset design
• Data types, field naming conventions and data validation
• Sorting, filtering and advanced filter criteria
• Duplicate detection and data-quality checks
• Relative, absolute and mixed cell references
• Core aggregation formulas using SUMIFS, COUNTIFS and AVERAGEIFS
• Lookup methods using XLOOKUP and INDEX-MATCH

Workshop: Participants convert an unstructured monthly sales extract into a validated Excel Table and produce a first set of category, region and salesperson calculations.

Day 2: Preparing source data with Power Query

• Power Query interface, query steps and data source connections
• Importing CSV files, Excel ranges and workbook folders
• Column profiling, error detection and null-value treatment
• Splitting, merging, replacing and standardising fields
• Appending monthly files and combining related datasets
• Unpivoting cross-tab reports for analysis
• Loading cleaned queries to worksheets and the Data Model

Workshop: Participants build a repeatable Power Query process that combines monthly regional files, cleans inconsistent values and loads a reporting-ready dataset.

Day 3: Analysing performance with PivotTables and data models

• PivotTable field layout and summarisation choices
• Grouping dates into months, quarters and years
• Calculated fields, show-values-as and percentage-of-total analysis
• PivotCharts and report filters for comparative analysis
• Power Pivot tables, relationships and star-schema principles
• DAX measures using SUM, CALCULATE and DIVIDE
• Time-intelligence measures for month-to-date and year-to-date reporting

Workshop: Participants create a linked sales and targets data model, then produce PivotTable analyses for attainment, margin and period-on-period variance.

Day 4: Designing interactive management dashboards

• Dashboard audience, decision questions and KPI selection
• KPI cards with targets, variances and status indicators
• Conditional formatting for thresholds and exceptions
• Chart selection for trends, comparisons, composition and ranking
• Slicers, timelines and connected PivotTable controls
• Dynamic chart ranges and formula-driven labels
• Dashboard layout, visual hierarchy and workbook navigation

Workshop: Participants design and build an interactive management dashboard page with KPI cards, trend charts, a ranked exception view and slicer controls.

Day 5: Delivering controlled, refreshable reporting

• Dashboard testing against source totals and business rules
• Formula auditing, trace precedents and error handling
• Workbook protection, input controls and version management
• Refresh procedures for queries, data models and PivotTables
• Documentation of assumptions, metric definitions and data lineage
• Presenting dashboard insights and recommendations to managers
• Excel-to-Power BI handover considerations and platform boundaries

Workshop: Participants complete, test and present their end-to-end dashboard workbook, including a refresh guide, KPI definitions and a short management insight briefing.

Tools & standards covered

Microsoft Excel for Microsoft 365, Power Query, Power Pivot, Power BI Desktop

A typical training day

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions