Course Outline

MS Excel 2016 - Master

This Master course is aimed at learners who have a good sound knowledge of Excel and can use basic functions such as IF and OR, NOT, countif and countblank.

This course covers topics to enable data to be summarised and format data depending on criteria. It also covers some analytical functions.  It also covers how to record repetitive tasks.

By the end of the course you will be able to use financial functions such as PMT, FV and Rate and also Match and Index functions.  You will learn how to use conditional formatting to high trends and exceptions with a click of the mouse. You will learn how to work with pivot tables and charts, create and play back macros. Lastly you will learn how to use some analytical functions such as goal seek, solver and scenarios as well as use one and two input data tables.

Estimated Course duration is 3 - 4 hours.  Each lesson take on average 3-5 minutes to complete.

Go to the Learning Tips to view the learning objectives for this course.

Course Details

Financial Functions

PMT
FV
RATE

Lookup Functions

Combining MATCH with INDEX
Combining LOOKUP with MATCH
Lookup Functions with Named Ranges

Logical Functions

IFERROR

Conditional Formatting

Creating Rules
Creating Rules using Formulas

Pivot Tables & Pivot Charts

Working with Pivot Tables
Re-Arranging Pivot Tables
Slicers
Formatting Pivot Tables
Pivot Charts
Using GETPIVOTDATA with Pivot Tables

Documenting and Auditing

Formula Errors
Evaluate Formulas
Using the Watch Window
Comments in Cells and Workbooks
Validating Cell Entries
Protecting Data
Workbook Collaboration

Custom Sorts

Sort List by Value
Sort List by Colour

Advanced Features

Converting Text to Columns

Macros

Understanding Macros
Macros Security Levels
Recording a Macro
Running a Macro
Assigning Macros to the QAT

Analytical Options

The Analysis Toolpak
Goal Seek
Solver
Scenarios

Data Tables

One Input Variable
Two Input Variable
Course Highlights
Master 3-4hrs
For more than 50 licences enquire here.