Key Training Logo

Microsoft Excel 2019/365 Level 3

Course Code: XL19L3

Duration: 1 Day

Course Aims:

This course is aimed at people who want expand their knowledge into some of the more advanced calculation and analytical capability of Microsoft Excel.

Course Pre-requisites:

Students who wish to attend this course must have a good knowledge of Excel and be competent at working with basic formulas and functions, multiple sheets and multiple files.

Course Objectives:

On completion of this course delegates will be able to:
▪ Use logical, lookup, round and error functions
▪ Use auditing tools
▪ Group and outline data
▪ Consolidate data on a worksheet by category and position
▪ Set restrictions on data entry using data validation
▪ Apply custom formats and use conditional formatting
▪ Protect an Excel workbook and worksheets
▪ Use criteria ranges to analyse data using the Advanced Filter
▪ Use the Subtotal feature
▪ Create and edit Pivot Tables and Pivot Charts


Course Content:

Introduction and Objectives

Using Logical, Lookup & Round Functions
▪ Using IF, IFS, AND & OR function
▪ Using the VLOOKUP/HLOOKUP functions
▪ Using the IFERROR function
▪ Using ROUNDUP/ROUNDDOWN functions

Using Auditing Tools
▪ Show/Hide formulas
▪ Displaying precedents and dependants
▪ Removing arrows
▪ Error checking
▪ Evaluating a formula
▪ Using the ‘watch’ window

Working with Outlines
▪ Applying, expanding & collapsing an outline
▪ Modifying & clearing outline settings
▪ Using Auto and Clear outline

Using Data Validation
▪ Validating data
▪ Creating a custom error message
▪ Removing a data validation
▪ Using the Go To Special dialog box

Protecting Excel Data
▪ Protecting cells on a worksheet
▪ Unlocking & locking cells on a worksheet
▪ Making exceptions to cell protection
▪ Protecting worksheets from being inserted,
moved, deleted or hidden/unhidden
▪ Adding protection to control how a workbook
opens (full protection; read-only)
▪ Setting manual & automatic calculation

Creating/Revising Pivot Tables and Pivot Charts
▪ Creating a Pivot Table
▪ Adding, moving, removing Pivot Table fields
▪ Formatting and structuring a Pivot Table
▪ Filtering Pivot Table items
▪ Adding a Pivot Table slicer
▪ Adding a Timeline for filtering date ranges
▪ Changing the summary function
▪ Summarising data as percentages
▪ Refreshing a Pivot Table
▪ Changing the data source for a Pivot Table
▪ Moving & deleting a Pivot Table
▪ Using ‘Recommended Pivot Tables’
▪ Using the ‘Quick Analysis button” to create a
Pivot Table
▪ Creating a Pivot Chart
▪ Adding, moving and removing Pivot Chart fields
▪ Formatting and structuring a Pivot Chart
▪ Filtering Pivot Chart categories and data series
▪ Adding a Pivot Chart Slicer
▪ Refreshing a Pivot Chart
▪ Changing the data for a Pivot Chart
▪ Moving and deleting a Pivot Chart
▪ Adding a Timeline for filtering date ranges
▪ Using the ‘Quick Analysis button” to create a
Pivot Chart

Using Conditional & Custom Formats
▪ Applying a comparative conditional formatting
▪ Applying a top/bottom conditional format
▪ Managing conditional formats (editing and
▪ Applying built-in data bars, colour scales and
icons sets to numeric data
▪ Using a formula to conditionally format data
▪ Using the ‘Quick Analysis button” to add
conditional formatting
▪ Creating special codes to format numbers and

Creating Subtotals in a List
▪ Creating subtotals for groups of data
▪ Multiple subtotals
▪ Removing subtotals
▪ Copying subtotals to another

Working with Advanced Filters
▪ Creating a criteria range
▪ Using the Advanced Filter
▪ Copying filters to another worksheet

Using Range Names
▪ Jumping to a named range
▪ Assigning names to ranges
▪ Using range names in formulas
▪ Editing and deleting named ranges
▪ Applying range names to existing formulas
▪ Creating named ranges across sheets

Appendix– Custom Views (if time)
▪ Creating, displaying and deleting views

Action planning

Review of Programme

Key Affiliations

Investors in people
5 Percent Club
RI Top 500
REC Logo
Skills for health Quality Mark
European Union
Best Company 2019
RI Supplier Awards
Team Service Provider
Youth Employment UK
Inspiring the future