Advanced Excel Formulas and Functions Training Course

Duration 5 days 10 modules · 33 contact hours
Level Advanced Certificate on completion
Who attends Financial Analysts, Business Analysts and others
Fee from USD 1,100 KSh 95,000 for Kenyan organisations
Where 52 cities + online See dates by city
Next start 7 Nov 2026 Course code SD-DA-054

Before you join: Participants should be confident using Excel worksheets, entering formulas, applying basic cell references, sorting and filtering data, and using functions such as SUM, IF and COUNTIF.

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

Operational reports, financial models and management packs often fail for avoidable reasons: formulas are copied with incorrect references, lookup keys are inconsistent, source values are stored as text, and error handling conceals missing records. Advanced Excel users need more than individual functions. They need a disciplined way to structure workbooks, select formulas, test assumptions and produce calculations that colleagues can inspect and maintain.

This five-day course develops that discipline through advanced Excel formulas and functions used in reporting, reconciliation, forecasting and data preparation. Participants work with Excel Tables, defined names, logical tests, error handling, XLOOKUP, INDEX and MATCH, dynamic arrays, conditional aggregation, text parsing, date calculations, statistical analysis and financial functions. They also use LET, LAMBDA and modern array functions to reduce repeated logic and create reusable calculations.

Instruction combines focused demonstrations with realistic finance, HR, operations and service-reporting cases. Participants analyse formula requirements, build calculations, identify errors, compare alternative approaches and review workbook designs with peers. They leave with a formula design pack containing reusable patterns, audit checks, lookup methods and a personal workbook improvement plan.

The course suits professionals who already use Excel regularly and need to build or maintain complex workbooks with greater accuracy, speed and control. It is particularly relevant where spreadsheet outputs inform budgets, performance reports, regulatory submissions, resource plans or operational decisions.

What you will be able to do

  • Design structured workbooks using Excel Tables, defined names and controlled references
  • Construct advanced logical formulas with explicit error and exception handling
  • Build reliable single- and multiple-criteria lookups using XLOOKUP, INDEX and MATCH
  • Create dynamic array formulas for filtering, sorting, extracting and reshaping data
  • Apply conditional aggregation and date logic to reporting and forecasting calculations
  • Clean imported text, dates and numeric values using conversion and parsing functions
  • Develop reusable calculations with LET, LAMBDA and modern array functions
  • Audit formula logic, calculation performance and workbook controls before release

Benefits of attending

For you

  • Build complex formulas with clearer logic and fewer copy-and-paste errors
  • Replace repetitive manual reporting steps with dynamic array calculations
  • Diagnose broken lookups, circular references and hidden calculation errors
  • Create reusable named formulas for recurring reporting requirements
  • Review colleagues' workbooks using a practical formula audit process

For your organisation

  • Reduce reporting errors caused by inconsistent formulas and uncontrolled references
  • Shorten recurring analysis and reconciliation cycles through reusable calculations
  • Improve traceability of assumptions, exceptions and calculation logic
  • Increase consistency between departmental workbooks and reporting outputs
  • Reduce dependence on individual spreadsheet authors for critical models

Course modules

10 modules over 5 days

Participants can structure calculation workbooks so formulas remain readable, stable and reviewable.

  • Structured Excel Tables with stable column names and calculated columns
  • Workbook architecture separating inputs, calculations, outputs, controls and assumptions
  • Defined names using workbook scope, sheet scope, and meaningful naming rules
  • Relative, absolute and mixed references controlled during formula copying
  • Formula dependency chains inspected with precedents, dependents and trace arrows
  • Calculation modes, iterative settings and volatile functions assessed for risk
  • Data types standardised before calculations: numbers, dates, text, blanks and errors

