Excel for Business Intelligence Training Course

Duration 5 days 10 modules · 33 contact hours
Level Intermediate Certificate on completion
Who attends Business Analyst, Data Analyst 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-BI-172

Before you join: Participants should already be regular Excel users and be comfortable creating formulas, sorting and filtering data, formatting worksheets, and building standard PivotTables.

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

Operational teams often hold sales, finance, customer, service and procurement data in separate spreadsheets, extracts and monthly files. Analysts can create PivotTables quickly, yet reports remain slow to refresh, difficult to reconcile and vulnerable to formula errors. This course addresses the gap between competent spreadsheet use and a controlled Excel-based business intelligence reporting process.

Participants build the technical and analytical skills expected of advanced Excel business intelligence practitioners. They structure source data, apply advanced formulas, import and transform files with Power Query, create star-schema Data Models, and write DAX measures for reusable calculations. The course also covers PivotTables, KPI reporting, dashboard design, data-quality checks, refresh controls and workbook performance.

Teaching combines concise instructor briefings with guided case work based on realistic reporting situations, such as monthly sales performance, budget variance, customer retention and service operations. Participants work through query transformations, model design decisions, calculation logic and dashboard requirements, then document a reporting process suitable for their own work. They leave with a BI-ready Excel reporting pack: a data model design, transformation specification, measure catalogue, dashboard wireframe, refresh runbook and personal application plan. A certificate of completion is issued.

The course suits professionals who already use Excel regularly and need to produce reliable management reporting from larger or more varied data sources. It is particularly relevant where Power BI is not yet available, where Excel remains the reporting tool of record, or where teams need stronger data preparation and modelling discipline before adopting wider BI platforms.

What you will be able to do

  • Design a star-schema Excel Data Model with defined fact tables, dimensions and relationships
  • Transform multi-source files into analysis-ready tables using Power Query
  • Build controlled calculations with XLOOKUP, dynamic arrays, LET and LAMBDA functions
  • Write DAX measures for KPIs, variances, time intelligence and filter-aware analysis
  • Produce interactive PivotTable reports with slicers, timelines and governed measures
  • Create management dashboards using KPI cards, variance charts and conditional formatting
  • Validate report outputs through reconciliation totals, exception checks and refresh controls
  • Develop a reporting handover pack containing documentation, refresh instructions and ownership actions

Benefits of attending

For you

  • Faster preparation of recurring management reports from multiple source files
  • A reusable method for building refreshable Excel reporting models
  • Stronger credibility when explaining KPI calculations and data exceptions
  • Practical DAX and Power Query skills for advanced analyst roles
  • A documented reporting pack for immediate workplace application

For your organisation

  • Reduced manual copy-and-paste work in monthly reporting cycles
  • More consistent KPI definitions across finance, sales and operations reports
  • Earlier detection of missing records, mismatched totals and data-quality issues
  • Improved auditability through documented transformations, measures and refresh controls
  • Better management decisions from timely, interactive performance reporting

Course modules

10 modules over 5 days

Participants can define a controlled workbook structure and assess data readiness for business intelligence reporting.

  • BI question framing: measures, dimensions, grain, refresh frequency and decision owners
  • Source-data profiling with tables, filters, sort orders and data types
  • Structured Excel Tables using stable names, calculated columns and totals
  • Data-validation rules for permitted values, dates, ranges and exception flags
  • Workbook architecture separating raw data, transformations, models, outputs and controls
  • Documenting field definitions, source ownership, refresh schedules and reconciliation points

Participants can create maintainable formula logic for controlled reporting outputs and exceptions.

  • XLOOKUP, XMATCH and INDEX-MATCH selection by key uniqueness and compatibility
  • SUMIFS, COUNTIFS and AVERAGEIFS calculations using explicit date and status criteria
  • Dynamic arrays with FILTER, UNIQUE, SORT and spill-range references
  • LET and LAMBDA functions for readable, reusable calculation logic
  • IFERROR, ISNUMBER and blank handling for controlled exception outputs
  • Formula auditing with precedents, dependents, Evaluate Formula and watch windows

