Key Training Logo

Microsoft Excel 2016 Level 2

Course Code: XL16L2

Duration: 1 Day

Course Aims:

This Microsoft Excel 2016 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
▪ Create range names and use range names in formulas
▪ 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 lists and filter information

 

Course Content:

Introduction and Objectives

Using Large Excel Worksheets
▪ Increasing & decreasing the magnification
▪ Splitting the window,
▪ Freezing & unfreezing panes

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)
▪ Drawing and formatting ‘Shapes’
▪ Inserting SmartArt

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

Creating Charts
▪ Creating, moving, resizing, printing & deleting charts
▪ Using built-in chart styles and ‘Quick Layouts’
▪ Formatting chart objects & data series
▪ Adding & removing chart elements (eg. 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

Action planning

Review of Programme

Key Affiliations

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