Data Management and Visualisation with Excel Training Course

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

Before you join: Participants should be comfortable entering and editing data in Excel, using basic formulas such as SUM and IF, sorting and filtering lists, and creating simple 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

Many reporting teams spend too much time correcting spreadsheets, reconciling extracts, rebuilding monthly PivotTables, and explaining charts that do not answer management questions. Poorly structured workbooks create duplicate records, broken formulas, inconsistent metrics, and reports that cannot be refreshed reliably. Excel remains central to finance, operations, programme management, HR, procurement, and commercial reporting, but useful analysis depends on disciplined data preparation and clear visual communication.

This course develops the intermediate-to-advanced Excel methods needed to build controlled data models and decision-ready reports. Participants structure source data with Excel Tables, clean and combine extracts with Power Query, apply lookup and aggregation formulas, create Data Model relationships, and use PivotTables for repeatable analysis. They also select appropriate charts, define KPIs, create interactive dashboards, and apply checks for accuracy, refreshability, accessibility, and handover. The course addresses common reporting problems including mismatched records, inconsistent date formats, duplicate keys, untraceable formulas, misleading chart scales, and unreliable external links.

Instruction combines short technical briefings with guided case work based on realistic reporting scenarios, such as budget monitoring, supplier performance, service delivery, sales pipeline, and workforce reporting. Participants work through the full reporting chain: source data, transformation, analysis, visualisation, quality assurance, and publication planning. They leave with reusable templates, a dashboard wireframe, data-quality controls, and an application plan for improving a workplace report. Delivery is instructor-led over five days, available in classrooms in more than 50 cities or live online. Participants who complete the course receive a certificate of completion.

What you will be able to do

  • Structure Excel workbooks using tables, named ranges, calculation layers, and documented source boundaries
  • Clean, validate, standardise, and reconcile source data using Excel formulas and control checks
  • Build repeatable Power Query transformations for imported files, folders, workbooks, and tables
  • Create relational Data Models using keys, relationships, calendar tables, and DAX measures
  • Analyse business data with PivotTables, slicers, calculated measures, ranking, and variance calculations
  • Design charts and dashboards that present KPIs, trends, exceptions, and comparisons accurately
  • Apply workbook governance controls for refreshes, formula checks, documentation, and protected inputs
  • Produce a practical redesign plan for a recurring workplace report or dashboard

Benefits of attending

For you

  • Reduced time spent cleaning and rebuilding recurring reports
  • Greater confidence diagnosing formula, data, and refresh errors
  • Stronger dashboard design portfolio for analyst and reporting roles
  • Reusable methods for combining files and standardising extracts
  • Clearer communication of findings to managers and decision-makers

For your organisation

  • More reliable recurring reports through documented transformations and control checks
  • Reduced manual consolidation effort across monthly and weekly reporting cycles
  • Consistent KPI calculations across finance, operations, programme, and commercial teams
  • Faster identification of exceptions through focused PivotTables and dashboards
  • Improved report handover through source documentation, refresh plans, and protected inputs

Course modules

10 modules over 5 days

Participants can structure an Excel workbook with clear data, model, control, and presentation layers.

  • Workbook architecture: sheets, tables, named ranges, and calculation dependencies
  • Data types, locale settings, dates, and text-to-number conversion rules
  • Excel Tables structured references for auditable, expandable source datasets
  • Import boundaries between raw data, transformations, models, and presentation sheets
  • File naming, version control, and folder structures for shared reporting
  • Calculation modes, circular references, and formula error tracing techniques

Participants can prepare consistent, validated datasets and document exceptions requiring correction.

  • Data Validation lists, custom rules, input messages, and error alerts
  • Duplicate detection using conditional formatting, COUNTIFS, and unique record checks
  • TRIM, CLEAN, SUBSTITUTE, TEXTSPLIT, and TEXTAFTER for text standardisation
  • Dates normalised with DATEVALUE, EOMONTH, WORKDAY, and calendar logic
  • Missing-value rules, outlier flags, and exception logs for remediation
  • Reconciliation checks comparing row counts, totals, keys, and control totals

Participants can build transparent formulas for lookups, conditional calculations, and dynamic report outputs.

  • XLOOKUP, XMATCH, INDEX-MATCH, and exact-match lookup selection criteria
  • SUMIFS, COUNTIFS, AVERAGEIFS, and criteria ranges for controlled aggregation
  • IF, IFS, SWITCH, and error handling with IFERROR and IFNA
  • Dynamic arrays using FILTER, SORT, UNIQUE, SEQUENCE, and spill references
  • LET formulas for readable calculations and reduced repeated expression logic
  • Formula auditing through precedents, dependents, Evaluate Formula, and Watch Window

Participants can import, clean, combine, and refresh data through documented Power Query steps.

  • Power Query interface, query steps, data types, and load destinations
  • CSV, folder, workbook, and web-table imports using query connectors
  • Applied Steps documented for repeatable transformations and refresh control
  • Merge queries, append queries, and join types for relational datasets
  • Unpivoting cross-tab reports into analysis-ready transactional table structures
  • Query parameters and refresh settings for governed recurring data updates

Participants can create a simple relational Data Model and define measures for consistent analysis.

  • Data Model relationships, primary keys, foreign keys, and cardinality checks
  • Star schema design separating fact tables from descriptive dimension tables
  • Power Pivot measures, calculated columns, and aggregation choices
  • DAX measure syntax using SUM, CALCULATE, DISTINCTCOUNT, and DIVIDE
  • Calendar tables for month-to-date, year-to-date, and period comparisons
  • Relationship troubleshooting for duplicate keys, unmatched records, and ambiguous paths

