Key Training Logo

Microsoft Excel 2016 Level 3

Course Code: XL16L3

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 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
▪ Insert and delete comments into a worksheet
▪ Protect an Excel workbook, cells and worksheets
▪ Use the Subtotal feature
▪ Create and edit Pivot Tables and Pivot Charts
▪ Use criteria ranges to analyse data using the Advanced Filter and Dbase functions


Course Content:

Introduction and Objectives

Using Logical, Lookup & Round Functions
▪ Using the VLOOKUP/HLOOKUP functions
▪ Using IF, AND & OR function
▪ Using the ISERROR function

Using Auditing Tools
▪ 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 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

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

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

Working with Advanced Filters
▪ Creating a criteria range
▪ Using the Advanced Filter
▪ Dfunctions

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

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

Action planning

Review of Programme

Key Affiliations

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