Excel Advanced Training | Live Online or Onsite

< 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