Advanced 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-089

Before you join: Participants should be confident with everyday Excel tasks, including entering formulas, formatting worksheets, sorting and filtering data, and using basic functions such as SUM, IF and COUNTIF.

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

Complex Excel workbooks often become difficult to trust when formulas are copied inconsistently, source data changes each month, PivotTables are manually adjusted, and dashboards contain figures that cannot be traced. Analysts and reporting teams need models that calculate correctly, refresh predictably and communicate exceptions clearly. This course addresses the practical work of turning operational, financial and customer data into controlled analysis and decision-ready reporting.

Participants build advanced capability across formulas, dynamic arrays, lookup methods, Excel Tables, PivotTables, Power Query, the Data Model and DAX measures. They learn to clean and combine monthly files, create multi-criteria calculations, model relationships between datasets, analyse trends and variances, and present results through interactive dashboards. The course also covers calculation auditing, error handling, sensitivity analysis, workbook protection and appropriate VBA automation for repeatable tasks.

Teaching combines focused instructor briefings, guided case work, group problem-solving, reporting simulations and application planning. Cases reflect common requirements such as budget variance reporting, customer analysis, payroll reconciliations, procurement tracking and monthly management information. Participants receive a certificate of completion and leave with reusable templates, formula patterns, dashboard structures and a workplace application plan. The course is suited to professionals who already use Excel regularly and need stronger methods for analytical reporting, data preparation and controlled workbook design.

What you will be able to do

  • Build auditable calculation models using named ranges, structured references and formula tracing
  • Clean and standardise source data using Excel Tables, validation rules and Power Query
  • Construct multi-criteria calculations using XLOOKUP, INDEX-MATCH, SUMIFS and dynamic arrays
  • Create management reports using PivotTables, PivotCharts, slicers and calculated measures
  • Model related datasets in the Excel Data Model using relationships and DAX measures
  • Analyse trends, outliers, scenarios and target values using statistical and what-if tools
  • Design interactive dashboards that communicate performance, variance drivers and exceptions
  • Automate repeatable workbook tasks using recorded macros, VBA procedures and controlled distribution

Benefits of attending

For you

  • Produce refreshable monthly reports with fewer manual calculation steps
  • Diagnose formula errors and trace figures back to source data
  • Build dashboards suitable for management meetings and performance reviews
  • Use Power Query to prepare recurring files without repeated copy-and-paste work
  • Apply VBA automation to personal reporting tasks with appropriate controls

For your organisation

  • Reduce manual handling errors in recurring financial and operational reports
  • Shorten reporting cycles through refreshable queries and reusable model structures
  • Improve traceability of calculations, source data and reporting assumptions
  • Provide managers with consistent dashboards for variance and exception review
  • Increase analyst capacity by automating repetitive workbook preparation tasks

Course modules

10 modules over 5 days

Participants can build traceable calculation models with controlled references, reusable logic and error handling.

  • Relative, absolute and mixed references controlled in multi-sheet calculation models
  • Named ranges and structured references used to improve formula readability
  • Audit precedents, dependents and formula errors with Excel tracing tools
  • IFERROR, LET and LAMBDA functions reduce repeated calculation logic
  • Build nested logical tests using IF, IFS, AND and OR
  • Apply data validation lists, input messages and error alerts
  • Protect formula cells while permitting controlled user input

Participants can prepare consistent, analysis-ready datasets using Excel Tables and data quality controls.

  • Convert raw ranges into Excel Tables with stable field names
  • Standardise dates, text, blanks and numeric formats before analysis
  • Identify duplicate records using conditional formatting and COUNTIFS tests
  • Split, combine and clean fields with Text to Columns
  • Apply custom number formats for units, variances and exceptions
  • Use SUBTOTAL and AGGREGATE to calculate filtered-list results
  • Document source fields, business rules and refresh assumptions

Participants can retrieve, reconcile and calculate data accurately across multiple business datasets.

  • Retrieve records with XLOOKUP, XMATCH and INDEX-MATCH combinations for resilient models
  • Select exact, approximate and wildcard matches for business keys
  • Return multiple criteria results using XLOOKUP and concatenated keys
  • Calculate conditional totals, counts and averages with SUMIFS family
  • Construct two-way lookups with INDEX, MATCH and MATCH
  • Handle missing keys, duplicate matches and lookup error states
  • Compare lookup methods for speed, maintainability and version compatibility

Participants can create flexible formula-driven outputs that adapt when source data changes.

  • Spill dynamic arrays using FILTER, SORT, UNIQUE and SEQUENCE functions
  • Create exception lists from multiple conditions with FILTER
  • Use TAKE, DROP, CHOOSECOLS and VSTACK to reshape outputs
  • Generate monthly calendars and reporting periods with EDATE functions
  • Apply SUMPRODUCT for array calculations without helper columns
  • Build reusable custom functions through named LAMBDA definitions
  • Manage spill ranges, implicit intersection and calculation dependencies

Participants can create refreshable summary reports that support controlled data exploration.

  • Create PivotTables from Tables, ranges and approved external connections
  • Group dates, numeric bands and hierarchy levels for management reporting
  • Configure value fields, custom calculations and Show Values As
  • Add slicers, timelines and report filters for controlled exploration
  • Build PivotCharts aligned to decision questions and audience needs
  • Refresh, change data sources and retain formatting reliably
  • Use GETPIVOTDATA to link reporting models to pivot outputs

