Excel for Business Analytics Training Course

Duration 5 days 10 modules · 33 contact hours
Level Intermediate Certificate on completion
Who attends Business Analysts, Financial 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-110

Before you join: Participants should be confident with everyday Excel tasks, including entering formulas, formatting worksheets, sorting and filtering data, 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

Business teams often hold critical operational, financial and customer data in spreadsheets that are difficult to refresh, reconcile or explain. Manual copying, inconsistent formulas and poorly controlled workbooks can delay monthly reporting, weaken decisions and create avoidable control risks. Analysts need to turn raw extracts into reliable models, clear management reporting and repeatable analysis without relying on specialist programming tools for every request.

This course develops advanced Excel capability for analysing, modelling and presenting business data. Participants use Excel Tables, structured references, logical and lookup formulas, dynamic arrays, PivotTables, Power Query, Power Pivot and DAX measures. They build data models from multiple sources, test data quality, analyse variance and trends, produce forecasts and create decision-focused dashboards. The course also addresses workbook structure, audit checks, documentation and controls required for reports used by finance, operations, sales and programme teams.

Teaching combines short instructor briefings with guided demonstrations, individual workbook analysis, team case work and discussion of reporting decisions. Cases use realistic sales, budget, procurement, customer and service-delivery datasets. Participants leave with a reusable Excel analytics workbook containing a cleaned data table, query steps, data model, measures, dashboard layout, forecast assumptions and quality-control checks. The course includes five instructor-led days, delivered in a classroom in more than 50 cities or live online, with approximately 6.5 contact hours each day. Participants receive a certificate of completion.

The course suits professionals who already use Excel for reporting or analysis and need stronger methods for repeatable, decision-ready workbooks. It is particularly relevant where teams receive frequent data extracts and must convert them into trusted management information.

What you will be able to do

  • Structure business datasets using Excel Tables, data types and documented field definitions
  • Clean and combine source files using repeatable Power Query transformations
  • Build analytical formulas using XLOOKUP, SUMIFS, dynamic arrays and error handling
  • Create relational Excel data models using Power Pivot and defined table relationships
  • Write DAX measures for aggregations, ratios, time comparisons and variance analysis
  • Produce PivotTable analyses and interactive dashboards for management reporting
  • Develop forecasts and scenarios using assumptions, sensitivity analysis and trend methods
  • Apply workbook controls, reconciliation checks and documentation for reliable reporting

Benefits of attending

For you

  • Reduce manual spreadsheet preparation through reusable query and model workflows
  • Build management reports that can be refreshed from new source extracts
  • Explain business variances with traceable calculations and drill-down analysis
  • Create stronger evidence for budget, sales and operational recommendations
  • Improve confidence reviewing formula logic, data quality and workbook controls

For your organisation

  • Shorten reporting cycles through standardised data preparation and refresh steps
  • Reduce spreadsheet error risk through reconciliations, checks and documented assumptions
  • Improve consistency between finance, operations and commercial performance reports
  • Provide managers with drillable dashboards instead of static spreadsheet packs
  • Retain repeatable analytical methods when reporting responsibilities change

Course modules

10 modules over 5 days

Participants can prepare source data in a structure suitable for repeatable Excel analysis.

  • Excel Tables with structured references, calculated columns and consistent field names
  • Data types, date formats and identifier rules for reliable joins
  • Tidy data principles: one record per row, one variable per column
  • Source-data profiling using filters, conditional formatting and duplicate detection
  • Named ranges and table naming conventions for readable workbook formulas
  • Data dictionaries documenting fields, calculation rules, owners and refresh frequency

Participants can build transparent formulas that calculate, retrieve and test business information.

  • XLOOKUP and XMATCH for controlled retrieval from reference tables
  • SUMIFS, COUNTIFS and AVERAGEIFS for multi-criteria performance analysis
  • IFS, SWITCH and nested IF statements for business-rule classification
  • Dynamic arrays using FILTER, SORT, UNIQUE and SEQUENCE functions
  • LET function variables for shorter, more auditable calculation logic
  • IFERROR and IS functions for exception handling and data checks
  • Formula auditing with precedents, dependents, Evaluate Formula and Watch Window

Participants can create refreshable Power Query processes for cleaning and combining business data.

  • Power Query interface, query settings and applied-step documentation
  • Importing Excel workbooks, CSV files, folders and structured web tables
  • Changing data types before calculations, merges and data-model loading
  • Removing errors, blanks, duplicates and non-printing source characters
  • Splitting, replacing and extracting text fields using transformation commands
  • Appending monthly files into a single refreshable transaction table
  • Merging reference tables using keys and controlled join types
  • Query parameters and refresh settings for repeatable reporting cycles

Participants can construct a relational model that supports accurate multi-table analysis.

  • Fact and dimension tables for transactions, dates, products and customers
  • Star schema design for efficient reporting and understandable relationships
  • Primary keys, foreign keys and cardinality checks before model loading
  • Power Pivot data loading and relationship creation between model tables
  • Calendar tables supporting monthly, quarterly and year-to-date analysis
  • Avoiding duplicate totals caused by many-to-many relationship structures
  • Model documentation covering source tables, refresh sequence and calculation ownership

Participants can write DAX measures for business performance, ratios and time comparisons.

  • Measures versus calculated columns: choosing the correct calculation method
  • SUM, DISTINCTCOUNT and DIVIDE measures for core performance indicators
  • CALCULATE and filter context for controlled metric definitions
  • Year-to-date, prior-period and growth measures using date intelligence
  • Variance measures for actual, budget, forecast and prior-year comparisons
  • Percentage, margin and average measures with zero-denominator handling
  • Measure naming, display folders and format strings for report usability

