Advanced Excel Dashboards Training Course

Duration 5 days 10 modules · 33 contact hours
Level Advanced Certificate on completion
Who attends Business Intelligence 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 9 Nov 2026 Course code SD-BI-054

Before you join: Participants should already use Excel regularly and be comfortable creating formulas, sorting and filtering data, working with worksheets, and building basic charts and 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

Excel dashboards are often expected to turn finance, operations, sales, HR or programme data into decisions, yet many workbooks remain static report packs with inconsistent measures, slow calculations and charts that obscure exceptions. Advanced dashboard developers need to define the decision required, structure source data, calculate measures correctly and present results so managers can identify performance gaps without searching through worksheets.

This course develops the Excel skills needed to design, build and maintain interactive management dashboards. Participants define KPI requirements, prepare data with Excel Tables and Power Query, create relational models with Power Pivot, and calculate measures using DAX. They apply chart-selection rules, visual hierarchy, conditional formatting, PivotTables, slicers, timelines, dynamic arrays and form controls to create focused dashboard views. The course also addresses calculation performance, refresh routines, reconciliation controls, documentation and stakeholder sign-off.

Delivery combines instructor demonstrations, short technical briefings, case work using realistic reporting data, group exercises, simulations of dashboard review meetings and individual application planning. Participants build and refine a dashboard prototype during the course, using either supplied case data or an approved work example. They leave with a reusable dashboard requirements brief, KPI dictionary, data-model design, dashboard wireframe and refresh-and-quality checklist.

The course suits experienced Excel users who produce recurring reports or support management information. It is particularly relevant to analysts and reporting specialists working with large extracts, multiple data sources, recurring performance packs and demanding internal stakeholders.

What you will be able to do

  • Define dashboard requirements using decision questions, KPI specifications and audience needs
  • Prepare reporting data with Excel Tables, structured references and Power Query transformations
  • Build relational Excel data models using Power Pivot relationships and calendar tables
  • Create DAX measures for ratios, variances, distinct counts and conditional KPIs
  • Design dashboard layouts using visual hierarchy, Gestalt principles and chart-selection rules
  • Construct interactive dashboard views using PivotTables, slicers, timelines and dynamic arrays
  • Test dashboard accuracy through reconciliation, control totals, exception reports and KPI sign-off
  • Produce a maintainable dashboard build plan with refresh, ownership and adoption actions

Benefits of attending

For you

  • Build management dashboards that answer defined operational and financial questions
  • Reduce manual monthly reporting through repeatable queries, models and refresh routines
  • Present KPI exceptions with clearer charts, thresholds and drill-down views
  • Diagnose formula, source-data and model errors before dashboard publication
  • Create a portfolio-quality Excel dashboard prototype for immediate workplace use

For your organisation

  • Shorten recurring reporting cycles by standardising data preparation and refresh steps
  • Improve management confidence through documented KPI definitions and reconciled totals
  • Reduce spreadsheet errors caused by manual copying, inconsistent formulas and uncontrolled inputs
  • Provide decision-makers with interactive views of targets, trends, variances and exceptions
  • Create maintainable dashboard assets with named owners, controls and publication checks

Course modules

10 modules over 5 days

Participants can define a dashboard scope, decision purpose and controlled set of performance measures.

  • Dashboard decision questions translated into measures, dimensions, targets and exceptions
  • Audience, cadence and action thresholds documented in a dashboard requirements brief
  • SMART KPI definitions distinguish leading, lagging, diagnostic and outcome measures
  • Metric dictionaries specify calculation logic, owners, refresh cycles and source systems
  • Wireframes allocate executive summary, trend, variance and detail views
  • Chart selection follows comparison, composition, distribution, relationship and change tasks

Participants can create controlled worksheet structures and maintainable calculation logic for dashboard reporting.

  • Excel Tables establish structured references, calculated columns and controlled expansion
  • Named ranges support readable formulas, validation lists and chart sources
  • Data validation lists prevent invalid categories, dates and status values
  • IF, IFS and SWITCH encode business rules and exception labels
  • XLOOKUP, INDEX and MATCH reconcile codes across source tables
  • LET and LAMBDA reduce repetition in reusable calculation patterns
  • Formula auditing traces precedents, dependents, errors and unintended circular references

Participants can import, clean, combine and refresh dashboard source data using Power Query.

  • Power Query imports CSV, workbook, folder and database extracts
  • Query steps standardise headers, data types, nulls and duplicate records
  • Append queries combines monthly files with consistent column structures
  • Merge queries joins reference tables using defined key fields
  • Pivot and unpivot transformations reshape cross-tab reports into analysis-ready records
  • Parameters control file locations, reporting periods and refresh settings
  • Query diagnostics identify slow steps and avoid unnecessary source reads

Participants can build relational data models and calculate reusable dashboard measures with DAX.

  • Star schemas separate fact tables from conformed dimension tables
  • Power Pivot relationships replace fragile VLOOKUP chains between large tables
  • Granularity checks prevent duplicated totals and misleading aggregated ratios
  • Calendar tables support month-to-date, year-to-date and prior-period comparisons
  • DAX measures calculate totals, ratios and conditional business metrics
  • CALCULATE modifies filter context for controlled KPI calculations
  • Distinct counts and weighted averages address non-additive reporting measures

Participants can design clear dashboard layouts that direct attention to important performance information.

  • Dashboard hierarchy directs attention from headline performance to explanatory detail
  • Gestalt principles group related measures through proximity, alignment and enclosure
  • Pre-attentive attributes highlight exceptions using position, colour, size and contrast
  • Colour palettes reserve red, amber and green for defined status rules
  • Data-ink ratio removes decoration, gridlines and duplicated labels
  • Accessible formatting provides readable fonts, contrast and non-colour status cues