Participants can import, clean, combine and refresh recurring datasets through Power Query.

  • Import CSV, workbook, folder and database extracts through Power Query
  • Profile columns to identify errors, nulls, distributions and quality issues
  • Transform data types, headers, whitespace and inconsistent category labels
  • Merge queries using key fields and controlled join types
  • Append monthly files into refreshable transaction datasets for analysis
  • Create conditional columns, index fields and parameter-driven filters
  • Document query steps and manage refresh credentials appropriately

Participants can build relational Excel data models and calculate analytical measures using DAX.

  • Load related tables into the Excel Data Model
  • Define one-to-many relationships using verified dimension and fact keys
  • Distinguish calculated columns from measures in analytical models
  • Write DAX measures using SUM, CALCULATE and DISTINCTCOUNT
  • Apply filter context through CALCULATE, ALL and FILTER functions
  • Build calendar tables and time intelligence with DATEADD
  • Diagnose relationship ambiguity, blank results and double counting

Participants can assess trends, exceptions and scenarios using Excel analytical tools.

  • Summarise distributions using descriptive statistics and percentile measures
  • Apply conditional formatting rules to identify outliers and threshold breaches
  • Estimate relationships with CORREL, regression charts and trendlines
  • Use Forecast Sheet outputs with documented assumptions and limits
  • Build sensitivity tables with Data Table and scenario inputs
  • Set target values using Goal Seek and Solver constraints
  • Calculate date intervals, working days and business-period metrics

Participants can design Excel dashboards that focus management attention on material decisions and exceptions.

  • Translate management questions into measures, dimensions and visual priorities
  • Design dashboard layouts using hierarchy, proximity and consistent formatting
  • Select charts that distinguish trend, composition, comparison and distribution
  • Build variance bridges with waterfall charts and labelled drivers
  • Apply conditional formats and sparklines for rapid exception scanning
  • Control user interaction through slicers, drop-downs and linked charts
  • Test dashboards for readability, refresh status and decision relevance

Participants can automate selected Excel tasks and establish controls for reliable workbook distribution.

  • Record repeatable tasks with absolute and relative macro references
  • Edit recorded VBA procedures using variables, loops and error handling
  • Assign macros to buttons while managing trusted workbook settings
  • Use worksheet protection, locked cells and permitted edit ranges
  • Apply workbook inspection before distribution to remove hidden content
  • Establish file naming, version control and refresh ownership conventions
  • Draft a workplace application plan for one reporting process

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

What you take back to work

  1. Advanced formula pattern library
  2. Excel data quality checklist
  3. Power Query transformation template
  4. PivotTable reporting model
  5. Management dashboard wireframe
  6. Workbook automation and control plan

Who should attend

  • Financial Analysts — build controlled models, forecasts and management reports from changing data
  • Business Analysts — combine operational datasets and produce decision-focused analysis
  • Management Accountants — automate variance reporting and improve auditability of calculations
  • Data Analysts — prepare source files and model reporting data within Excel
  • Project Managers — track delivery metrics, resource data and portfolio reporting
  • Operations Managers — monitor service, procurement and performance exceptions through dashboards

Skills and tools covered

Advanced formula designPower Query transformationPivotTable reportingDAX data modellingDashboard designVBA task automation

Tools and standards

Microsoft Excel, Power Query, Power Pivot, DAX

How the course runs

Training methodology

The course uses short technical briefings followed by guided case work based on financial, operational and customer reporting data. Participants work individually and in groups to diagnose workbook issues, construct formulas, transform data, build models and present findings. Reporting simulations develop judgement about chart choice, dashboard structure and data quality controls. The instructor reviews alternative methods, common errors and version limitations. Participants finish with application planning for a reporting or analysis process they can improve after the course.

Requirements and prerequisites

Participants should be confident with everyday Excel tasks, including entering formulas, formatting worksheets, sorting and filtering data, and using basic functions such as SUM, IF and COUNTIF. Experience creating simple PivotTables is useful but not essential. Participants should have worked with business data such as budgets, transactions, customer records or operational reports. The course does not require programming experience, prior VBA knowledge, statistics training, database administration or previous use of Power Query, Power Pivot or DAX.

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, sorting and filtering data, and working with basic functions. Prior use of Power Query, Power Pivot, DAX and VBA is not required.

Bring a laptop with a current desktop version of Microsoft Excel where possible. Participants may also bring a non-sensitive example of a recurring report or dataset to inform their application plan.

The course suits analysts, accountants, reporting specialists, project staff and managers who work regularly with sizeable Excel files. It is particularly relevant for people responsible for monthly reporting, reconciliations, forecasts or performance dashboards.

This course assumes participants can already use standard formulas and worksheet features. It concentrates on advanced calculation design, dynamic arrays, Power Query, data modelling, DAX, dashboards and selected VBA automation.

Participants can use the methods to improve a recurring report, clean monthly source files, automate workbook preparation or redesign a management dashboard. The final module includes an application plan for identifying an appropriate process and setting practical controls.

Training runs from 08:30 to 16:30 each day, providing approximately 6.5 contact hours daily and 32.5 contact hours overall. Time is divided between instructor briefings, guided analysis, group work, reporting simulations and 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 Accounting Data Analytics with Python Training Course 10 days · from USD 2,200 · next 16 Nov 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 VBA Automation Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Advanced Microsoft Excel 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 Supply Chain Data Analytics Training Course 10 days · from USD 2,200 · next 16 Nov