Advanced Microsoft Excel Training Course

Duration 5 days 10 modules · 33 contact hours
Level Advanced Certificate on completion
Who attends Financial Analysts, Business Analysts and others
Fee from USD 1,100 KSh 95,000 for Kenyan organisations
Where 52 cities + online See dates by city
Next start 16 Nov 2026 Course code SD-DA-091

Before you join: Participants should be confident with everyday Excel tasks, including entering formulas, using relative and absolute references, sorting and filtering lists, and creating basic charts.

Dates & prices

Two start dates a month in every city, and live online on weekdays or weekends. Fees are per participant; classroom fees include tuition, materials, lunch and refreshments, and the certificate.

Suggest a date or city

Showing the next six months. Later dates, other cities and private deliveries for your team are available on request.

Course overview

Excel workbooks often become critical reporting systems without the design discipline, data controls or audit trails needed for dependable decisions. Analysts may spend hours reconciling monthly files, correcting formula errors, rebuilding reports or explaining inconsistent totals. Advanced Excel capability reduces this rework by turning raw operational, financial and customer data into controlled models, repeatable reports and decision-ready analysis.

This course develops the formula, modelling, data preparation and reporting methods expected of experienced Excel users. Participants build reliable calculation models with dynamic arrays, XLOOKUP, LET and LAMBDA; analyse operational data with PivotTables and statistical functions; create sensitivity models; and produce management dashboards. The course also covers Power Query for repeatable data transformation, Power Pivot and DAX for multi-table analysis, and VBA fundamentals for automating standard reporting tasks.

Instruction combines concise technical briefings with realistic cases drawn from finance, operations, sales and project reporting. Participants work through formula design, data-cleaning decisions, reporting requirements and model quality checks using supplied datasets. They leave with a reusable Excel reporting and modelling toolkit: workbook architecture guidance, formula patterns, dashboard layouts, Power Query designs, DAX measure examples and a personal application plan for priority reports.

The course suits professionals who already use Excel regularly and need to build, review or improve analytical workbooks used by managers, colleagues or clients. It is particularly relevant where reporting depends on recurring extracts, multiple source files, complex calculations or high scrutiny of figures.

What you will be able to do

  • Build controlled calculation models using structured references, dynamic arrays and named ranges
  • Design multi-criteria formulas using XLOOKUP, INDEX-MATCH, LET and LAMBDA
  • Analyse operational datasets with PivotTables, statistical functions and exception checks
  • Transform recurring source data using Power Query queries, joins and append operations
  • Model forecasts and sensitivities using Data Tables, Goal Seek and scenario analysis
  • Create decision-focused dashboards using PivotCharts, conditional formatting and dynamic ranges
  • Automate repeatable reporting procedures with recorded macros and basic VBA code
  • Apply workbook quality controls covering documentation, reconciliation and version management

Benefits of attending

For you

  • Reduce time spent rebuilding recurring reports from manual extracts
  • Produce auditable models that withstand manager and stakeholder review
  • Diagnose formula errors and inconsistent totals with structured checking methods
  • Build dashboard reports that focus attention on exceptions and decisions
  • Automate standard workbook tasks using recorded macros and basic VBA

For your organisation

  • Shorten monthly reporting cycles through repeatable queries and automated procedures
  • Improve confidence in management information through reconciliation and quality controls
  • Reduce spreadsheet errors caused by copied formulas and unmanaged source files
  • Create consistent reporting models that colleagues can review and maintain
  • Increase analytical capacity without moving every reporting requirement into specialist systems

Course modules

10 modules over 5 days

Participants can construct maintainable calculation models using modern Excel formula methods.

  • Structured references, named ranges and table objects for durable calculation models
  • IF, IFS, SWITCH and nested logic for controlled decision rules
  • XLOOKUP, XMATCH and INDEX-MATCH selected by retrieval requirement
  • Dynamic arrays using FILTER, SORT, UNIQUE and SEQUENCE functions
  • LET and LAMBDA functions used to reduce repeated formula logic
  • Formula auditing with precedents, dependents, Evaluate Formula and error checks