Participants can select and configure charts that communicate trends, comparisons and drivers of change.

  • Column, bar and line charts match categorical and time-series comparisons
  • Variance charts compare actual, budget, forecast and prior-period results
  • Waterfall charts explain movement between opening and closing values
  • Bullet charts show performance against target and tolerance bands
  • Scatter plots reveal relationships, clusters and unusual observations
  • Combo charts combine volume and rate measures with disciplined axes
  • Small multiples compare repeated trends without overloaded single charts

Participants can create interactive dashboard views that allow users to filter, compare and inspect data.

  • PivotTables summarise model data with consistent measures and report layouts
  • Slicers and timelines filter connected PivotTables across dashboard sections
  • PivotCharts retain interactive filtering while preserving chart design discipline
  • Dynamic arrays drive spill ranges for responsive tables and lists
  • FILTER, SORT and UNIQUE create controlled detail views
  • Form controls support scenario selectors and user-directed comparisons
  • Dynamic named ranges maintain chart sources as data expands

Participants can improve dashboard speed, protect controlled areas and document refresh dependencies.

  • Calculation modes, volatile functions and array formulas affect workbook speed
  • SUMIFS and COUNTIFS replace inefficient repeated lookup calculations
  • Helper columns simplify complex logic and improve auditability
  • Workbook size is reduced through targeted ranges and query loading choices
  • Error handling distinguishes missing data, invalid inputs and calculation failures
  • Protection settings separate editable inputs from controlled formulas and layouts
  • Refresh sequences document dependencies between queries, models and dashboard outputs

Participants can validate dashboard results and establish practical controls for publication and ownership.

  • Reconciliation checks compare dashboard totals with authorised source reports
  • Control totals test row counts, amounts and key-category completeness
  • Exception reports isolate unmatched keys, blanks and unexpected values
  • KPI sign-off records definitions, thresholds, owners and approval dates
  • Version conventions identify file owner, reporting period and release status
  • Refresh checklists confirm inputs, transformations, calculations and publication readiness
  • Workbook documentation explains sources, assumptions, limitations and change history

Participants can plan a controlled workplace dashboard build, review process and adoption approach.

  • Requirements brief prioritises decisions, users, refresh cadence and source availability
  • Dashboard backlog sequences quick wins, dependencies and deferred enhancements
  • Prototype review criteria test usefulness, clarity, accuracy and response time
  • Stakeholder feedback is converted into controlled design and metric changes
  • Adoption plan assigns training, ownership, support and review responsibilities
  • Personal build plan identifies first dashboard, data sources and milestones

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

What you take back to work

  1. Dashboard requirements brief template
  2. KPI definition and metric dictionary template
  3. Power Query transformation pattern library
  4. Power Pivot data model blueprint
  5. Interactive Excel dashboard wireframe
  6. Dashboard refresh and quality checklist

Who should attend

  • Business Intelligence Analysts — need interactive Excel reporting where enterprise BI tools are unavailable or unsuitable
  • Financial Analysts — need reliable budget, forecast, variance and management-performance dashboards
  • Management Information Analysts — need to convert recurring data extracts into controlled reporting products
  • Operations Analysts — need to monitor service levels, throughput, costs and operational exceptions
  • Data Analysts — need to prepare source data and present decision-ready analysis in Excel
  • Programme Monitoring Officers — need to track delivery indicators, targets, milestones and exceptions

Skills and tools covered

Dashboard requirements analysisPower Query transformationDAX measure designInteractive chart designExcel data modellingDashboard quality assurance

Tools and standards

Microsoft Excel Tables, Power Query, Power Pivot, DAX

How the course runs

Training methodology

The instructor combines short technical briefings with live Excel demonstrations and guided build sequences. Case work uses realistic reporting scenarios such as budget variance, service delivery, sales performance and programme monitoring. Group exercises examine dashboard requirements, chart choices, KPI definitions and data-quality issues. Simulations recreate stakeholder review and sign-off discussions, requiring participants to justify measures and design decisions. Individual application planning helps each participant identify a workplace dashboard, available source data, key users, first milestones and required controls.

Requirements and prerequisites

Participants should already use Excel regularly and be comfortable creating formulas, sorting and filtering data, working with worksheets, and building basic charts and PivotTables. Experience with functions such as SUMIFS, IF and XLOOKUP or VLOOKUP is helpful. The course assumes confidence working with tabular business data and interpreting measures such as actuals, targets and variances. Prior knowledge of Power Query, Power Pivot, DAX, VBA, SQL or formal data-modelling methods is not required. Participants should have access to a current desktop version of Microsoft Excel.

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 be confident with everyday Excel formulas, tables, charts and basic PivotTables. Power Query, Power Pivot and DAX are taught from the ground up, but this is not a beginner Excel course.

Bring a laptop with a current desktop version of Microsoft Excel if your delivery arrangements require personal devices. You may bring a non-sensitive reporting example, subject to your employer's data-security rules.

It is designed for analysts, finance professionals, management information staff, operations teams and reporting specialists who build recurring Excel reports. It also suits professionals responsible for converting data extracts into management dashboards.

The course focuses on building governed, interactive dashboards in Excel, rather than general spreadsheet skills or enterprise Power BI deployment. It includes formulas, Power Query, Power Pivot and DAX only where they support dashboard design and maintenance.

Participants leave with a dashboard requirements brief, model design, wireframe and quality checklist that can be adapted to a real reporting need. The final module identifies the first dashboard, source data, stakeholders and build milestones.

Training runs from 08:30 to 16:30 each day, providing about 6.5 contact hours daily. The five days progress from requirements and data preparation through modelling, design, interactivity, quality assurance and workplace 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

Related courses in Business Intelligence