Participants can analyse modelled data through PivotTables, slicers and drill-down views.

  • PivotTable field placement for dimensions, measures, filters and comparisons
  • Grouping dates into months, quarters and financial reporting periods
  • Show Values As calculations for shares, rankings and running totals
  • Slicers and timelines controlling multiple PivotTables from shared data
  • Drill-down and detail extraction for investigating unexpected results
  • Calculated fields limitations compared with Power Pivot measures
  • PivotTable refresh controls and source validation before distribution

Participants can design concise Excel dashboards that communicate performance and exceptions.

  • Decision questions translated into measures, dimensions, thresholds and reporting cadence
  • Dashboard wireframes separating headline indicators, trends and diagnostic detail
  • KPI design using targets, tolerances, status indicators and owner assignments
  • Column, line, bar and waterfall charts matched to analytical purpose
  • Variance charts showing actual, budget, forecast and prior-period movement
  • Conditional formatting for exceptions, trend signals and threshold breaches
  • Layout rules for readable labels, visual hierarchy and controlled colour use

Participants can build assumption-driven forecasts and test the effect of key business drivers.

  • Driver-based forecasts linking volume, price, cost and capacity assumptions
  • Trend analysis using moving averages, growth rates and seasonal comparisons
  • FORECAST.ETS and TREND functions for time-series estimation
  • Scenario Manager for base, upside and downside planning cases
  • Data Tables testing one-variable and two-variable assumption sensitivity
  • Goal Seek for required sales, margin or cost-reduction targets
  • Forecast-versus-actual variance analysis with assumptions and corrective actions

Participants can apply practical controls that make analytical workbooks reliable and reviewable.

  • Input, calculation and output sheets separated within workbook architecture
  • Control totals reconciling source records, query outputs and report measures
  • Data validation lists preventing invalid categories, dates and assumptions
  • Formula consistency checks using error flags and exception reports
  • Sheet protection, cell locking and controlled input areas
  • Version control conventions for files, assumptions and reporting periods
  • Documentation notes covering sources, transformations, measures and known limitations

Participants can plan an Excel analytics solution for a defined workplace reporting requirement.

  • Reporting requirements mapped to decisions, users, measures and delivery dates
  • Source-system assessment covering availability, granularity, quality and refresh timing
  • Analytical workflow from extraction through transformation, modelling and dashboard publication
  • Priority backlog for quick improvements, controls and future automation
  • Stakeholder review criteria for accuracy, clarity, relevance and actionability
  • Personal application plan defining first workbook, milestones and review points

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

What you take back to work

  1. Excel analytics workbook template
  2. Power Query transformation checklist
  3. Star schema data-model design sheet
  4. DAX measure library
  5. Management dashboard wireframe
  6. Workbook control and reconciliation checklist

Who should attend

  • Business Analysts — need repeatable analysis from operational and commercial data
  • Financial Analysts — need controlled models, variance reporting and forecast scenarios
  • Management Accountants — need faster monthly reporting from multiple source extracts
  • Operations Managers — need service, capacity and productivity dashboards
  • Sales Operations Analysts — need pipeline, customer and territory performance analysis
  • Programme and M&E Officers — need reliable reporting from delivery and beneficiary data

Skills and tools covered

Data preparationExcel data modellingDAX measure writingDashboard designForecast analysisWorkbook controls

Tools and standards

Microsoft Excel, Power Query, Power Pivot, DAX

How the course runs

Training methodology

The instructor uses short briefings and live Excel demonstrations to introduce each method, followed by guided workbook work using realistic business datasets. Individual tasks build formula, query and modelling fluency; group case work tests interpretation of sales, budget and operational results. Participants compare alternative reporting choices, review common spreadsheet errors and practise explaining findings to decision makers. The final sessions use application planning to identify source data, refresh routines, controls and dashboard priorities for a current workplace reporting need.

Requirements and prerequisites

Participants should be confident with everyday Excel tasks, including entering formulas, formatting worksheets, sorting and filtering data, and creating basic charts. They should understand rows, columns, worksheets, relative and absolute cell references, and simple functions such as SUM, IF and AVERAGE. Experience using PivotTables is helpful but not essential. Participants should bring a laptop with desktop Excel for Windows, preferably Microsoft 365. No programming, database administration, statistics qualification or prior Power Query, Power Pivot or DAX experience is required.

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 already be comfortable with basic formulas, sorting, filtering and worksheet navigation. The course begins with data structure and progresses to advanced analysis, so prior Power Query, Power Pivot and DAX experience is not required.

Bring a laptop with desktop Excel for Windows, preferably Microsoft 365, and a charger. You may bring a non-sensitive sample report or anonymised data extract for use during application planning.

It is designed for analysts, accountants, reporting specialists, managers and programme staff who regularly prepare business reports from spreadsheet extracts. It is not intended for complete Excel beginners.

Basic Excel courses focus on everyday worksheet skills, while this course concentrates on repeatable data preparation, modelling, DAX measures, forecasting and controlled reporting. It also goes beyond chart creation by addressing source data, calculation logic and refresh processes.

Participants can use the methods to replace manual monthly consolidation, build refreshable management reports and investigate performance variances. The reusable workbook template and checklists provide a starting structure for workplace reporting needs.

The course runs from 08:30 to 16:30 each day, with approximately 6.5 contact hours daily. Time is distributed across ten modules covering data preparation, formulas, Power Query, modelling, DAX, reporting, forecasting and workbook controls.

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 Excel Data Analysis and Visualisation Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Excel Data Management and Analysis Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Excel Data Modelling and Simulation Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Excel Data Visualisation and Analytics Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Financial Data Analytics Using Python Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics IBM SPSS Statistics Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Microsoft Excel Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics NVivo Qualitative Data Analysis Training Course 10 days · from USD 2,200 · next 16 Nov