Skip to content

# Excel Intermediate Training | Live Online or Onsite

### Excel Intermediate: 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 Intermediate course is designed for delegates who are proficient users of Excel but who need to improve their knowledge in the following areas – IF, COUNTIF, SUMIF, VLOOKUP and HLOOKUP functions, database work, PivotTables and conditional formatting. The course is focused on those skills that are most commonly required within the workplace. It is therefore suitable for delegates who do quite a bit of Excel work but need to become speedier and more proficient at what they do. Download the course outline in pdf format.

Introduction to Logical Functions
• Comparison operators
• Create logical test formulas to return TRUE or FALSE
• Use the IF function to return values or formulas in place of TRUE or FALSE
• Use COUNTIF and COUNTIFS to count based on a test
• Use SUMIF and SUMIFS to sum based on a test
Introduction to Lookup Functions
• Use VLOOKUP for exact match lookups
• Use VLOOKUP for approximate match lookups
• Use IFERROR or IFNA to handle NA errors
• Use the HLOOKUP function
• Perform a VLOOKUP across sheets
• Perform a VLOOKUP across workbooks
• New!! XLOOKUP (VLOOKUP and HLOOKUP replacement) – Office 365
Date Functions
• Use WORKDAY and NETWORKDAYS to perform working day calculations
Conditional Formatting
• Apply formatting based on numeric rules eg greater than, less than, between, equal to etc
• Apply formatting based on date values – today, date, this week, this month etc
• Apply formatting based on text rules – contains text or equal to
• Format duplicate or unique values
• Format top and/or bottom values
• Format above/below average values
• Use Data Bars to visually compare numeric data
• Use Colour Scales to visually compare numeric data
• Use Icon Sets to visually represent thresholds/targets
• Edit /delete conditional formats
• Get the order of conditions right
• The ‘Stop if true’ setting
Working with Excel Tables
• Use Excel Tables
• Use the Excel Table Total Row
• Create formula within an Excel Table
• Use Slicers to filter within an Excel Table
• Create a database using Excel Tables and Data Validation
Introduction to PivotTables
• What is a PivotTable?
• How to set up your data correctly for a PivotTable
• Elements of a PivotTable – PivotTable areas
• Understand the PivotTable cache
• Create a basic PivotTable
• Change the calculations used in a PivotTable
• Show multiple calculations in the same PivotTable
• Format values in a PivotTable
• Show/hide subtotals and grand totals
• Switch between PivotTable layouts
• Switch between and create your own PivotTable Styles
• Update and refresh your PivotTable
• Generate Report Filter Pages
• Slice a PivotTable
• Create a PivotChart