Participants can prepare reliable source data and apply input and worksheet controls.

  • Excel Tables configured with typed columns, calculated fields and totals
  • Data validation lists, custom rules and input-message controls
  • TRIM, CLEAN, SUBSTITUTE and TEXTSPLIT applied to inconsistent source values
  • Date, time and text conversions using VALUE, DATEVALUE and TEXT
  • Duplicate detection through COUNTIFS, conditional formatting and unique-key checks
  • Workbook protection, locked cells and permitted input ranges

Participants can calculate business metrics, rankings and exceptions from detailed datasets.

  • SUMIFS, COUNTIFS and AVERAGEIFS for multi-criteria operational metrics
  • Criteria syntax using wildcards, dates, inequalities and cell references
  • AGGREGATE and SUBTOTAL functions for filtered-list calculations and exception handling
  • Ranking, percentile and quartile analysis with RANK, PERCENTILE and QUARTILE
  • Statistical summaries using MEDIAN, MODE, STDEV and confidence intervals
  • Error-handling patterns using IFERROR, IFNA and intentional error flags

Participants can build forecast models and test the impact of changing assumptions.

  • Time-series models built with monthly drivers, assumptions and scenario inputs
  • Forecast formulas using growth rates, seasonality and rolling periods
  • Goal Seek applied to break-even, margin and volume assumptions
  • Data Tables calculating one-variable and two-variable sensitivity results
  • Scenario Manager used to compare named planning cases
  • NPV, XNPV, IRR and XIRR for investment appraisal

Participants can summarise and interrogate large datasets through controlled pivot reports.

  • PivotTables built from clean tables with appropriate field data types
  • Grouping dates, numeric bands and hierarchy levels within PivotTables
  • Calculated fields, Show Values As and custom number formats
  • Slicers, timelines and report connections for controlled filtering
  • GETPIVOTDATA formulas linked to selected PivotTable report outputs and controls
  • PivotChart choices matched to comparisons, trends and composition

Participants can create clear dashboards that support timely management decisions.

  • Chart selection based on comparison, distribution, relationship and time-series questions
  • Combo charts with secondary axes used only for compatible scales
  • Dynamic charts driven by tables, named ranges and spill formulas
  • Conditional formatting using formulas, icon sets and data bars
  • Dashboard layouts structured around decisions, exceptions and supporting detail
  • Accessible formatting through meaningful titles, labels, contrast and number formats

Participants can build refreshable Power Query processes for recurring source data.

  • Power Query interface, query steps and applied-step dependencies
  • Importing CSV, text, folder and workbook data sources
  • Data types, locale settings and error replacement during transformation
  • Appending monthly files and merging lookup tables with join types
  • Unpivoting cross-tab reports into normalized, analysis-ready transaction records
  • Query parameters, refresh settings and source-path management

Participants can analyse related tables using Excel Data Models and DAX measures.

  • Data Model relationships defined with unique keys and cardinality checks
  • Star-schema design separating fact tables from dimension tables
  • Power Pivot measures compared with calculated columns and implicit aggregations
  • DAX measures using CALCULATE, SUMX, DISTINCTCOUNT and DIVIDE
  • Filter context and row context diagnosed through measure results
  • Calendar tables supporting year-to-date, prior-period and rolling measures

Participants can automate common worksheet and reporting procedures using basic VBA.

  • Macro security settings, trusted locations and safe workbook distribution
  • Recording macros to inspect generated Visual Basic for Applications code
  • Relative references and control buttons for repeatable worksheet procedures
  • VBA variables, ranges, loops and conditional statements for simple automation
  • Reusable procedures for formatting, exporting and refreshing standard reports
  • Debugging with breakpoints, step execution and Immediate Window inspection

Participants can review workbook risks and plan controlled improvements to business reporting.

  • Workbook architecture separating inputs, calculations, outputs and documentation
  • Model documentation using assumptions registers, source notes and change logs
  • Quality assurance checks for balances, reconciliations, exceptions and reasonableness
  • Performance tuning through calculation settings, efficient formulas and query design
  • Version control conventions for filenames, releases and approved workbook copies
  • Personal application plan selecting priority reports, data sources and controls

