Microsoft Excel 2019/365 Level 4

Course Code: XL19L4

Duration: 1 Day

Course Aims:

This Microsoft Excel 2019/365 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 (e.g. 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 date functions
▪ 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 and Goal Seek
▪ Create standardised worksheets with the use of templates
▪ Create dashboard interfaces using form controls
▪ Automate common tasks in worksheets by recording macros
▪ Assign macros to the Quick Access Toolbar, shortcut keys and worksheet buttons

Course Content:

Introduction and Objectives

Further Functions
▪ DATE FUNCTIONS (Year, Month, Today)
▪ SUMIF(S)
▪ COUNTIF(S)
▪ AVERAGEIF
▪ MAXIF(S)
▪ MINIF(S)
▪ TEXT FUNCTIONS (e.g., TextJoin, Mid)
▪ MATCH
▪ INDEX

Importing & Exporting Data
▪ Importing Data from text files
▪ Importing Data from other sources
▪ Changing external data range properties
▪ Exporting data

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

Using Templates
▪ Creating, using, editing and deleting templates

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

Introduction to Macros
▪ Recording and running macros
▪ Assigning a shortcut key
▪ Adding a custom button to the Quick Access
Toolbar
▪ Assigning a macro to a button/object on a
worksheet
▪ Editing a macro
▪ Un-hiding and hiding personal macros
▪ Deleting macros
▪ Saving the spreadsheet as an macro enable
workbook

Action planning

Review of Programme