Participants can import repeatable data sources and configure queries for controlled refreshes.

  • Query folding considerations when importing database and cloud-service extracts
  • Power Query connectors, query privacy levels and credential management
  • Importing CSV, folder, workbook, SQL Server and SharePoint sources
  • Applied Steps pane used to inspect transformation order and dependencies
  • Parameterised file paths and source settings for controlled report refreshes
  • Staging queries separated from load queries through query reference patterns
  • Refresh diagnostics identify source changes, failed credentials and schema mismatches

Participants can cleanse, combine and reshape source data into consistent reporting tables.

  • Data type assignment before joins, calculations and model loading
  • Text cleansing with Trim, Clean, Replace Values and case standardisation
  • Splitting, extracting and parsing compound fields with delimiters and positions
  • Merge queries using join kinds, key tests and unmatched-record review
  • Append queries to consolidate monthly files with consistent column structures
  • Unpivot transformations convert cross-tab layouts into analysis-ready attribute-value rows
  • Error rows, nulls and duplicates isolated through repeatable exception queries

Participants can build a relational Excel Data Model that supports reliable cross-filtered analysis.

  • Fact and dimension tables distinguished by business process and analytical grain
  • Star schema design with conformed dimensions and surrogate business keys
  • Excel Data Model relationships configured for one-to-many filter propagation
  • Calendar table created with contiguous dates, fiscal periods and working days
  • Many-to-many relationship risks resolved through bridge tables and allocation rules
  • Model loading choices: connection-only, worksheet table or Data Model
  • Relationship diagrams checked against source totals and expected filter paths

Participants can write DAX measures that respond correctly to report filters and time periods.

  • DAX measures distinguished from calculated columns through evaluation context
  • Explicit measures for revenue, margin, units and average transaction value
  • CALCULATE modifies filter context using ALL, REMOVEFILTERS and KEEPFILTERS
  • Time intelligence measures compare month-to-date, year-to-date and prior periods
  • DIVIDE, COALESCE and variables prevent misleading errors and repeated logic
  • Measure naming, display folders and format strings support report maintenance
  • Reconciliation measures test model results against controlled source aggregates

Participants can build interactive PivotTable reports that answer management performance questions.

  • PivotTable layouts answer variance, contribution, trend and ranking questions
  • Hierarchies, drill-down and Show Values As reveal drivers behind totals
  • Slicers, timelines and report connections coordinate multiple PivotTables
  • Calculated fields avoided where DAX measures provide governed calculations
  • Top N filters, sorting and grouping focus attention on material exceptions
  • GETPIVOTDATA formulas create controlled narrative outputs linked to PivotTables
  • PivotTable refresh settings and cache choices managed for reliable reporting

Participants can design Excel dashboards that communicate performance, variance and required action clearly.

  • Dashboard wireframes map executive questions to measures, visuals and actions
  • KPI cards show actuals, targets, variances and threshold-based status
  • Combo charts compare volume and rate measures without misleading scales
  • Variance charts use favourable-direction logic, labels and reference lines
  • Conditional formatting highlights exceptions through data bars, icons and rules
  • Small multiples and sparklines show local trends within compact report areas
  • Accessibility checks cover colour contrast, labels, number formats and layout order

Participants can establish reporting controls that improve accuracy, traceability and workbook reliability.

  • Control totals reconcile record counts, amounts and distinct keys after refresh
  • Data-quality rules test completeness, validity, uniqueness, consistency and timeliness
  • Refresh runbooks define source checks, refresh sequence, approvals and issue escalation
  • Workbook protection, locked cells and input controls reduce accidental changes
  • File naming, version control and distribution permissions preserve report traceability
  • Performance tuning reduces volatile formulas, unused ranges and excessive PivotCaches
  • Error-handling messages distinguish missing data, failed queries and invalid selections

Participants can plan and document the controlled rollout of an Excel business intelligence report.

  • Requirements-to-dashboard traceability links questions, measures, sources and report components
  • BI reporting backlog prioritised using decision value, effort and data readiness
  • Stakeholder review criteria assess accuracy, relevance, usability and refresh reliability
  • Personal application plan identifies first report, data owners, milestones and controls
  • Model handover checklist covers documentation, refresh instructions, access and support
  • Success measures track report adoption, refresh failures, cycle time and corrections

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

