Key Training Logo

Microsoft Excel 2013: Level 2

Course Code: XL13L2

Duration: 1 Day

Course Aims:

This Microsoft Excel 2013 Level 2 course is aimed at people who want 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

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