Global

Advanced excel: Lookups, pivottables and automation

A ready-made course you customise for your organisation, with a live session deck and two surveys. You only add the 9 details that are specific to your organisation.

See the live deckSee the full outline
Try the coursePreview the survey
Length
50 min
Lessons
6 lessons
Frequency
For analysts after Excel essentials
Last reviewed
21 Oct 2026
Dashboards in Excel and their grey areas
What would you do?

The dashboard figures are last month's because the PivotTable was not refreshed

Try it: this is one of 21 interactive activities in the course.

What's included

Three pieces that work together

Copy once, get everything. Learners prepare on their own, you answer questions live, and the surveys tell you what stuck.

Self-paced course

Short lessons with real workplace scenarios, not walls of text.

  • 6 lessons, about 50 minutes
  • Process, accordion, scenario, tabs and quizzes

Live session deck

An AhaSlides presentation to run after the course, in person or online.

  • 12 slides
  • Quiz, poll, word cloud, scale slides
  • A closing slide for your contacts

Two surveys

Find out what landed, and whether people use it on the job.

  • Post-course feedback, about 2 minutes
  • 30-day check-in
  • Anonymous by default
  • Results feed your next refresher

Learning objectives

At the end of this course, learners can

Each objective and the lesson that covers it.

IDObjectiveSourceWhere it's covered
TO 193.0Achieve ≥ 80% on the final assessmentnoneWhole course
SO 193.1Choose and predict the result of a lookup function, including an exact match and a not-found resultBest practice; suite documentationWhole course
SO 193.2Build a PivotTable that summarises a table and refresh it after the data changesBest practice; suite documentationWhole course
SO 193.3Prevent and fix bad data with validation rules, named ranges and cleaning stepsBest practice; suite documentationWhole course
SO 193.4Build a dashboard whose figures trace back to the source and are checked before sharingBest practiceWhole course
SO 193.5Explain what a macro is, record a simple one, and follow the organisation's rules on macro files + your infoBest practice; organisation's IT policyWhole course

Your part: what a template can't do for you

  • Your workplace details: fill in the 9 highlighted fields so the course describes your own policies, contacts and locations.
  • Records: record completion in your own LMS or record system.

Course outline

6 lessons, about 50 minutes

Minutes per lesson are an estimate, shared out by page count.

ContentInteractiveVideoYou customise
1

A lookup that returns the wrong price: lookup functions

3 pages

10 min
Before you beginNoteList
An invented storyTextImage
Which lookup?TabsKey factQuiz
2

PivotTables for summaries

3 pages

10 min
The four areasAccordion
Build one, step by stepProcessText
Predict the summaryFlashcardsSorting activityMatching quiz
3

Data validation, cleaning, named ranges and dynamic arrays

3 pages

9 min
Stop bad data at the doorTabs
Clean what is already thereProcess
Names and spillsTimelineChecklistMulti-select quiz
4

Dashboards in Excel and their grey areas

3 pages

9 min
ScenarioScenario
ScenarioScenario
ScenarioScenarioAccordionMatching quiz
5

An introduction to macros and VBA, and where to get help

3 pages

9 min
What a macro isAccordion
Record, run, stopProcessText
Who to askProcessYour infoYour infoYour infoYour infoYour infoQuiz
6

Final assessment

1 page

3 min
PracticeQuizKey factNoteButtonYour info

Live deck and surveys

12 slides to run live, two surveys to close the loop

Pink slides are interactive. Both surveys are anonymous; pink questions are written for this course.

ContentTitle, agenda
PollWhich task takes you longest in a reporting cycle?
Word cloudA formula error that has cost you time
ContentExact match, lookups and the four PivotTable areas
QuizA lookup must return an exact match. What do you set? (The exact-match argument ✔)
QuizNew rows were added and the PivotTable looks old. You… (refresh it ✔)
QuizA manager asks you to paste values and delete the source. You… (keep a linked version and label the copy ✔)
Content · customiseCustomise: our version {{OFFICE_SUITE_IN_USE}} and macro rules {{MACRO_POLICY_LINK}}
ScaleHow confident are you building a PivotTable without help?
Open-endedA manual task I would like to automate
Q&AAsk the trainer anything
Content · customiseClosing {{IT_HELPDESK_CONTACT}}, {{COURSE_URL}}, {{SURVEY_1_URL}}, {{SURVEY_2_URL}}
Post-course feedback: 11 questions, about 2 min
  1. Overall rating 5 stars
  2. Relevant, realistic, right length, helpful activities agree/disagree grid
  3. What was most useful? multi-select
  4. Confidence before this course, and now 1-5 scale
  5. Estimated time saved per week on reporting tasks after 30 days (hours) short text
  6. Which techniques have you used since the course? multiple choice
  7. Since the course, have you checked a dashboard or report total against its source before sharing? multiple choice
  8. I would tell my team if I found a mistake in a report I had already sent. 1-5 scale
  9. One thing we should change or add open text
  10. Would you recommend this course? NPS
30-day check-in
  1. Have you used what you learned since the course? multiple choice
  2. How confident are you today? Compare with day 1 1-5 scale
  3. Do you know where to find the team workbook template and the macro rules? yes / no / not sure
  4. A moment where the training helped, or where you weren't sure open text
  5. Which topics should the next refresher focus on? ranking

Results show whether people use the training on the job, not just whether they finished it.

Make it yours

9 fields to fill in, and you're ready

Everything else is done. These are the details only your organisation knows; each one is highlighted inside the course.

  • Office suite in use{{OFFICE_SUITE_IN_USE}}
  • Team workbook template{{TEAM_WORKBOOK_TEMPLATE}}
  • Macro policy link{{MACRO_POLICY_LINK}}
  • It helpdesk contact{{IT_HELPDESK_CONTACT}}
  • Security incident route{{SECURITY_INCIDENT_ROUTE}}
  • LMS or record system{{LMS_OR_RECORD_SYSTEM}}
Show all 9 fields
  • Course URL{{COURSE_URL}}
  • Survey 1 URL{{SURVEY_1_URL}}
  • Survey 2 URL{{SURVEY_2_URL}}

FAQ

Common questions

Can I run advanced excel: Lookups, pivottables and automation training online?

Yes. The course is self-paced and online, and the live deck lets you add a live session in the room or online.

How often is this training needed?

For analysts after Excel essentials, before a month-end or reporting cycle, or when a team inherits a large workbook.

Who needs this training?

Finance, operations and reporting analysts who already use Excel daily; run by L&D with finance leads.

What records should I keep?

Record completion in your own LMS or record system, so you can show who finished the course and when.

Can I edit the content?

Yes, everything. Change the wording, swap scenarios for ones from your workplace, add your logo, or remove parts that don't apply.

Related templates

Often taught together

All Office productivity templates →
Global

Microsoft 365 and google workspace collaboration basics

Staff moving to a cloud office suite or onboarding to a team's shared tools · Office productivity

35 min
Global

Excel essentials for everyday work

Office staff · Office productivity

45 min
Global

Powerpoint and presentation design skills

Professionals · Office productivity

40 min
Global

Outlook: Email and calendar management

Office staff and managers · Office productivity

30 min
Last reviewed: 21 October 2026Templates are a starting point, not legal advice.

Ready to run it?

Open the live deck to see how the session runs alongside the course and both surveys.

See the live deck