Excel Data Management and Analysis Training Course

Duration 5 days 10 modules · 33 contact hours
Level Intermediate Certificate on completion
Who attends Data Analysts, Finance 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-DA-068

Before you join: Participants should be comfortable entering data, formatting worksheets, using basic formulas such as SUM and IF, and working with multiple worksheets in Excel.

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 is often the final destination for data from finance systems, CRM platforms, procurement records, HR files and operational trackers. Yet many reports are slowed by inconsistent formats, duplicate records, unreliable lookups, formulas that cannot be checked and manual monthly consolidation. These weaknesses create reporting delays, misleading totals and avoidable rework. This course addresses the practical discipline of preparing, controlling, analysing and presenting business data in Excel.

Participants learn to structure source data, apply data quality rules, build controlled calculations and combine related datasets without corrupting totals. They use Excel Tables, advanced formulas, lookup methods, PivotTables, PivotCharts, Power Query and Power Pivot data models. The course also covers data validation, reconciliation, exception reporting, report design, documentation and workbook controls. Participants gain methods for turning recurring exports into repeatable analysis and management reporting.

This is a five-day instructor-led course delivered in a classroom in 50+ cities or live online. Each training day runs from 08:30 to 16:30, providing approximately 6.5 contact hours. Short instructor briefings are followed by guided case work, group analysis and individual workbook development using realistic business datasets. Participants leave with reusable templates, quality-control checklists, reporting models and an application plan for improving a recurring Excel report. A certificate of completion is issued to participants who complete the course.

The course suits professionals who already use Excel and need stronger control over data preparation, analysis and reporting. It is particularly relevant for analysts, finance staff, reporting specialists, operations teams and managers responsible for evidence-based decisions.

What you will be able to do

  • Structure business datasets using defined grain, fields, measures and dimensions
  • Clean imported data using Excel functions and Power Query transformation steps
  • Apply data validation, reconciliation and exception checks to improve data quality
  • Build auditable calculations using conditional, date and dynamic-array formulas
  • Join related datasets using XLOOKUP, INDEX-MATCH and composite keys
  • Analyse operational and financial data using PivotTables, PivotCharts and slicers
  • Create repeatable refresh processes using Power Query and Power Pivot data models
  • Produce controlled Excel reports with documentation, ownership and review criteria

Benefits of attending

For you

  • Reduced time spent cleaning and consolidating recurring Excel exports
  • Stronger ability to identify unreliable records before reporting decisions
  • Greater confidence building formulas that colleagues can inspect and maintain
  • Reusable Power Query methods for monthly and weekly data refreshes
  • Clearer evidence of analytical capability for reporting and analyst roles

For your organisation

  • Fewer reporting errors caused by duplicate records, invalid entries and broken formulas
  • Shorter reporting cycles through repeatable imports, transformations and refresh procedures
  • More consistent KPI calculations across finance, operations and management reports
  • Improved auditability through documented sources, controls, assumptions and ownership
  • Better management decisions from clearer exception analysis and trend reporting

Course modules

10 modules over 5 days

Participants can define the structure, scope and control requirements for an Excel analysis.

  • Analytical questions translated into fields, measures, dimensions and decision criteria
  • Grain defined before combining transaction, customer, product and calendar records
  • Data types assigned for dates, identifiers, quantities, currencies and percentages
  • Source-to-report data flow mapped from extract through controlled output
  • Excel workbook architecture separates raw data, calculations, outputs and assumptions
  • Common spreadsheet failure modes identified: duplicate rows, hard-codes and broken links

Participants can import and standardise data from common business file formats.

  • CSV, text and workbook imports configured with correct delimiters and encodings
  • Power Query import settings preserve headers, types and source locations
  • Whitespace, nonprinting characters and inconsistent case removed using CLEAN, TRIM and UPPER
  • Dates converted safely from text, serial numbers and regional formats
  • Missing values classified as unknown, not applicable, zero or data error
  • Duplicate records detected using composite keys and conditional formatting rules
  • Transformation steps documented for repeatable monthly data refreshes

Participants can apply controls that identify invalid, incomplete and inconsistent data.

  • Data validation lists restrict category entries to approved reference values
  • Custom validation formulas control date ranges, thresholds and identifier formats
  • Conditional formatting exposes blanks, outliers, duplicates and exception conditions
  • Reconciliation checks compare record counts, totals and control sums
  • Error values handled with IFERROR, ISERROR and explicit exception flags
  • Audit columns record source, refresh date, owner and transformation status
  • Quality rules prioritised by materiality, frequency and downstream reporting impact

Participants can build transparent Excel calculations for operational and management reporting.

  • Structured references calculate row-level metrics within Excel Tables
  • SUMIFS, COUNTIFS and AVERAGEIFS aggregate data against multiple criteria
  • IF, IFS and SWITCH encode transparent business rules and classifications
  • Date functions calculate ageing, reporting periods, working days and deadlines
  • Dynamic arrays use FILTER, UNIQUE, SORT and SEQUENCE for live outputs
  • Formula auditing traces precedents, dependents and inconsistent calculation patterns
  • Named ranges clarify assumptions and reduce formula maintenance risk

Participants can combine related Excel datasets while checking for missing and duplicated matches.

  • XLOOKUP retrieves matched attributes with exact-match and missing-value handling
  • INDEX-MATCH supports flexible lookup directions and controlled match behaviour
  • Composite keys join records where no single unique identifier exists
  • One-to-many relationships identified before aggregating customer, invoice and payment data
  • Reconciliation totals detect unmatched records, duplicate keys and join inflation
  • Reference tables maintained for codes, mappings, rates and approved classifications

