Key Training Logo

Microsoft Excel 2016 Level 4

Course Code: XL16L4

Duration: 1 Day

Course Aims:

This Microsoft Excel 2016 Level 4 course is aimed at people who want to expand their knowledge into the more specialised analytical functionality and built-in automation offered by Microsoft Excel.

Course Pre-requisites:

Students who wish to attend this Microsoft Excel course must have a very good knowledge of Excel and be competent at working with advanced functions (e.g. Logical & Lookup) and have some basic knowledge of Excel’s analytical capabilities (eg. Pivot Tables and database tools).

Course Objectives:

On completion of this course delegates will be able to:
▪ Find and calculate data using lookup, math and statistical functions
▪ Calculate with dates
▪ Manipulate text using calculations and text functions
▪ Import and export data from/to other applications and text files
▪ Carry out “What-If Analysis” using Scenario Manager, Goal Seek and Solver
▪ Create dashboard interfaces using form controls
▪ Create standardised worksheets with the use of templates
▪ Automate common tasks in worksheets by recording macros
▪ Assign macros to the Quick Launch Toolbar, keystrokes and worksheet buttons

 

Course Content:

Introduction and Objectives

Further Functions
▪ INDEX
▪ MATCH
▪ SUMIF(S)
▪ COUNTIF(S)
▪ Date Functions
▪ Text Functions

Importing & Exporting Data
▪ Importing Data from text files
▪ Importing Data from the web
▪ Changing external data range properties
▪ Removing the query definition
▪ Exporting data

What If Tools
▪ Using the Scenario Manager
▪ Displaying a scenario
▪ Creating a scenario summary report
▪ Obtaining the desired result to a formula using Goal Seek

Using Form Controls in a Workbook
▪ Creating a check box, list box, combo box, options buttons & spin button

Using Templates
▪ Creating, using, editing and deleting templates

Introduction to Macros
▪ Defining & running macros
▪ Recording a macro
▪ Assigning a shortcut key
▪ Adding a custom button to the Quick Launch Toolbar
▪ Adding a macro button to a worksheet

Action planning

Review of Programme

Key Affiliations

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