Key Training Logo

Microsoft Excel 2019/365 Level 2

Course Code: XL19L2

Duration: 1 Day

Course Aims:

This Microsoft Excel 2019/365 Level 2 course is aimed at people who want to expand their basic knowledge of Microsoft Excel to work with larger spreadsheets and workbooks, create charts and manage tables of data.

Course Pre-requisites:

Students who wish to attend this course must have a basic knowledge of Excel and feel comfortable with creating, saving, editing and formatting spreadsheets, and creating simple formulas and functions.

Course Objectives:

On completion of this course delegates will be able to:
▪ Manage and enhance large Excel worksheets
▪ Create formulas across multiple worksheets
▪ Copy formats and values across multiple worksheets
▪ Copy and link information across multiple workbooks
▪ Represent data graphically using charts
▪ Understand the concepts an Excel database and create and modify data tables
▪ Sort and filter list

 

Content:

Introduction and Objectives

Using Large Excel Worksheets
▪ Zoom in and Zoom out
▪ Splitting the window
▪ Freezing & unfreezing panes

Notes
▪ Insert, Edit, Show, Hide and Delete notes
▪ Printing notes

Working with Multiple Worksheets
▪ Navigating between and selecting worksheets
▪ Renaming worksheets
▪ Selecting multiple worksheets
▪ Colouring worksheet tabs
▪ Copying & moving worksheets
▪ Copying and moving data between worksheets
▪ Using grouped worksheets
▪ Creating formulas across sheets
▪ Creating aggregate functions across sheets
▪ Referencing cells across sheets
▪ Hyperlinking between sheets
▪ Using Paste Special & Flash Fill
▪ Working with Paste Special
▪ Copying values & formulas between worksheets
▪ Performing mathematical operations
▪ Creating data sequences using ‘Flash Fill’

Analysing Data Tables
▪ Sorting tables
▪ Sorting a table by multiple fields
▪ Filtering data in a table
▪ Creating a custom filter
▪ The SUBTOTAL function
▪ Using the ‘Quick Analysis button” to add totals
to a table

Appendix – Using Graphics in a Workbook (if time)
▪ Inserting SmartArt and Screenshot

Working with Excel Data Tables
▪ Creating & modifying a data table
▪ Working with the data form
▪ Creating an Excel table
▪ Using the ‘Quick Analysis button” to create an
Excel table
▪ Use the Slicer to filter an Excel table

Using Multiple Workbooks
▪ Opening, activating & tiling workbooks
▪ Copying data between workbooks
▪ Linking cells between workbooks
▪ Managing and troubleshooting workbook links

Data Consolidation
▪ Consolidate data from multiple worksheets and workbooks
▪ Create links to source data

Creating Charts
▪ Creating, moving, resizing, printing & deleting
charts
▪ Formatting chart objects & data series
▪ Adding & removing chart elements (e.g. titles,
data labels, legend)
▪ Changing the data source
▪ Changing chart type and location
▪ Filtering chart categories and data series
▪ Creating chart templates
▪ Using ‘Recommended Charts’
▪ Advanced charting
▪ Creating a combination chart
▪ Using a secondary axis
▪ Adding a trendline
▪ ‘Sparklines’ (mini charts in a single cell)
▪ Using the ‘Quick Analysis button” to create a
chart
▪ Copying a chart into Word/PowerPoint as a
picture and as link

Action planning

Review of Programme

Key Affiliations

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