Participants can organise datasets and create controlled views for review and analysis.

  • Excel Tables provide structured references, calculated columns and expandable ranges
  • Multi-level sorting orders records by business priority and exception status
  • AutoFilter isolates subsets using text, number, date and colour criteria
  • Advanced Filter extracts unique lists and criteria-based record subsets
  • SUBTOTAL and AGGREGATE return visible-row calculations after filtering
  • Slicers control filtered views for managers reviewing recurring reports
  • Sheet protection separates permitted inputs from formulas and controlled outputs

Participants can summarise, interrogate and present business data through PivotTables and charts.

  • PivotTables summarise transactions by period, category, geography and owner
  • Value field settings apply sums, counts, averages and percentage calculations
  • Calculated fields and show-values-as reveal variance, rank and contribution
  • PivotCharts present comparisons, trends and composition without misleading scales
  • Slicers and timelines support controlled exploration of approved reporting dimensions
  • Chart selection matches analytical questions: comparison, trend, distribution or relationship
  • Dashboard layouts distinguish headline measures, drivers, exceptions and supporting detail

Participants can create refreshable Power Query processes for recurring data preparation.

  • Power Query connects repeatable imports to folders, workbooks, CSV files and databases
  • Applied Steps document filtering, splitting, replacing and type-conversion transformations
  • Merge Queries combines related tables using defined keys and join types
  • Append Queries consolidates monthly files with consistent column structures
  • Group By creates controlled aggregations before loading analysis-ready datasets
  • Query parameters separate source locations from transformation logic and refresh settings
  • Refresh procedures test row counts, errors and changed source structures

Participants can build Excel data models that support consistent multi-table analysis.

  • Power Pivot data models connect fact tables with shared dimension tables
  • Star schema design separates transactions from customers, products and calendar attributes
  • Relationships use unique dimension keys and controlled filter directions
  • DAX measures calculate totals, ratios and period comparisons at query time
  • CALCULATE modifies filter context for selected categories, periods and business rules
  • Calendar tables support month-to-date, year-to-date and prior-period analysis
  • Model checks identify ambiguous relationships, missing keys and misleading totals

Participants can document, control and plan improvements to a recurring Excel report.

  • Data dictionaries define fields, business meanings, formats, owners and permitted values
  • Workbook controls assign refresh responsibilities, review dates and version conventions
  • Exception logs record data defects, decisions, corrections and unresolved risks
  • Report acceptance criteria specify purpose, audience, measures, filters and timing
  • Scenario analysis tests assumptions using data tables, Goal Seek and Solver
  • Application plans select one recurring report for redesign after the course
  • Peer review applies a checklist to formulas, sources, controls and presentation

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

What you take back to work

  1. Excel data-quality rules checklist
  2. Source-to-report data flow template
  3. Formula auditing and reconciliation checklist
  4. Power Query transformation pattern library
  5. PivotTable management reporting model
  6. Recurring report improvement action plan

Who should attend

  • Data Analysts — need repeatable methods for preparing and analysing business extracts
  • Finance Analysts — need controlled calculations, reconciliations and period reporting
  • Business Intelligence Analysts — need Excel-based preparation before formal BI publication
  • Operations Managers — need reliable performance reports from operational source data
  • Reporting Specialists — need faster refresh processes and clearer management outputs
  • Project and Programme Officers — need accurate trackers, exception reports and evidence summaries

Skills and tools covered

Data quality controlExcel formula designDataset reconciliationPower Query transformationPivotTable analysisManagement reporting

Tools and standards

Microsoft Excel Tables, Power Query, PivotTables, Power Pivot

How the course runs

Training methodology

The course combines short technical briefings with guided case work using finance, customer, procurement and operational datasets. Participants inspect flawed source files, define data-quality rules, build calculations and test reporting outputs in Excel. Group exercises focus on reconciling totals, selecting suitable analysis methods and reviewing workbook controls. Trainers demonstrate methods before participants apply them to structured tasks. The final sessions use application planning to identify one recurring report, its source data, control points, refresh process and improvement priorities.

Requirements and prerequisites

Participants should be comfortable entering data, formatting worksheets, using basic formulas such as SUM and IF, and working with multiple worksheets in Excel. Experience with business reports, operational data, financial data or recurring exports is useful. Participants should understand rows, columns, filters and basic charts. Prior knowledge of Power Query, PivotTables, Power Pivot, DAX, databases, SQL, programming or statistics is not required. The course is designed for users who need to move beyond basic spreadsheet use into controlled data preparation and analysis.

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 with basic formulas, worksheet navigation, sorting and filtering. Prior use of PivotTables or Power Query is helpful but not required.

Participants need a laptop with a recent desktop version of Microsoft Excel, ideally Microsoft 365 or Excel 2021 or later. Course files are provided, and participants may bring non-confidential examples of recurring reporting challenges.

The course is designed for analysts, finance professionals, reporting specialists, operations staff and managers who work with recurring business data. It is not intended for complete Excel beginners or specialist data engineers.

General Excel courses usually focus on spreadsheet features, while this course focuses on data control, preparation, reconciliation and analytical reporting. Power BI courses focus on a separate reporting platform; this course develops disciplined analysis within Excel.

Participants can use the templates and methods to improve a monthly report, consolidate recurring files, check source-data quality or rebuild an unreliable workbook. The application plan identifies a specific report, its controls and its refresh priorities.

The course provides approximately 32.5 contact hours across five days, from 08:30 to 16:30 each day. Time is divided between instructor demonstrations, guided analysis, case work, peer review 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

Related courses in Data Analytics