Excel App: Intermediate–Advanced Pack - Full Lesson

This Excel App training pack helps learners move confidently from intermediate to advanced skills through realistic, workplace‑style scenarios. It includes a structured tasks workbook (fully editable), practice files, and completed examples, plus a concise slide deck to support delivery. Topics include importing delimited data, custom sorts and advanced filters, PivotTables, and core functions such as IF and VLOOKUP.

What’s included

Editable tasks workbook (Int→Adv) covering:

Importing delimited text (CSV/TXT) into named sheets and ranges
Multiple‑column sorts and Custom Lists (e.g. Mon→Sun, role hierarchies)
Custom AutoFilter and Advanced Filter with criteria ranges (AND/OR, multi‑field)
PivotTables (build, analyse, refresh)
IF and VLOOKUP/HLOOKUP tasks in practical contexts (discounts, invoices, travel mileage)

Presentation/lesson slides outlining modules and intentions (importing, advanced sorting/filtering, PivotTables, IF, VLOOKUP) for quick recap and teacher prompts.

Practice files + completed examples so learners can compare their results and self‑correct.

Examples of scenarios covered:

• Importing records of employees saved as delimited text and sort this list in hierarchical order (Director, Manager, Supervisor…)
• Using advanced filtering to find invoices; invoice numbers less than 170 and amounts of more than £1,000 excluding
• Creating and updating a pivot table for a list of people surveyed so that the data items in the pivot table are a count of the people surveyed, organised by town and subdivided into gender
• Using a Lookup function to search a product code in a table of products and return the description of the product or its unit price, without copying and pasting it.
• Using the IF function to calculate travel expenses refunds when mileage is differentiated into two different tiers. e.g. Each employee is reimbursed £0.35 per mile for the first 500 miles travelled (Tier 1) and £0.25 thereafter (Tier 2).