What you take back to work

  1. Excel BI workbook architecture template
  2. Power Query transformation specification
  3. Star-schema Data Model diagram
  4. DAX KPI measure catalogue
  5. Management dashboard wireframe
  6. Reporting refresh and handover checklist

Who should attend

  • Business Analyst — builds recurring reports from operational and financial data
  • Data Analyst — needs Excel-based transformation, modelling and reporting methods
  • Finance Analyst — produces budget, variance and management performance reports
  • Reporting Analyst — improves refresh reliability and calculation control in recurring packs
  • Operations Manager — requires decision-ready KPI reporting from service and process data
  • Project or Programme Officer — consolidates delivery metrics across multiple workstreams

Skills and tools covered

Power Query transformationData model designDAX measure writingPivotTable analysisDashboard developmentReporting governance

Tools and standards

Power Query, Power Pivot, DAX, Excel Data Model

How the course runs

Training methodology

The course uses short technical briefings followed by guided case work, group exercises and individual build activities in Excel. Participants apply Power Query transformations, Data Model relationships, DAX measures and PivotTable reporting to realistic management-reporting scenarios. Facilitated reviews compare alternative modelling and dashboard choices, with attention to calculation control and report usability. The final sessions use application planning to define each participant's first reporting use case, required data sources, ownership arrangements and refresh controls.

Requirements and prerequisites

Participants should already be regular Excel users and be comfortable creating formulas, sorting and filtering data, formatting worksheets, and building standard PivotTables. Prior experience working with business data such as transactions, budgets, customer records or operational metrics is useful. Participants should understand common measures including totals, averages, percentages and variances. Power Query, Power Pivot, DAX, SQL, VBA, Power BI and formal data-modelling experience are not required. The course teaches these Excel business intelligence methods from their practical starting point.

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

You should be comfortable with standard formulas, tables, sorting, filtering and ordinary PivotTables. You do not need previous experience with Power Query, Power Pivot, DAX, SQL, VBA or Power BI.

Bring a laptop with a current desktop version of Microsoft Excel and permission to install or use the Power Query and Data Model features available in your environment. You may also bring non-sensitive examples of reporting challenges for use during application planning.

It is designed for analysts, finance professionals, reporting specialists, operations staff and managers who create recurring Excel reports. It is most useful for people moving from manual spreadsheet consolidation to refreshable analytical reporting.

Standard Excel courses usually focus on worksheet features and formulas, while this course focuses on data preparation, modelling, governed measures and reporting processes. It teaches Excel-based business intelligence methods rather than Power BI report publishing and administration.

Participants can use the workbook architecture, query specification, Data Model design and refresh checklist for a current recurring report. Typical first applications include monthly sales packs, budget variance reports, customer metrics and service performance dashboards.

Training runs from 08:30 to 16:30 each day, with about 6.5 contact hours daily. The five days progress from workbook design and advanced formulas through Power Query, data modelling, DAX, dashboards, controls 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 Business Intelligence

Business Intelligence Data Visualisation for Business Training Course 10 days · from USD 2,200 · next 16 Nov Business Intelligence Excel Business Reporting and Dashboards Training Course 5 days · from USD 1,100 · next 16 Nov Business Intelligence Excel Dashboard Design for Management Reporting Training Course 5 days · from USD 1,100 · next 16 Nov Business Intelligence Excel Dashboard Development Training Course 5 days · from USD 1,100 · next 16 Nov Business Intelligence Excel Power Query and Power Pivot Training Course 5 days · from USD 1,100 · next 16 Nov Business Intelligence Financial Dashboarding with Excel Training Course 5 days · from USD 1,100 · next 16 Nov Business Intelligence Financial Modelling with Power BI Training Course 5 days · from USD 1,100 · next 16 Nov Business Intelligence Operational Dashboards Using Excel Training Course 5 days · from USD 1,100 · next 16 Nov