Home / Masterclasses / AI and Microsoft / Excel Power Session: XLOOKUP, PivotTables and Dynamic Arrays (3-hour online masterclass)
Quick Look Course Summary:Excel Power Session: XLOOKUP, PivotTables and Dynamic Arrays (3-hour online masterclass)
-
Next Public Course Date:
-
Length: 1 day(s)
-
Price (at your venue): 1 Person R 8,159 EX VAT 3 Person R 5,295 EX VAT 10 Person R 3,794 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Live online on Zoom or Microsoft Teams - for one person or a whole organisation, anywhere in South Africa.
Get Free & personalised
Training Advice
Excel Power Session: XLOOKUP, PivotTables and Dynamic Arrays (3-hour online masterclass)
In three hours your team replaces manual matching, copy-paste summaries and fragile formulas with XLOOKUP, PivotTables and dynamic arrays – the three tools that do most of the work in a modern Excel report.
Most month-end packs, sales reports and HR registers in South African offices are still assembled by hand: codes matched by eye, totals copied across, nested VLOOKUPs that break when a column moves. This three-hour Excel masterclass concentrates on the three tools that end that – XLOOKUP, PivotTables and the dynamic array functions in Microsoft 365. No tour of the ribbon and no basics: only the features that do most of the work in a modern Excel report.
It is a focused cut of BOTI's Excel Advanced and PivotTables courses, run live on Zoom so that a whole finance, sales or admin team can attend together. Delegates work on their own screen with the practice files sent beforehand: a price list joined to a sales export with XLOOKUP, a payroll or sales export summarised by month and branch in a PivotTable with slicers, and a summary built on FILTER, SORT, UNIQUE and SUMIFS that updates itself when the data changes.
Every exercise ends with the same question – does the total still agree with the source? – and the session closes with a personal action list: the one report at work each delegate will rebuild next week with the new tools.
Why this course
For your organisation
- Reports that update when the data changes instead of being rebuilt every month
- Fewer lookup and copy-paste errors in management packs, pricing and payroll checks
- A whole team on the same modern Excel tools in one morning
- Less reliance on the one person in the office who 'does the spreadsheets'
- A practical step before the full Excel Advanced or PivotTables course
For your delegates
- XLOOKUP that does what VLOOKUP never could, learnt on real exports
- Your first PivotTable with slicers, built in minutes and refreshed instead of rebuilt
- Dynamic array formulas that spill the answer and keep themselves current
- The practice files, a PDF workbook and a one-page formula sheet to keep
- One report from your own work chosen to rebuild next week
What delegates will be able to do
- Match data between two tables with XLOOKUP, including lookups to the left and a message when nothing is found
- Return several columns from one lookup and handle approximate matches for price bands and commission tiers
- Turn a range into an Excel table so that formulas and PivotTables grow with the data
- Build a PivotTable that summarises an export by month, branch and category, with formats that survive a refresh
- Add slicers and a timeline so that colleagues can filter a report without breaking it
- Use Show Values As for shares of total, differences on last month and running totals
- Write FILTER, SORT and UNIQUE formulas that spill a live list onto a report sheet
- Combine SUMIFS, COUNTIFS and a spilled list into a summary that updates itself
- Check a rebuilt report against its source before it goes to a manager
Who should attend
People who already use Excel every day and want its modern tools: bookkeepers and accounts staff, sales and operations administrators, HR and payroll officers, analysts, project coordinators and team leaders who assemble a monthly report from system exports. Delegates should be comfortable with basic formulas, sorting and filtering; no previous PivotTable experience is needed. Staff who are new to Excel should start with Microsoft Excel Beginners (1 day).
Course outline
Live session – XLOOKUP, PivotTables and dynamic arrays on real office data
Opening: the three tools and the practice files
- Why manual matching and copy-paste summaries break, and what replaces them
- Microsoft 365 and Excel 2021/2024: which features need which version
- Turning a range into a table: the habit everything else depends on
- A look at the practice files: a price list, a sales export and a leave register
XLOOKUP: matching data between tables
- XLOOKUP against VLOOKUP: no column counting, matches to the left, a message when nothing is found
- Pulling a product name, price and supplier into a sales export with one formula
- Approximate matches for price bands, commission tiers and tax brackets
- Two-way lookups and lookups into another workbook
- On-screen exercise: a sales export priced from the price list and checked against the invoice total
PivotTables: a month-end summary in minutes
- Building a PivotTable from an export: rows, columns, values and filters
- Grouping dates by month and quarter, and numbers into bands
- Slicers and a timeline for a report managers can filter themselves
- Show Values As: share of total, change on last month, running total
- Number formats and layouts that survive a refresh, and refreshing when new data arrives
- On-screen exercise: a payroll or sales export summarised by branch and month, with slicers
Dynamic arrays and your action list
- How spilled results work, and what the #SPILL! error is telling you
- FILTER, SORT and UNIQUE for a live list of overdue accounts or active staff
- SUMIFS and COUNTIFS against a spilled list of categories for a self-updating summary
- Checking the rebuilt report against the source
- Your personal action list: the one report you will rebuild next week
How it is delivered
Live online on Zoom (or Microsoft Teams), 2.5 to 3 hours – typically 09:00-12:00, or a time that suits the team. From 1 to 500 delegates can join. Handouts are electronic: a PDF workbook, the practice files and templates, and a one-page formula summary are emailed before the session. Delegates need a laptop with Microsoft 365 or Excel 2021/2024 – XLOOKUP and dynamic arrays are not in older versions – so that they build every exercise on their own screen. The masterclass runs for a public group on scheduled dates, or privately for one organisation on a date of its choice.
What our clients say
“Business Optimization Training Institute facilitated Microsoft Excel Training for our company. The course was efficiently scheduled and planned around our timetable. We were impressed by the quality of training.”
“The training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
“The facilitator effectively encouraged participation and used practical examples, discussions and interactive activities to ensure that the concepts were understood and could be applied in the workplace.”
Frequently asked questions
How many people can join?
From 1 to 500 delegates on Zoom. Everyone works in their own copy of the practice files while the facilitator shares the screen step by step, and questions come through the chat, so a large group practises as much as a small one.
Do we get handouts?
Yes, electronically: a PDF workbook, the practice workbooks and templates, and a one-page summary of the formulas, emailed before the session.
Can we run it privately for our team?
Yes. We run it for one organisation on a date and time of your choice, and the facilitator can build the exercises on a sample of your own exports (with personal information removed).
Which version of Excel do delegates need?
Microsoft 365 or Excel 2021 and later. XLOOKUP and the dynamic array functions (FILTER, SORT, UNIQUE) do not exist in Excel 2016 or 2019; delegates on those versions can follow the PivotTable module but not the other two.
How does this differ from the full Excel courses?
This is the three-hour version: three tools, worked hard, for a group of any size. Microsoft Excel Advanced (1 day) adds what-if analysis, Power Query, auditing and macros, and Excel Pivot Tables, Dashboards and Power Pivot (2 days) takes PivotTables through to dashboards and the Data Model.