Key Training Logo

Microsoft Excel 2013: Level 3

Course Code: XL13L3

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

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