Skip to content

Excel Advanced Training | Live Online or Onsite

    Excel Advanced: 1 Day Course

    < Back to Excel Course Outlines

    Our training courses can be delivered in-house at your offices or remotely online. Please note the outline can be tailored – nothing is set in stone!  Our Excel Advanced course is designed for delegates who wish to extend their knowledge of the key subject areas covered in the Intermediate Excel course – IF, LOOKUP, conditional formatting and PivotTables. Delegates need to be comfortable with the intermediate content before progressing to this level. The course also covers a basic introduction to recorded macros – a means of automating repetitive tasks. Download the course outline in pdf format.

    Logical Functions
    • Use AND and OR with IF to perform multiple tests simultaneously
    • Use the NOT function
    • Create nested IF functions for extended output
    • Use the IFS function for extended output (Office 365 only)
    Lookup Functions
    • Lookup to the left using MATCH and INDEX
    • Use MATCH and INDEX to perform a two-way lookup
    • Use the INDIRECT Function to perform a two-way lookup
    • Return a complete record using MATCH and INDEX
    • Use the SWITCH (Office 365 only) or CHOOSE to lookup across multiple tables
    • New!! XLOOKUP (improved function for VLOOKUP, HLOOKUP, MATCH & INDEX scenarios) – Office 365
    Introduction to Array Formulas
    • What are array formulas and why are they useful?
    • Conditional calculations using array formulas
    • Useful lookup techniques using array formulas
    • Using SUMPRODUCT
    • New!! – Dynamic Arrays including spill range, spill reference & the #SPILL error (replaces old CTRL SHIFT ENTER array formulas) – Office 365
    • New Array Functions!! – UNIQUE, SORT, FILTER & SEQUENCE Functions – Office 365
    Conditional Formatting Using Formula
    • Use AND and OR to apply multiple tests simultaneously
    • Format an entire row based on a condition in one or more fields
    • Use functions such as COUNTIF, ISTEXT and ISFORMULA to set up a conditional formatting rule
    • Use text functions to format text values that begin with or end with characters
    • Format duplicates solution (better than the inbuilt solution)
    • Format duplicates across multiple columns
    Database Work
    • Use Excel’s Advanced Filter to perform complex filters and/or return unique values
    • Use database functions – DSUM, DAVERAGE etc
    PivotTables
    • Prepare data for a PivotTable – common data problems
    • Group data – date, numeric, text fields
    • Calculated items and named sets
    • Calculated fields
    • Format slicers and timelines
    • Connect slicers and timelines to multiple reports
    • Apply conditional formatting to a PivotTable
    Introduction to Recorded Macros
    • Show the Developer tab
    • Record a basic macro
    • Assign a macro to a Quick Access Toolbar button, a Ribbon button and to a Command button
    • Save a workbook that contains a macro
    • Save a macro to the Personal Macro workbook