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
rule
▪ Applying a top/bottom conditional format
▪ Managing conditional formats (editing and
clearing)
▪ 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
dates

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

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
Matrix
Ofsted
Apprenticeships
5 Percent Club
RI Top 500
REC Logo
Skills for health Quality Mark
AELP
European Union
ESFA
Best Company 2019
RI Supplier Awards
ARC
Team Service Provider
Youth Employment UK
Inspiring the future