Start a conversation ↗

Description

Intermediate Excel: Building Reliable Formulas, Cross-Tabulating and Consolidating Data

Move from patched-together spreadsheets to reliable workbooks that another user can pick up without rebuilding everything

  • 2 days — 14 h
  • In-person or virtual
  • Intermediate
  • Up to 6 participants

The workbook works, but nobody can find their way around it. Files are reconciled by hand, nested formulas are no longer readable, free-text entry introduces inconsistent labels, and every monthly summary means rebuilding the same tables from scratch. More time is spent correcting than analysing.

These two days mark a step change. Participants structure their data so that it can genuinely be analysed, master lookup functions and conditional calculations, build a pivot table from raw extract to dashboard, then secure data entry and consolidate information drawn from several sheets or files.

Learning objectives

  • Structure data in a form that analysis tools can use
  • Apply lookup functions and conditional calculations
  • Build and format a pivot table
  • Secure data entry through drop-down lists and validation rules
  • Consolidate data drawn from several sheets or files
  • Design clear charts suited to the message

What makes this programme different

Workbooks brought by participants are restructured during the session
Lookup functions are practised on deliberately imperfect data
A pivot table is built from the raw extract through to the dashboard

Programme

1Structuring Your Data

Preparing a base that can genuinely be analysed

  • Rules for a well-built data table
  • Converting to a structured table and naming ranges
  • Cleaning labels and removing duplicates
  • Separating raw data
  • calculations and reporting

2Lookup and Calculation Functions

Linking and conditioning your data

  • Vertical lookup and match-based functions
  • Handling missing values and reconciliation errors
  • Nested conditional functions and logical functions
  • Criteria-based calculations for sums and counts
  • Date and text functions for everyday use

3Pivot Tables

From data set to dashboard

  • Creating a pivot table and selecting fields
  • Grouping by period and by category
  • Percentage calculations and calculated fields
  • Slicers and filters for interactive reading
  • Formatting and refreshing the data

4Reliability and Consolidation

Making the workbook easy to hand over

  • Drop-down lists and validation of entered data
  • Protecting sheets and calculation cells
  • Consolidating several sheets or files
  • Choosing the chart that fits the message

Who is it for

Regular Excel users as well as accounting assistants · administrators and reporting officers who work with large tables.

Prerequisites

Comfortable with data entry | simple formulas and basic table formatting.

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.