Microsoft Excel Expert

About the Course

Designed for
Aimed at advanced users who have an excellent knowledge of Microsoft Excel, this course provides candidates with practical experience of the topics listed in the course outline below. On completion, candidates should be equipped with the necessary skills to create Pivot Tables, produce complex nested functions, and record and edit macros.

Teaching Approach
The teaching approach contains a mix of lectures, practical and one-to-one work and varies according to the people attending. For the most part, candidates are able to work at a pace which best suits them, and emphasis is placed upon quality of learning, rather than quantity.

Pre-requirements
Those attending should have a very good understanding of standard Microsoft Excel functions, be able to record a simple macro and be competent Microsoft Windows users.

Course Material:
A large part of each course is spent with a set of prepared exercises and projects so that those attending gain firm foundations through their own practical experience. Professionally presented course notes are provided and these may be retained for reference afterwards.

Duration:
This training program is one day in duration.

Course Outline

Advanced Analysis of Data with Pivot Tables
Creating a calculated field
Creating a calculated item
Using Pivot Table results in a worksheet formula
Linking slicers to multiple Pivot Tables

Advanced Nested Functions
Nesting multiple IF statements
Nesting AND and OR with IF statements
Nesting MATCH with VLOOKUP functions
Nesting INDEX and MATCH functions
Nested text functions

Array Functions
Working with CSE array functions (Ctrl + Shift + Enter)
Returning array solutions from an array

Macros and Macro Editing
Recording a simple macro (recap only – see pre-requirements above)
Editing a simple macro
Creating an input message box
Displaying a message box
Working with cells
Working with columns and rows
Working with sheets
Working with workbooks
Creating a Pivot Table with a macro
Creating functions with macros
Error handling
Where to from here?