Participants can create readable decision logic and controlled responses to formula exceptions.

  • IF, IFS and SWITCH formulas structured for readable decision logic
  • AND, OR and NOT combined with business-rule tests
  • IFERROR, IFNA and ISERROR selected for controlled exception handling
  • Blank, zero and missing-value rules encoded without masking source defects
  • LET variables used to shorten repeated calculations and expose assumptions
  • Boolean arithmetic converts multiple tests into compact inclusion and exclusion rules
  • Data validation lists linked to formula-driven control cells

Participants can retrieve accurate values from complex tables while handling duplicates and missing keys.

  • XLOOKUP returns exact matches, defaults, wildcard matches and approximate bands
  • XMATCH identifies positions for flexible retrieval and ranking formulas
  • INDEX and MATCH replace brittle VLOOKUP and HLOOKUP constructions
  • Two-way lookups combine row and column matches within matrix reports
  • Multiple-criteria retrieval uses concatenated keys and Boolean array matching
  • Duplicate keys, unmatched records and lookup direction handled explicitly
  • Reference functions OFFSET and INDIRECT evaluated for flexibility and calculation cost

Participants can use dynamic arrays to generate automatically updating analysis and report outputs.

  • Dynamic arrays spill results automatically and report blocked spill ranges
  • FILTER extracts records using single and multiple Boolean criteria
  • SORT and SORTBY order outputs by calculated fields and custom sequences
  • UNIQUE produces distinct lists, occurrence counts and validation sources
  • SEQUENCE creates period headers, identifiers and calendar-driven calculation grids
  • TAKE, DROP, CHOOSECOLS and CHOOSEROWS reshape report-ready arrays
  • Legacy CSE array formulas identified and converted where appropriate

Participants can calculate controlled totals, counts and measures using multiple business criteria.

  • SUMIFS and COUNTIFS apply aligned criteria ranges for controlled aggregation
  • AVERAGEIFS, MAXIFS and MINIFS calculate conditional operational measures
  • SUMPRODUCT handles weighted averages, multi-condition totals and array arithmetic
  • SUBTOTAL and AGGREGATE respect filters while excluding errors or hidden rows
  • Criteria syntax manages dates, wildcards, operators and cell-based thresholds
  • Rolling-period calculations combine EDATE, EOMONTH and date criteria
  • Frequency distributions built with COUNTIFS and dynamic bin thresholds

Participants can convert inconsistent imported fields into calculation-ready data.

  • TEXTSPLIT, TEXTBEFORE and TEXTAFTER parse structured identifiers and imported fields
  • TRIM, CLEAN and SUBSTITUTE remove nonprinting characters and inconsistent spacing
  • LEFT, RIGHT, MID and FIND extract codes from fixed and variable strings
  • VALUE, NUMBERVALUE and DATEVALUE convert imported text into usable values
  • DATE, EDATE, EOMONTH and WORKDAY calculate reporting and service deadlines
  • NETWORKDAYS.INTL applies local working-week patterns and holiday calendars
  • TEXT formats outputs without converting numeric calculations into text

Participants can apply analytical and financial functions to support evidence-based reporting decisions.

  • Percentiles, quartiles and rank functions identify outliers and performance bands
  • MEDIAN, MODE and standard deviation describe operational data distributions
  • FORECAST.LINEAR and TREND project values from historical observations
  • Correlation and covariance functions test directional relationships between measures
  • NPV, XNPV, IRR and XIRR evaluate dated investment cash flows
  • PMT, IPMT and PPMT build loan repayment schedules
  • Rounding functions prevent reporting discrepancies and reconciliation exceptions

Participants can simplify complex formulas and package recurring logic for consistent reuse.

  • Nested formulas decomposed using LET, helper columns and staged calculations
  • LAMBDA functions package recurring business logic with named parameters
  • BYROW, BYCOL, MAP, REDUCE and SCAN apply reusable array logic
  • CHOOSE and CHOOSECOLS assemble flexible reporting outputs from selected fields
  • Formula comments, labels and naming conventions support peer review
  • Reusable formulas documented with purpose, inputs, outputs and error behaviour
  • Compatibility decisions distinguish Microsoft 365 functions from legacy alternatives

