About This Course
In today's data-driven business environment, the ability to collect, clean, analyse, and visualise data is no longer optional — it is a core professional competency. Microsoft Excel remains one of the most widely deployed analytical tools across industries, yet most professionals use only a fraction of its capabilities. This programme is designed to move participants decisively beyond basic spreadsheet skills into the realm of structured, repeatable, and insight-driven data analytics.
Over ten intensive days, delegates will master the full analytical stack available within Excel — from data preparation and transformation using Power Query, to advanced formula engineering, statistical modelling, PivotTable design, and interactive dashboard construction. Each module builds on the last, creating a coherent analytical workflow that mirrors real-world business intelligence requirements.
TrainingComplex has designed this course for professionals who work with data regularly but need a structured, applied methodology to make that data genuinely useful for decision-making. Whether you are a finance analyst crunching revenue figures, an M&E officer tracking programme indicators, or a supply chain manager modelling demand forecasts, this course delivers the Excel fluency to elevate your analytical output.
By the end of the programme, participants will be equipped to independently design analytical solutions in Excel — solutions that are auditable, scalable, and presentation-ready — giving their organisations a meaningful competitive and operational advantage.
Who Should Attend
- Finance and accounting professionals who manage and report on numerical data
- Monitoring and evaluation officers working with programme performance data
- Business analysts and reporting officers in corporate or NGO settings
- Human resource professionals who track workforce metrics and HR analytics
- Supply chain and procurement officers who model inventory and cost data
- Project managers who need to build trackers, dashboards, and progress reports
- Marketing and sales professionals who analyse campaign and revenue performance
- Government and public sector officers engaged in data reporting and planning
- Researchers and academics who use Excel for quantitative data analysis
- Any professional seeking to strengthen their data analytics capabilities using Excel
Learning Objectives
- Demonstrate confident use of advanced Excel formulas including XLOOKUP, INDEX-MATCH, array formulas, and nested logic functions
- Import, clean, reshape, and transform datasets using Power Query and the M language
- Design structured, auditable Excel workbooks that follow data analytics best practices
- Build dynamic PivotTables and PivotCharts to summarise and explore large datasets
- Apply descriptive and inferential statistical functions to analyse distributions, correlations, and trends
- Construct interactive dashboards using slicers, timelines, dynamic named ranges, and form controls
- Automate repetitive analytical tasks using Excel macros and an introduction to VBA scripting
- Model business scenarios using What-If Analysis, Goal Seek, Solver, and sensitivity tables
- Visualise data effectively using advanced charting techniques tailored to different analytical narratives
- Apply data validation, conditional formatting, and structured tables to ensure data integrity
- Use Power Pivot and the Data Model to integrate multiple data sources and create calculated measures with DAX
- Present analytical findings professionally using Excel output formatted for executive audiences
Course Modules
Module 1: Foundations of Data Analytics in Excel
- The role of Excel in the modern data analytics ecosystem
- Core data analytics concepts: structured vs. unstructured data, data types, and analytical goals
- Excel workbook architecture best practices for analytics: separating raw data, calculations, and outputs
- Named ranges, structured tables (ListObjects), and their analytical advantages
- Keyboard shortcuts, navigation techniques, and productivity settings for power users
- Understanding Excel's calculation engine and how to audit formula dependencies
Module 2: Advanced Formula Engineering
- Logical functions: nested IF, IFS, AND, OR, and SWITCH for complex conditional logic
- Lookup and reference mastery: XLOOKUP, XMATCH, INDEX-MATCH, and CHOOSE
- Text manipulation functions: TEXTJOIN, TEXTSPLIT, LEFT, MID, RIGHT, FIND, and SUBSTITUTE
- Date and time analytics: NETWORKDAYS, EDATE, DATEDIF, WEEKNUM, and dynamic date calculations
- Array formulas and dynamic arrays: FILTER, SORT, UNIQUE, SEQUENCE, and BYROW
- Error-handling strategies: IFERROR, IFNA, and designing robust, audit-friendly formulas
Module 3: Data Cleaning and Preparation Techniques
- Identifying and handling missing values, duplicates, and inconsistencies in raw datasets
- Text-to-columns, Flash Fill, and data standardisation techniques
- Data validation rules to enforce input integrity at source
- Conditional formatting as a diagnostic tool for spotting outliers and anomalies
- Using helper columns and structured transformation logic before analysis
- Documenting data cleaning steps to ensure reproducibility and auditability
Module 4: Power Query for Data Transformation
- Introduction to Power Query: the ETL (Extract, Transform, Load) workflow in Excel
- Connecting to multiple data sources: CSV, Excel workbooks, databases, and web data
- Applying transformations: filtering rows, splitting columns, pivoting, unpivoting, and merging queries
- Grouping, aggregating, and reshaping data for analytical use
- Introduction to the M language: writing and editing custom transformation steps
- Refreshing queries and scheduling data updates for live reporting workflows
Module 5: PivotTables and PivotCharts for Data Exploration
- Building PivotTables from structured tables and the Data Model
- Grouping, sorting, filtering, and drilling down within PivotTables
- Calculated fields and calculated items for custom metric creation
- Slicers and timelines for interactive, user-controlled data exploration
- PivotCharts: linking visualisations to PivotTables for dynamic reporting
- PivotTable design best practices: layout, value formatting, and report-ready presentation
Module 6: Statistical Analysis and Descriptive Analytics
- Descriptive statistics in Excel: AVERAGE, MEDIAN, MODE, STDEV, VAR, SKEW, and KURT
- Frequency distributions, histograms, and percentile analysis using FREQUENCY and PERCENTILE
- Correlation and covariance analysis using CORREL and COVARIANCE functions
- Regression analysis using Excel's Analysis ToolPak: interpreting outputs and R-squared values
- Ranking, percentile rank, and normalisation techniques for comparative analysis
- Using the Data Analysis ToolPak add-in for t-tests, ANOVA, and descriptive summaries
Module 7: Data Visualisation and Advanced Charting
- Choosing the right chart type for the analytical narrative: comparison, distribution, composition, and relationship
- Building combination charts, secondary axes, and dynamic chart titles
- Sparklines, data bars, icon sets, and in-cell visualisation techniques
- Waterfall, funnel, treemap, sunburst, and box-and-whisker charts for advanced storytelling
- Chart formatting principles: reducing chartjunk, applying brand colours, and labelling for clarity
- Linking charts to dynamic data ranges and named ranges for automatic updates
Module 8: Interactive Dashboard Design
- Dashboard planning: defining KPIs, audience needs, and layout structure before building
- Using form controls (dropdown lists, scroll bars, option buttons) to drive interactivity
- Building dynamic named ranges with OFFSET and INDEX for self-updating charts
- Designing multi-page dashboards with navigation buttons and hyperlinked sheets
- Applying consistent colour schemes, typography, and grid alignment for professional output
- Testing dashboard functionality, protecting sheets, and preparing output for distribution
Module 9: Business Scenario Modelling and What-If Analysis
- Data tables: one-variable and two-variable sensitivity tables for scenario exploration
- Goal Seek: working backwards from a target output to find the required input
- Scenario Manager: creating, naming, and comparing multiple business scenarios
- Solver: optimisation modelling for resource allocation, cost minimisation, and capacity planning
- Forecasting functions: FORECAST.ETS, TREND, and GROWTH for time-series projections
- Building model documentation and assumption registers for transparent, auditable models
Module 10: Power Pivot and the Excel Data Model
- Introduction to Power Pivot: extending Excel's analytical capacity beyond worksheet limits
- Importing and linking multiple tables in the Data Model using relationship management
- DAX fundamentals: creating calculated columns and measures with SUM, CALCULATE, RELATED, and DIVIDE
- Time intelligence DAX functions: TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD
- Building KPI indicators and hierarchies in the Data Model for richer PivotTable analysis
- Comparing Power Pivot to standard PivotTables: when and why to use each approach
Module 11: Automation with Macros and an Introduction to VBA
- Recording macros: capturing repetitive tasks and reviewing the generated VBA code
- Running and assigning macros to buttons, shapes, and the Quick Access Toolbar
- The VBA editor: understanding modules, procedures, and the structure of recorded code
- Editing recorded macros: adjusting ranges, adding loops, and introducing conditional logic
- Practical automation use cases: formatting reports, refreshing queries, and exporting sheets to PDF
- Macro security settings, workbook compatibility, and distributing macro-enabled files safely
Module 12: Data Integrity, Governance, and Workbook Auditing
- Workbook auditing tools: Trace Precedents, Trace Dependents, and the Watch Window
- Identifying and resolving circular references, volatile functions, and performance bottlenecks
- Protecting workbooks, worksheets, and cell ranges with passwords and permissions
- Version control practices for analytical Excel files in team environments
- Data governance principles applied to Excel: ownership, access control, and change logging
- Preparing Excel workbooks for handover: documentation, naming conventions, and user guides
Module 13: Capstone Project: End-to-End Analytics Solution
- Project briefing: participants receive a sector-relevant raw dataset and a defined analytical problem statement
- Data import and cleaning using Power Query and direct Excel techniques
- Formula design and statistical analysis to surface key insights from the dataset
- Dashboard construction incorporating PivotTables, dynamic charts, and interactive controls
- Scenario modelling or forecasting component integrated into the analytical solution
- Final presentation of the completed analytics solution to the group, with facilitator and peer feedback
Requirements & Prerequisites
Participants should have a working knowledge of Microsoft Excel at an intermediate level, including familiarity with basic formulas such as SUM, AVERAGE, IF, and VLOOKUP. Experience navigating Excel workbooks, formatting cells, and creating simple charts is expected. No prior experience with Power Query, PivotTables, or VBA is required, as these topics are introduced progressively during the course.
Training Methodology
- Hands-on lab sessions where participants work through structured Excel exercises on their own laptops using real-world datasets drawn from finance, HR, supply chain, and development sectors
- Step-by-step guided demonstrations by the facilitator followed immediately by independent participant practice to reinforce each technique
- Case study analyses requiring delegates to diagnose messy, real-life datasets and apply appropriate cleaning, transformation, and analytical techniques
- Dashboard build challenges in which small groups design and present a fully functional Excel dashboard to solve a defined business problem
- Scenario-based modelling exercises using What-If Analysis and Solver to simulate business planning and forecasting decisions
- Peer review sessions where participants critique each other's formula logic, dashboard design, and data presentation choices
- Daily recap exercises that require delegates to apply the prior day's learning in a new, slightly more complex data context
- A capstone analytics project in which each participant independently builds an end-to-end analytical solution — from raw data import to executive dashboard — using a dataset relevant to their sector
Organisational Benefits
- Faster, more accurate internal reporting cycles driven by analysts who can work independently with complex data
- Reduced dependence on external consultants or IT teams for routine data processing and dashboard creation
- Improved quality of evidence for strategic decision-making across departments
- Standardised analytical tools and templates that ensure consistency across the organisation
- Enhanced capacity to integrate data from multiple internal systems into unified Excel-based reports
- Strengthened monitoring, evaluation, and learning capability for programme and project oversight
- Time savings through macro automation of high-frequency, repetitive data tasks
- A workforce that can communicate data insights visually and persuasively to senior leadership
- Greater organisational agility in responding to data requests from funders, boards, and regulators
- A measurable return on training investment through improved analyst productivity and output quality
Where Is Microsoft Excel for Data Analytics Training Offered?
This course runs in-person across: Johannesburg, Nairobi, Dubai, Naivasha, Accra, Kigali, Kampala, Dar es Salaam, Cape Town, Lagos, Abuja, Mombasa, Riyadh, Addis Ababa, Pretoria, Arusha, Kisumu, Nakuru, Zanzibar, plus Live Virtual (online) cohorts. Browse live dates for your city below.
Upcoming Schedules
| Location | Format | Dates | Price |
|---|
| Live Virtual |
Online |
5 Oct 2026 – 16 Oct 2026 |
KES 160,000 |
| Johannesburg, South Africa |
In-Person |
12 Oct 2026 – 23 Oct 2026 |
USD 7,000 |
| Nairobi, Kenya |
In-Person |
12 Oct 2026 – 23 Oct 2026 |
KES 230,000 |
| Dubai, United Arab Emirates |
In-Person |
19 Oct 2026 – 30 Oct 2026 |
USD 8,200 |
| Naivasha, Kenya |
In-Person |
19 Oct 2026 – 30 Oct 2026 |
KES 230,000 |
| Accra, Ghana |
In-Person |
26 Oct 2026 – 4 Nov 2026 |
USD 7,600 |
| Kigali, Rwanda |
In-Person |
26 Oct 2026 – 6 Nov 2026 |
USD 3,800 |
| Kampala, Uganda |
In-Person |
26 Oct 2026 – 4 Nov 2026 |
USD 3,800 |
| Dar es Salaam, Tanzania |
In-Person |
26 Oct 2026 – 4 Nov 2026 |
USD 4,600 |
| Cape Town, South Africa |
In-Person |
26 Oct 2026 – 4 Nov 2026 |
USD 7,600 |
| Lagos, Nigeria |
In-Person |
26 Oct 2026 – 4 Nov 2026 |
USD 5,000 |
| Abuja, Nigeria |
In-Person |
26 Oct 2026 – 6 Nov 2026 |
USD 5,600 |
| Mombasa, Kenya |
In-Person |
26 Oct 2026 – 4 Nov 2026 |
KES 240,000 |
| Riyadh, Saudi Arabia |
In-Person |
26 Oct 2026 – 4 Nov 2026 |
USD 9,000 |
| Kampala, Uganda |
In-Person |
2 Nov 2026 – 13 Nov 2026 |
USD 3,800 |
+230 more dates — view live schedule →
Frequently Asked Questions
Do I need to be an advanced Excel user to attend this course?
An intermediate level of Excel proficiency is recommended — you should be comfortable with basic formulas, formatting, and navigating workbooks. Advanced topics such as Power Query, Power Pivot, and VBA are introduced progressively, so no prior experience with those tools is required.
Will I receive a certificate upon completing the course?
Yes. All participants who complete the full ten-day programme will receive a Certificate of Completion from TrainingComplex, confirming their training in Microsoft Excel for Data Analytics.
Do I need to bring my own laptop?
Yes, participants are required to bring a laptop installed with Microsoft Excel 2016 or a later version (Microsoft 365 is strongly recommended). The course involves continuous hands-on practice, and having your own device ensures you can retain all files and exercises after the training.
Is this course available online for participants who cannot travel?
Yes. The course is offered as a Live Virtual option with the same content, facilitator interaction, and hands-on exercises delivered via a live online platform. Participants receive all datasets and exercise files digitally in advance of each session.
What datasets will be used during the training?
TrainingComplex uses carefully curated, real-world-style datasets drawn from sectors including finance, human resources, supply chain, monitoring and evaluation, and sales. These datasets reflect the types of data challenges delegates are likely to encounter in their professional roles.