Participants can build refreshable PivotTable reports that reveal trends, variances, and exceptions.

  • PivotTable field placement for meaningful row, column, filter, and value analysis
  • Value Field Settings, number formats, and custom calculations
  • Grouping dates, bins, and categories without altering source records
  • Slicers, timelines, report connections, and controlled interactive filtering
  • Show Values As calculations for variance, ranking, share, and running totals
  • PivotTable refresh, source changes, and layout preservation controls

Participants can select and format charts that communicate business findings without distortion.

  • Chart selection matched to comparison, composition, distribution, and trend questions
  • Column, bar, line, scatter, waterfall, histogram, and box plot uses
  • Chart data ranges, dynamic titles, labels, and number formatting
  • Dual-axis risks, truncated axes, and misleading visual encoding avoided
  • Reference lines, target bands, and variance annotations for decision support
  • Accessible colour palettes, data labels, and chart readability checks

Participants can design interactive Excel dashboards around defined decisions, KPIs, and user needs.

  • Dashboard requirements translated into audience, decisions, metrics, and refresh needs
  • KPI definitions with numerator, denominator, timing, owner, and source
  • Wireframes arranging scorecards, trends, exceptions, and supporting detail
  • Interactive dashboard controls using slicers, drop-downs, and linked charts
  • Conditional formatting for thresholds, data bars, icon sets, and exceptions
  • Executive dashboard layout prioritising action signals over decorative visuals

Participants can apply practical controls that improve workbook reliability, security, and maintainability.

  • Workbook protection, locked cells, and permitted input ranges
  • External links, connection properties, and refresh failure diagnostics
  • Error checks for formulas, queries, relationships, PivotTables, and charts
  • Documentation sheets recording sources, assumptions, refresh steps, and owners
  • Quality assurance checklist for accuracy, readability, reproducibility, and handover
  • Accessibility checks covering colour contrast, labels, and screen-reader context

Participants can plan a controlled reporting workflow and identify improvements for a workplace deliverable.

  • Business questions converted into data requirements, metrics, and visual outputs
  • Requirements log distinguishing source limitations, assumptions, and calculation rules
  • End-to-end workflow from import through transformation, analysis, and reporting
  • Peer review criteria for formula logic, controls, and visual interpretation
  • Refresh and publication plan assigning owners, frequencies, and approval points
  • Personal application plan identifying a workplace report for redesign

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

What you take back to work

  1. Excel workbook architecture template
  2. Data-quality and reconciliation checklist
  3. Power Query transformation specification
  4. KPI definition and dashboard wireframe
  5. Workbook governance and refresh plan
  6. Workplace report redesign action plan

Who should attend

  • Business Analyst — needs repeatable Excel models and management reporting outputs
  • Financial Analyst — prepares budgets, forecasts, variance reports, and recurring performance packs
  • Operations Manager — monitors service levels, workload, suppliers, and operational exceptions
  • Project Manager — consolidates delivery data, milestones, risks, and resource reporting
  • Monitoring and Evaluation Officer — cleans programme data and presents indicator performance
  • HR or Workforce Analyst — reports workforce metrics, staffing trends, and people data

Skills and tools covered

Excel data modellingPower Query transformationFormula auditingPivotTable analysisDashboard designData quality control

Tools and standards

Microsoft Excel Tables, Power Query, Power Pivot, DAX

How the course runs

Training methodology

The course uses short instructor briefings followed by guided case work, group discussion, and individual spreadsheet building. Participants work with realistic data extracts containing formatting errors, duplicates, missing values, inconsistent codes, and reporting requirements. Demonstrations show each method in Excel before participants apply it to structured reporting tasks. Group reviews examine chart choices, KPI definitions, formula logic, and data-quality controls. The final sessions support application planning for a report, dashboard, or recurring workbook participants want to improve at work.

Requirements and prerequisites

Participants should be comfortable entering and editing data in Excel, using basic formulas such as SUM and IF, sorting and filtering lists, and creating simple charts. Experience with routine reporting, data extracts, or spreadsheet-based analysis is useful. Participants should have access to a recent desktop version of Microsoft Excel, ideally Microsoft 365 or Excel 2021 or later. Prior experience with Power Query, Power Pivot, DAX, database design, programming, SQL, or statistical software is not required. This course does not assume specialist data engineering knowledge.

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 basic worksheets, formulas, sorting, filtering, and simple charts. Power Query, PivotTables, Power Pivot, DAX, SQL, and programming experience are not required.

Bring a laptop with a recent desktop version of Microsoft Excel, ideally Microsoft 365 or Excel 2021 or later. You may also bring a non-confidential example of a report or dashboard you want to improve.

It is suited to analysts, managers, reporting staff, finance professionals, project teams, and monitoring specialists who prepare or review Excel-based reports. It is particularly useful for people responsible for recurring reporting cycles.

The course assumes basic Excel familiarity and concentrates on data preparation, repeatable analysis, data models, PivotTables, dashboards, and controls. It remains focused on Excel rather than Power BI, SQL development, or enterprise data engineering.

Participants can use the templates and application plan to improve a recurring spreadsheet, reporting pack, tracker, or dashboard. Methods are applicable to financial reporting, operational monitoring, programme data, procurement, HR, and sales reporting.

Each training day runs from 08:30 to 16:30 and provides approximately 6.5 contact hours. Time is divided between instructor demonstrations, guided spreadsheet work, case discussions, group review, 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

More courses in Data Analytics

Data Analytics Data Governance Frameworks Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Governance Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Governance and Compliance Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Literacy Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Data Storytelling and Visualization Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Data Strategy Development Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Data Visualisation Using Python Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Visualisation and Reporting Training Course 10 days · from USD 2,200 · next 16 Nov