Start a conversation ↗

Description

Advanced Excel: Building Analytical Models, Cross-Referencing Data and Automating Routine Tasks

Build workbooks that last and eliminate the manual handling repeated every month

  • 3 days — 21 h
  • In-person or virtual
  • Intermediate
  • Up to 6 participants

The tracking file has grown month after month: layers of formulas nobody dares to touch, manual copy-and-paste before every committee meeting and discrepancies found only after the report has been circulated. More time is spent rebuilding the same tables than analysing them.

Three days to rework the data structure, then build reliable analyses using lookup functions, array formulas and PivotTables. The final day focuses on automation, so the repetitive tasks in the monthly cycle can be removed for good.

Learning objectives

  • Structure a data source that analytical tools can work with
  • Combine lookup, logical and date functions within a single formula
  • Build a PivotTable with groupings and calculated fields
  • Verify workbook reliability using audit and validation tools
  • Automate a repetitive task with a recorded macro and then refine it

What makes this programme different

Participants work on their own workbooks from the second day onwards
Every complex formula is broken down and then rebuilt step by step
A monthly reporting workbook is rebuilt end to end during the programme

Programme

1Structuring the data

What determines every analysis that follows

  • Organising a workable database
  • Structured tables and named ranges
  • Importing and cleaning external data
  • Removing duplicates and standardising formats

2Advanced formulas

Calculating correctly in complex cases

  • Lookup functions and handling missing values
  • Nested conditions and logical functions
  • Calculations on dates and text strings
  • Array formulas and dynamic ranges

3Analysis with PivotTables

Answering a question in a few clicks

  • Creating and refreshing a PivotTable
  • Grouping by period and by band
  • Calculated fields and calculated items
  • Slicers and timelines for filtering

4Reliability and presentation of results

Making the workbook reviewable by a third party

  • Auditing formulas and tracing precedents
  • Data validation and input messages
  • Rule-based conditional formatting
  • Protecting sheets and input cells

5Automating routine tasks

Removing repeated manual handling

  • Recording and running a macro
  • Reading and editing the generated code
  • Buttons and triggering routines
  • Choosing between a macro and native functions

Who is it for

Regular Excel users in finance, accounting, management control, procurement or human resources who produce analytical reports.

Prerequisites

Confident use of basic Excel formulas and of relative and absolute references.

Dates & locations

36 scheduled dates between November 2026 and December 2027. Seats are confirmed in the order enquiries are received.

November 2026

December 2026

January 2027

February 2027

March 2027

April 2027

May 2027

June 2027

September 2027

October 2027

November 2027

December 2027

None of these dates suit you? We open additional sessions on request, and any programme can be run privately for your team.

Practical details

Before the programme
Online positioning questionnaire. Your development objectives are shared with the trainer, who tailors the practical case studies to your context.
Teaching methods
Theoretical input, workshops and practical case studies. Digital course materials and method sheets provided.
Assessment
Multiple-choice tests and role-play exercises. Assessment of learning at the start and end of the programme, with immediate and 60-day follow-up evaluations.
After the programme
One year of access to the e-learning platform. Self-assessment of the skills acquired and a 30-day follow-up session with your trainer.
How to register
Registration online or on the basis of a quotation.
Lead time
11 working days after confirmation of registration.
Accessibility
Accessible to people of determination. Contact our accessibility coordinator to design a suitable solution: contact@mpf-academy.ae
Start dates
Rolling intake: in addition to the scheduled sessions, this programme can start on request.