Exercises and examples are adapted to each cohort's sectors and roles.

What you take back to work

  1. Advanced formula pattern library
  2. Workbook quality assurance checklist
  3. Forecast sensitivity model template
  4. Management dashboard layout template
  5. Power Query transformation design
  6. Excel reporting application plan

Who should attend

  • Financial Analysts — build auditable forecasts, budget models and management reporting
  • Business Analysts — convert operational data into controlled analysis and decision reports
  • Management Accountants — improve recurring close, variance and performance reporting
  • Data Analysts — prepare source files and analyse multi-table datasets within Excel
  • Project Managers — track delivery, resources, milestones and programme exceptions
  • Operations Managers — monitor service levels, volumes, costs and performance trends

Skills and tools covered

Formula architectureData transformationPivotTable analysisFinancial modellingDashboard designVBA automation

Tools and standards

Power Query, Power Pivot, Data Analysis Expressions (DAX), Visual Basic for Applications (VBA)

How the course runs

Training methodology

The course uses short instructor briefings followed by guided case work, peer review and individual workbook development. Cases include budget forecasting, sales reporting, operational performance analysis and recurring data consolidation. Participants practise formula selection, query design, PivotTable analysis, dashboard construction and model checking against stated reporting requirements. Group discussions examine alternative approaches and common failure points. Each participant completes an application plan identifying reports, data sources, controls and automation opportunities relevant to their role.

Requirements and prerequisites

Participants should be confident with everyday Excel tasks, including entering formulas, using relative and absolute references, sorting and filtering lists, and creating basic charts. Prior experience with SUMIFS, IF statements and PivotTables is useful, though not essential. The course assumes participants can work independently in a Windows desktop Excel environment and interpret business data such as dates, amounts and categories. No prior programming, VBA, Power Query, Power Pivot, DAX, statistics or financial modelling experience is required. It is not designed for complete beginners.

A typical training day

08:30 – 10:30First session
10:30 – 10:45Refreshment break
10:45 – 12:30Second session
12:30 – 13:30Lunch and networking
13:30 – 15:00Third session
15:00 – 15:15Refreshment break
15:15 – 16:30Workshop and daily review

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

What the fee includes

  • Instruction by a practitioner facilitator
  • Full course workbook and materials
  • Exercise files, templates and case studies
  • Refreshments and lunch on classroom deliveries
  • Certificate of completion
  • Post-course application plan

Frequently asked questions

Participants should already be comfortable entering formulas, using relative and absolute references, sorting and filtering data, and creating basic charts. Experience with PivotTables and functions such as IF or SUMIFS is useful but not mandatory.

Bring a laptop with a current Windows desktop version of Microsoft Excel, ideally Microsoft 365, if attending classroom delivery. Supplied datasets are used during training; participants may also bring sanitised examples of their own reporting challenges.

The course is designed for analysts, accountants, reporting professionals, managers and project staff who use Excel for recurring analysis or decision support. It suits people who need more than basic formulas and charts but do not need a specialist data engineering course.

Beginner and intermediate courses focus on worksheet basics, simple formulas, formatting and standard lists. This course concentrates on model architecture, dynamic arrays, Power Query, Data Models, DAX, dashboard reporting and VBA-based automation.

Participants use methods suited to recurring reports, forecasts, reconciliations, operational dashboards and source-data consolidation. The final application plan identifies specific workbooks and reporting processes where the methods can be introduced.

Training runs from 08:30 to 16:30 each day, providing about 6.5 contact hours daily. The five days cover ten modules in teaching order, with time allocated to briefings, guided analysis, peer discussion and individual application planning.

Questions about this course?

Ask about dates, fees, group bookings or running it for your team. Our training team replies by email.

Ask on WhatsApp info@skillsetdevelopment.com

More courses in Data Analytics

Data Analytics Advanced Data Analysis Using R Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Advanced Excel Formulas and Functions Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Advanced Excel Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Advanced Excel VBA Automation Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Advanced NVivo Data Analysis Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Advanced SPSS Data Analysis Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics ArcGIS Pro Spatial Analysis and Mapping Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Big Data for Managers Training Course 10 days · from USD 2,200 · next 16 Nov