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

Posted by Blue Pecan Computer Training