Data Analytics Using 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-099

Before you join: Participants should be comfortable entering formulas, sorting and filtering lists, formatting worksheets, and working with files in Microsoft 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 remains a primary analysis environment for finance, operations, HR, procurement and programme teams, yet many reports are assembled through fragile copy-and-paste steps, inconsistent definitions and formulas that cannot be checked. The result is slow reporting, disputed numbers and decisions based on outdated or poorly classified data. This course addresses the practical route from raw operational extracts to a controlled analysis, forecast and management-ready dashboard.

Participants learn to structure analytical workbooks, assess source-data quality, clean inconsistent fields and create repeatable calculations. The course covers Excel Tables, advanced formulas, PivotTables, descriptive statistics, Power Query, Power Pivot Data Models, DAX measures, dashboards, forecasting and scenario analysis. Participants will be able to convert transaction-level data into meaningful performance measures, identify exceptions and trends, test assumptions, and present findings with appropriate caveats.

Instruction combines short technical briefings with realistic data cases drawn from sales, procurement, service delivery, budgeting and workforce reporting. Participants build and review analytical outputs individually and in groups, diagnose formula and data-quality issues, and apply a structured peer-review checklist. They leave with reusable workbook structures, data-cleaning rules, dashboard designs, forecasting models and an application plan for a workplace reporting need.

The course is suited to professionals who already use Excel regularly and need stronger analytical methods than basic spreadsheet reporting. It is particularly relevant where teams receive recurring CSV exports, system extracts or survey data and must turn them into dependable evidence for managers, clients, regulators or programme sponsors.

What you will be able to do

  • Structure analytical Excel workbooks using controlled data, calculation and output layers
  • Clean and standardise raw data using Excel functions and documented quality rules
  • Build reusable formulas with lookup, conditional aggregation and dynamic array functions
  • Analyse operational data using descriptive statistics, PivotTables and variance measures
  • Transform recurring source files using Power Query queries and refresh procedures
  • Model related datasets using Power Pivot relationships and DAX measures
  • Design decision-focused dashboards using suitable charts, filters and conditional formatting
  • Communicate analytical findings through reconciled reports, forecasts and decision narratives

Benefits of attending

For you

  • Produce repeatable Excel analyses instead of manually assembled monthly reports
  • Diagnose data-quality and formula issues before reports reach decision-makers
  • Build interactive PivotTable and dashboard outputs for stakeholder questions
  • Create refreshable Power Query workflows for recurring data extracts
  • Present forecasts and analytical findings with stated assumptions and limitations

For your organisation

  • Reduce manual report preparation through reusable queries, models and templates
  • Improve confidence in management information through documented checks and reconciliations
  • Shorten turnaround time for recurring sales, operations and finance reporting
  • Create more consistent metric definitions across teams and reporting packs
  • Support better operational decisions with visible trends, exceptions and forecast scenarios

Course modules

10 modules over 5 days

Participants can structure an Excel analysis workbook and assess whether source data is fit for purpose.

  • Analytical questions translated into measurable business metrics and decision criteria
  • Workbook architecture separating raw data, calculations, outputs and documentation
  • Excel Tables configured with structured references, names and refreshable ranges
  • Data types, units, grain and time periods checked before analysis
  • Source-to-report lineage documented using field definitions and transformation notes
  • File naming, version control and access conventions for shared workbooks

Participants can clean common data defects and document defensible data-quality decisions.

  • Text, dates and numeric values standardised with Excel conversion functions
  • Duplicates identified using composite keys, conditional formatting and Remove Duplicates
  • Missing values profiled and treated using documented business rules
  • TRIM, CLEAN, SUBSTITUTE and TEXTSPLIT applied to inconsistent source fields
  • Outliers flagged through distribution checks, IQR thresholds and domain limits
  • Data-quality log records exceptions, ownership, corrections and residual risks

Participants can build transparent, reusable calculation models using advanced Excel formulas.

  • Relative, absolute and mixed references controlled in reusable calculation models
  • IF, IFS and SWITCH encode transparent classification and exception rules
  • SUMIFS, COUNTIFS and AVERAGEIFS calculate segmented operational performance measures
  • XLOOKUP and INDEX-MATCH reconcile records across controlled lookup tables
  • Dynamic arrays with FILTER, SORT, UNIQUE and SEQUENCE produce live outputs
  • IFERROR and validation checks prevent misleading blanks and calculation failures

Participants can use descriptive statistics and analytical tests to interpret operational data appropriately.

  • Date functions build calendar, fiscal-period, ageing and cohort classifications
  • Descriptive statistics summarise central tendency, spread, skewness and sample size
  • Percentiles and quartiles identify service levels, thresholds and unusual observations
  • Correlation coefficients interpreted without confusing association with causal evidence
  • Sampling bias, survivorship bias and denominator errors tested before conclusions
  • Confidence intervals and hypothesis tests framed for operational decision questions

Participants can analyse large transaction datasets using PivotTables, filters and calculated summaries.

  • PivotTables aggregate transactions by product, region, customer and reporting period
  • Calculated fields, grouping and Show Values As reveal performance drivers
  • Slicers, timelines and report filters support controlled stakeholder interrogation
  • PivotCharts compare trends, rankings, composition and variance patterns clearly
  • GETPIVOTDATA retrieves governed summary values for management reporting packs
  • Refresh settings and source changes tested before distributing pivot outputs

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

  • Power Query imports CSV, workbook, folder and database extracts
  • Applied Steps document repeatable filtering, typing, splitting and merging transformations
  • Merge Queries joins reference data using validated business keys
  • Append Queries combines monthly files into controlled transaction tables
  • Query parameters and refresh paths support portable reporting workflows
  • Load destinations selected for Tables, Data Model or connection-only queries