Participants can identify formula defects, validate outputs and reduce avoidable calculation delays.

  • Evaluate Formula traces intermediate results through complex nested expressions
  • Watch Window monitors critical assumptions and control calculations
  • Go To Special locates constants, formulas, errors and blanks
  • ISFORMULA and FORMULATEXT support formula inventory and consistency checks
  • Circular references diagnosed using precedents, iterative settings and control totals
  • Volatile functions, whole-column references and repeated lookups reduced deliberately
  • Error checks reconcile source totals, exceptions and report outputs

Participants can specify, review and improve formula-driven workbooks for operational use.

  • Finance, HR and operations cases translate requirements into formula specifications
  • Formula selection weighs readability, compatibility, scale and maintenance cost
  • Input controls define valid values, ownership and refresh responsibilities
  • Calculation checks separate completeness, reasonableness and reconciliation tests
  • Report outputs distinguish decision measures, detail records and exception lists
  • Peer review uses a formula checklist before workbook release
  • Personal application plan prioritises workbook improvements, owners and review dates

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

What you take back to work

  1. Formula design and naming convention template
  2. Advanced lookup pattern library
  3. Dynamic array reporting model
  4. Conditional aggregation and date calculation reference sheet
  5. Formula audit and workbook release checklist
  6. Personal workbook improvement plan

Who should attend

  • Financial Analysts — build controlled forecasts, budgets, reconciliations and management reports
  • Business Analysts — convert reporting requirements into maintainable Excel calculation models
  • Data Analysts — clean, combine and analyse operational extracts without repeated manual steps
  • Accountants and Finance Managers — review complex formulas and strengthen spreadsheet controls
  • Operations Managers — produce accurate performance measures from service and resource data
  • HR and Workforce Analysts — analyse headcount, absence, payroll and staffing records

Skills and tools covered

Formula engineeringDynamic array analysisLookup designData cleansingWorkbook auditingConditional aggregation

Tools and standards

Microsoft Excel, Excel Tables, XLOOKUP, LAMBDA

How the course runs

Training methodology

The course uses short instructor briefings followed by guided formula construction, individual analysis and small-group review. Cases reflect reporting extracts, budget models, workforce records and operational measures. Participants compare formula options, test edge cases, inspect calculation dependencies and apply audit checks to realistic workbooks. The instructor demonstrates both Microsoft 365 functions and legacy-compatible alternatives where relevant. Each participant develops an application plan for improving a current or planned workbook, supported by peer feedback and instructor guidance.

Requirements and prerequisites

Participants should be confident using Excel worksheets, entering formulas, applying basic cell references, sorting and filtering data, and using functions such as SUM, IF and COUNTIF. Experience maintaining reports or working with operational, financial or people data is useful. Participants should understand that spreadsheet source data can contain duplicates, blanks and inconsistent formats. Prior knowledge of VBA, Power Pivot, Power BI, SQL, statistics or programming is not required. This is not an introductory Excel course; participants should already be comfortable creating standard formulas independently.

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 creating standard formulas, using relative and absolute references, sorting data and applying filters. The course starts with workbook structure and reference control, but it does not teach basic spreadsheet use.

Bring a laptop with Microsoft Excel installed and access to files you may wish to improve after the course. Microsoft 365 Excel is preferred because the course includes dynamic arrays, XLOOKUP, LET and LAMBDA.

It is intended for analysts, finance professionals, accountants, reporting specialists, HR analysts and operations staff who create or maintain complex Excel workbooks. It also suits managers who need to inspect spreadsheet logic and challenge calculation quality.

This course concentrates on formula design, functions, audit methods and workbook controls within Excel. It does not focus on dashboard development, data modelling in Power BI, macros or VBA programming.

Participants can apply the methods to recurring reports, reconciliations, forecasts, staffing analyses and data extracts. The formula patterns and audit checklist can be used when building a new workbook or reviewing an existing one.

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