What you'll learn

This intensive hands-on one-day training course has been designed to provide delegates with a very advanced set of tools and concepts for Excel 2007, 2010, 2013 or 2016 in order to further increase their knowledge and productivity.

Requirements

Delegates should have a good working knowledge and understanding of the concepts covered in the Introduction, Intermediate and Advance courses.

Description

MODULE 1: Advanced Conditional Formatting

Learning outcomes: Looking at the more advanced Conditional Formatting features and how to manage them effectively.

Topics covered:

Editing Standard Formatting Rules

Using Formulas in Conditional Formatting

MODULE 2: Using Form Controls and Templates

Learning outcomes: Introducing Form controls and using them to manage user interaction to help ensure they enter/search for information both accurately and effectively.

Topics covered:

Using Form Controls

Creating Templates

MODULE 3: Advanced Lookup Functions

Learning outcomes: An in-depth look into Excel's specialist lookup functions and the concept of arrays.

Topics covered:

Using the Index Function

Using the Match Function

Using Index and Match Together

Using Index and Match with Array Formula

Using the Indirect Function

MODULE 4: Advanced Functions in Excel

Learning outcomes: Effectively using Error, Logical and Rounding functions along with array formulas.

Topics covered:

Using Error Functions

Using Logical Functions

Using Array Formulas

Using Rounding Functions

MODULE 5: Advanced Pivot Table Tools

Learning outcomes: How to import Pivot Table data from external source along with adding custom fields and formulas.

Topics covered:

Importing Pivot Table Data

Calculated Fields in Pivot Tables

Showing Pivot Data as a Percentage

Creating Custom Pivot Tables Styles

MODULE 6: Introduction to User Defined Functions Using VBA

Learning outcomes: Using VBA to create specialist procedure and to define your own Excel functions.

Topics covered:

Creating a User Defined Function

Creating an Interactive Procedure

Create and Share a Function Add-in

Who this course is for:

Users with a solid understanding of, and experience with, Microsoft Excel 2007, 2010, 2013 or 2016 who want to increase their ability in a short space of time.