Participants can model related datasets and create business measures using Power Pivot and DAX.

  • Power Pivot Data Model relates fact tables to shared dimensions
  • Star schema separates transactions, dates, products, customers and organisational attributes
  • Relationship cardinality and filter direction checked to avoid double counting
  • DAX measures calculate revenue, margin, growth and distinct customer counts
  • CALCULATE and filter context produce period and segment-specific measures
  • Calendar tables support year-to-date, prior-period and rolling-period comparisons

Participants can design dashboards that communicate priorities, exceptions and performance drivers clearly.

  • Chart selection matches comparison, trend, distribution, relationship and composition questions
  • Dashboard wireframes prioritise decisions, metrics, thresholds, filters and action owners
  • Conditional formatting exposes exceptions, variance bands and operational thresholds
  • Combination charts and secondary axes used only with defensible scales
  • Titles, labels and annotations state the finding rather than decoration
  • Accessibility checks address colour contrast, number formats and readable layouts

Participants can build short-term forecasts and test how assumptions affect projected results.

  • Time-series plots distinguish trend, seasonality, cyclicality and irregular variation
  • Moving averages and exponential smoothing produce transparent short-term forecasts
  • FORECAST.ETS generates seasonal projections with confidence intervals and defined assumptions
  • Actual-versus-forecast variance analysis separates volume, price and mix effects
  • Scenario Manager and data tables test assumptions and sensitivity ranges
  • Forecast accuracy tracked using MAE, MAPE and bias measures

Participants can quality-check Excel analyses and present findings for management action.

  • Reconciliation checks tie dashboard totals to source-system control totals
  • Peer review checklist tests logic, refreshability, traceability and presentation quality
  • Data privacy and access controls applied to sensitive employee and customer data
  • Executive narrative distinguishes facts, interpretations, assumptions and recommended actions
  • Analytical findings presented with caveats, materiality and decision implications
  • Personal application plan defines workplace data set, stakeholders and delivery milestones

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

What you take back to work

  1. Analytical workbook architecture template
  2. Data-quality assessment checklist
  3. Advanced formula reference model
  4. Power Query transformation workflow
  5. Management dashboard wireframe
  6. Forecast and scenario analysis template

Who should attend

  • Business Analyst — needs repeatable methods for turning extracts into decision evidence
  • Financial Analyst — needs controlled models, variance analysis and forecast reporting
  • Operations Manager — needs service, productivity and exception reporting from transaction data
  • Data Analyst — needs stronger Excel methods for business-facing analysis and reporting
  • Procurement Specialist — needs supplier, spend and contract performance analysis
  • HR Analyst — needs reliable workforce metrics, trends and management dashboards

Skills and tools covered

Excel data cleaningAdvanced Excel formulasPivotTable analysisPower Query transformationDAX data modellingDashboard reporting

Tools and standards

Microsoft Excel, Power Query, Power Pivot, DAX

How the course runs

Training methodology

The course uses short instructor briefings followed by guided analysis of realistic business datasets. Participants work with transaction extracts, reference tables, recurring monthly files and management-reporting requirements. Individual tasks build technical fluency in formulas, PivotTables, Power Query and dashboards, while group reviews test assumptions, chart choices and data-quality decisions. Trainers use demonstrations and troubleshooting discussions to explain common spreadsheet failures. The final sessions focus on presenting findings, peer review and planning a workplace application.

Requirements and prerequisites

Participants should be comfortable entering formulas, sorting and filtering lists, formatting worksheets, and working with files in Microsoft Excel. They should have experience using Excel for routine reporting, operational tracking, budgeting or similar business tasks. Familiarity with basic functions such as SUM, IF and VLOOKUP is helpful, although alternatives are introduced during the course. No programming, SQL, statistics qualification, prior Power Query experience or prior Power Pivot experience is required. Participants should be willing to work with structured datasets and numerical business measures.

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, sorting, filtering and routine spreadsheet reporting. The course introduces advanced functions, Power Query, Power Pivot and DAX from the ground up, so prior experience with these features is not required.

Participants should bring a laptop with a current desktop version of Microsoft Excel, ideally Microsoft 365 or Excel 2021 or later. Course datasets and materials are provided, and participants may also bring non-sensitive examples of workplace reporting requirements.

The course is designed for analysts and business professionals who regularly receive operational, financial, customer, workforce or programme data. It is suitable for people who need to improve recurring reporting and analysis rather than learn basic spreadsheet use.

Basic Excel courses focus on worksheet use, introductory formulas and formatting, while this course concentrates on analytical methods, data preparation, modelling and reporting controls. Power BI courses focus on a separate reporting platform; this course develops advanced analysis within Excel, including Power Query and Power Pivot.

Participants can apply the methods to recurring monthly reports, budget monitoring, supplier analysis, workforce metrics, sales performance and service-delivery data. The course includes reusable structures for cleaning data, documenting logic, building dashboards and checking outputs before distribution.

Each day runs from 08:30 to 16:30 and provides approximately 6.5 contact hours, for about 32.5 contact hours across five days. Time is allocated to workbook design, cleaning, formulas, PivotTables, Power Query, data modelling, dashboards, forecasting and quality assurance.

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 Analytics Using Excel and Power BI Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Data Analytics for Business Analysts Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Analytics for Business Decision Making Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Analytics for Managers Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Analytics with Pandas Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Cleaning and Validation Using SPSS Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Governance Frameworks 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