## Demystify some of the most complex business relevant areas of Microsoft Excel.

With 160 videos and loads of practice exercises you will be able to use the most hard to grasp Excel functions in no time.

Our CPD UK certified online course covers what-you-need-to-know-in-business rather than what-Excel-can-do.

## Highlights

This course covers:

• Using advanced graphs and presentation techniques to maximise impact
• How superpower functions can solve complex problems
• Using PivotTables and PowerPivots to turn raw data into clear information that supports key decisions

## Preview

Course Content

Syllabus Explorer

Video

User Dashboard

The Test

Course Certificate

## Syllabus

### Section 1: Efficiency and Risk

directright

#### Unit 1.1 - Templates

Get a head start with templates. Set up an Excel template to save you an hour each time you create a new workbook. Create new default workbook and worksheet templates, learn how modular templates can make your spreadsheets more consistent and much quicker to set up. Your Excel, your way.

directright

#### Unit 1.2 - Increasing Efficiency and Reducing Risk

Make spreadsheets more efficient and less risky by identifying how to build in checks and controls from the outset. Identify key techniques that reduce risk and increase automation and efficiency. Introduce standards to help your team and documentation and review tools.

directright

#### Unit 1.3 - Data Validation

Understand extended uses of Data Validation, work with validation formulae. Understand lists and lists that depend on other list selections as well as other methods of tracking down invalid entries

directright

#### Unit 2.1 - Functions with Superpowers

Finding the right functions: with hidden powers – MOD() for patterns, OFFSET() for simple choices, INDIRECT() to manipulate formulae and identify how references to ranges of cells adapt automatically for new data

directright

#### Unit 2.2 - Array Formulae

One formula, one million calculations: SUMPRODUCT() - all the gain of an array formula with less of the pain

directright

#### Unit 2.3 - Tables

Why Tables are so much more than just a new format. Make your spreadsheets more automatic with a single command. Understand table formulae and instant readability

directright

#### Unit 2.4 - Advanced Range Names

Use the same name on different sheets using the Name Manager. Understand how to insert names in formulae – the efficient inclusion of Names, the use of intersections. Identify how to allocate a formula directly to a Range Name and why you might need to

directright

#### Unit 2.5 - What If Analysis

Use Excel to help you make better decisions. Use Goal Seek to find where you need to start to get where you need to go. Use an Excel Data Table to calculate dozens of possible outcomes. Create and manage alternative scenarios. Make more profit or incur less expense by using Excel Solver to identify the best solution

directright

#### Unit 2.6 - Problem Solving

Calculations that make decisions – understanding True and False. What Boolean Logic is and why it’s useful in practice, combining logic and arrays to solve complex problems

### Section 3: Excel Interactivity

directright

#### Unit 3.1 - Form Controls

Use Form controls to make life easier for users. Use a Spin Button to choose a value easily and an Option Button to choose with a single click. Also covers choosing from lists

directright

#### Unit 3.2 - Visual Basic and Macros

Create macros by writing Visual Basic code. A macro that performs one or more actions on selected cells. Write your own Excel functions with VB code. Understanding volatile functions. Trigger a macro when a particular cell is changed. Handling errors elegantly. Exchanging information with VB code. Displaying a Message box. Asking for user input using an Input box. Create an Excel form with a List box containing values from a range of cells. Sample VB projects: an automatic index to sheets, printing selected ranges. Avoiding macros when they’re not really necessary

directright

#### Unit 4.1 - Conditional Formatting

Beyond simple Conditional Formats. Basing conditions on a formula and deciding whether a batsman is out or not, choose currency symbols for a whole sheet by changing a single cell. Getting your rules in the right order and knowing when to stop. Graphical Conditional Formats – the detailed options. Using invisibility to your advantage.

directright

#### Unit 4.2 - Charts that Inspire

What makes a good chart – is it really a 6.5 cucumbers? Simple steps to make your charts clearer. Why small can be better than large. Are pie charts evil? Why 3D charts can be 50% worse. Mixed chart types, trendlines and projections. Advanced chart techniques: break-even lines and waterfall charts. Pictures in chart columns

directright

#### Unit 4.3 - Sparklines

In-cell charts – showing 12 times as much information in the same amount of space. Careful with that Axis. The different types of Sparkline: lines, columns and win loss. Sparklines based on a dynamic data range

directright

#### Unit 4.4 - Graphics Tricks and Techniques

Further graphics tips and techniques. Taking dynamic pictures with the Excel camera. Use the Excel camera to combine areas from multiple sheets on the same sheet of paper. Formatting Excel Camera pictures. Power View – using the Excel 2013 data visualisation add-in including plotting values on maps and ‘playing’ bubble charts. Using 3D Map to create videos of animated visualisations of results by location

### Section 5: Turning Data into Decisions

directright

#### Unit 5.1 - Working with External Data

Identify the Get External Tools, understand relational databases in ten minutes to liberate your data, and create relationships within Excel using the Excel 2013 Data Model

directright

#### Unit 5.2 - Advanced Uses of PivotTables

Use advanced PivotTable techniques to do more with your data, such as Calculated Fields and Calculated items. Use PivotTables as the calculation engine behind management reports, GETPIVOTDATA() and CUBE formulae to create flexible reports, as well as Excel 2016 slicers & timelines. Make your PivotTables more interactive, work with PivotCharts, and build interactive dashboards using PivotTables and Slicers.

directright

#### Unit 5.3 - PowerPivot

Understand the Pivot add-in and PowerPivot data tools. Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX). Use DAX in table columns.,DAX to create new measures and calculated fields. Understanding advanced DAX expressions – functions that combine calculations and database technique, and the Time Intelligence DAX functions – why you need a table containing all possible dates

directright

#### Unit 5.4 - Reporting with PowerPivot

Creating powerful reports with PowerPivot: Hierarchies, Perspectives and Sets, sdding Key Performance Indicators (KPIs) to a Power Pivot report. Creating CUBE formulae to report on your data in just the way you want to. Use all these techniques to turn millions of rows of data into a dashboard that supports better decisions

### Section 6: Course Plenary

directright

#### Unit 6.1 - Course Plenary

Revise what you have learned.

### Section 1: Design and Risk

directright

#### Unit 1.1 - Templates

Get a head start with templates ● Set up an Excel template that will save you an hour each time you create a new workbook ● Create new default workbook and worksheet templates ● Learn how modular templates can make your spreadsheets more consistent and much quicker to set up ● Your Excel, your way: customise the Excel interface to put the tools that you need at your fingertips

directright

#### Unit 1.2 - Efficiency and Risk

Make spreadsheets more efficient and less risky ● How to build in checks and controls from the outset ● Key techniques that reduce risk and increase automation and efficiency ● Introduce standards to help your team ● Documentation and review tools

directright

#### Unit 1.3 - Data Validation

Extended uses of Data Validation ● Working with validation formulae ● Lists and lists that depend on other list selections ● Other methods of tracking down invalid entries

directright

#### Unit 2.1 - Functions and Super Powers

Functions with super powers ● Finding the right functions and how they work ● Functions with hidden powers – MOD() for patterns, OFFSET() for simple choices, INDIRECT() to manipulate formulae ● Making references to ranges of cells adapt automatically for new data

directright

#### Unit 2.2 - Array Formulae

Array formulae ● One formula, one million calculations ● SUMPRODUCT() - all the gain of an array formula with less of the pain

directright

#### Unit 2.3 - Tables

Tables – structure comes to Excel ● Why Tables are so much more than just a new format ● Make your spreadsheets more automatic with a single command ● Table formulae – instant readability ● Data tools in Tables ● It’s not all good

directright

#### Unit 2.4 - Advanced Range Names

Advanced Range Names ● Use the same name on different sheets ● Using the Name Manager ● Names in formulae – efficient inclusion of Names, the use of intersections ● Formulae in Names – how to allocate a formula directly to a Range Name and why you might need to

directright

#### Unit 2.5 - What If?

Using Excel to help you make decisions ● Use Goal Seek to find where you need to start to get where you need to go ● Use an Excel Data Table to calculate dozens of possible outcomes ● Create and manage alternative scenarios ● Make more profit or incur less expense by using Excel Solver to identify the best solution

directright

#### Unit 2.6 - Problem Solving

Problem solving ● Calculations that make decisions – understanding TRUE ad FALSE ● What Boolean Logic is and why it’s useful in practice ● combining logic and arrays to solve complex problems ● Practical examples

### Section 3: Excel Interactivity

directright

#### Unit 3.1 - Form Controls

Use Form controls to make life easier for users ● Use a Spin Button to choose a value easily ● Use an Option Button to choose with a single click ● Choosing from lists

directright

#### Unit 3.2 - Visual Basic and Macros

Create macros by writing Visual Basic code ● A macro that performs one or more actions on selected cells ● Write your own Excel functions with VB code ● Understanding volatile functions ● Trigger a macro when a particular cell is changed ● Handling errors elegantly

directright

#### Unit 4.1 - Practical Interactivity with VBA

Exchanging information with VB code ● Displaying a Message box ● Asking for user input using an Input box ● Create an Excel form with a List box containing values from a range of cells ● Sample VB projects: an automatic index to sheets, printing selected ranges ● Avoiding macros when they’re not really necessary

directright

#### Unit 4.2 - Conditional Formatting

Conditional Formatting – beyond simple Conditional Formats ● Basing conditions on a formula and deciding whether a batsman is out or not ● Choose currency symbols for a whole sheet by changing a single cell ● Getting your rules in the right order and knowing when to stop ● Graphical Conditional Formats – the detailed options ● Using invisibility to your advantage

directright

#### Unit 4.3 - Charts that Inspire

Create charts to inspire ● What makes a good chart – is it really a 6.5 cucumbers? ● Simple steps to make your charts clearer ● Why small can be better than large ● Are pie charts evil? ● Why 3D charts can be 50% worse ● Mixed chart types, trendlines and projections ● Advanced chart techniques: break-even lines and waterfall charts ● Pictures in chart columns

directright

#### Unit 4.4 - Sparklines

In-cell charts – showing 12 times as much information in the same amount of space ● Careful with that Axis ● The different types of Sparkline: lines, columns and win loss ● Sparklines based on a dynamic data range

directright

#### Unit 4.5 - Graphics Tricks and Techniques

Further graphics tips and techniques ● Taking dynamic pictures with the Excel camera ● Use the Excel camera to combine areas from multiple sheets on the same sheet of paper ● Formatting Excel Camera pictures ● Power View – using the Excel 2013 data visualisation add-in including plotting values on maps and ‘playing’ bubble charts

### Section 5: Turning Data into Decisions

directright

#### Unit 5.1 - Working with External Data

Getting at your data using the Get External Data tools ● Understand relational databases in 10 minutes and liberate your data ● Excel 2013 Data Model create relationships within Excel

directright

#### Unit 5.2 - Advanced Uses of PivotTables

Use advanced PivotTable techniques to do more with your data ● Calculated Fields and Calculated items ● Using PivotTables as the calculation engine behind management reports ● Using GETPIVOTDATA() and CUBE formulae to create flexible reports ● Excel 2010 and 2013 Slicers and Timelines - make your PivotTables more interactive ● Working with Pivot Charts ● Building and interactive dashboard using PivotTables and Slicers

directright

#### Unit 5.3 - PowerPivot

The Excel 2010 and 2013 Power Pivot add-in ● PowerPivot data tools ● Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX) ● DAX in table columns ● DAX to create new measures and calculated fields ● Understanding advanced DAX expressions – functions that combine calculations and database techniques ● the Time Intelligence DAX functions – why you need a table containing all possible dates

directright

#### Unit 5.4 - Reporting with PowerPivot

Creating powerful reports with PowerPivot ● Hierarchies, Perspectives and Sets ● Adding Key Performance Indicators (KPIs) to a Power Pivot report ● Creating CUBE formulae to report on your data in just the way you want to ● Use all these techniques to turn millions of rows of data into a dashboard that supports better decisions

### Section 1: Design and Risk

directright

#### Unit 1.1 - Templates

Get a head start with templates ● Set up an Excel template that will save you an hour each time you create a new workbook ● Create new default workbook and worksheet templates ● Learn how modular templates can make your spreadsheets more consistent and much quicker to set up ● Your Excel, your way: customise the Excel interface to put the tools that you need at your fingertips

directright

#### Unit 1.2 - Efficiency and Risk

Make spreadsheets more efficient and less risky ● How to build in checks and controls from the outset ● Key techniques that reduce risk and increase automation and efficiency ● Introduce standards to help your team ● Documentation and review tools

directright

#### Unit 1.3 - Data Validation

Extended uses of Data Validation ● Working with validation formulae ● Lists and lists that depend on other list selections ● Other methods of tracking down invalid entries

directright

#### Unit 2.1 - Functions with Super Powers

Functions with super powers ● Finding the right functions and how they work ● Functions with hidden powers – MOD() for patterns, OFFSET() for simple choices, INDIRECT() to manipulate formulae ● Making references to ranges of cells adapt automatically for new data

directright

#### Unit 2.2 - Array Formulae

Array formulae ● One formula, one million calculations ● SUMPRODUCT() - all the gain of an array formula with less of the pain

directright

#### Unit 2.3 - Tables

Tables – structure comes to Excel ● Why Tables are so much more than just a new format ● Make your spreadsheets more automatic with a single command ● Table formulae – instant readability ● Data tools in Tables ● It’s not all good

directright

#### Unit 2.4 - Advanced Range Names

Advanced Range Names ● Use the same name on different sheets ● Using the Name Manager ● Names in formulae – efficient inclusion of Names, the use of intersections ● Formulae in Names – how to allocate a formula directly to a Range Name and why you might need to

directright

#### Unit 2.5 - What If Analysis

Using Excel to help you make decisions ● Use Goal Seek to find where you need to start to get where you need to go ● Use an Excel Data Table to calculate dozens of possible outcomes ● Create and manage alternative scenarios ● Make more profit or incur less expense by using Excel Solver to identify the best solution

directright

#### Unit 2.6 - Problem Solving

Problem solving ● Calculations that make decisions – understanding TRUE ad FALSE ● What Boolean Logic is and why it’s useful in practice ● combining logic and arrays to solve complex problems ● Practical examples

### Section 3: Excel Interactivity

directright

#### Unit 3.1 - Form Controls

Use Form controls to make life easier for users ● Use a Spin Button to choose a value easily ● Use an Option Button to choose with a single click ● Choosing from lists

directright

#### Unit 3.2 - Visual Basic and Macros

Create macros by writing Visual Basic code ● A macro that performs one or more actions on selected cells ● Write your own Excel functions with VB code ● Understanding volatile functions ● Trigger a macro when a particular cell is changed ● Handling errors elegantly

directright

#### Unit 4.1 - Practical Interactivity with VBA

Exchanging information with VB code ● Displaying a Message box ● Asking for user input using an Input box ● Create an Excel form with a List box containing values from a range of cells ● Sample VB projects: an automatic index to sheets, printing selected ranges ● Avoiding macros when they’re not really necessary

directright

#### Unit 4.2 - Conditional Formatting

Conditional Formatting – beyond simple Conditional Formats ● Basing conditions on a formula and deciding whether a batsman is out or not ● Choose currency symbols for a whole sheet by changing a single cell ● Getting your rules in the right order and knowing when to stop ● Graphical Conditional Formats – the detailed options ● Using invisibility to your advantage

directright

#### Unit 4.3 - Charts that Inspire

Create charts to inspire ● What makes a good chart – is it really a 6.5 cucumbers? ● Simple steps to make your charts clearer ● Why small can be better than large ● Are pie charts evil? ● Why 3D charts can be 50% worse ● Mixed chart types, trendlines and projections ● Advanced chart techniques: break-even lines and waterfall charts ● Pictures in chart columns

directright

#### Unit 4.4 - Sparklines

In-cell charts – showing 12 times as much information in the same amount of space ● Careful with that Axis ● The different types of Sparkline: lines, columns and win loss ● Sparklines based on a dynamic data range

directright

#### Unit 4.5 - Graphics Tricks and Techniques

Further graphics tips and techniques ● Taking dynamic pictures with the Excel camera ● Use the Excel camera to combine areas from multiple sheets on the same sheet of paper ● Formatting Excel Camera pictures ● Power View – using the Excel 2013 data visualisation add-in including plotting values on maps and ‘playing’ bubble charts

### Section 5: Turning Data into Decisions

directright

#### Unit 5.1 - Working with External Data

Getting at your data using the Get External Data tools ● Understand relational databases in 10 minutes and liberate your data ● Excel 2013 Data Model create relationships within Excel

directright

#### Unit 5.2 - Advanced Uses of PivotTables

Use advanced PivotTable techniques to do more with your data ● Calculated Fields and Calculated items ● Using PivotTables as the calculation engine behind management reports ● Using GETPIVOTDATA() and CUBE formulae to create flexible reports ● Excel 2010 and 2013 Slicers and Timelines - make your PivotTables more interactive ● Working with Pivot Charts ● Building and interactive dashboard using PivotTables and Slicers

directright

#### Unit 5.3 - PowerPivot

The Excel 2010 and 2013 Power Pivot add-in ● PowerPivot data tools ● Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX) ● DAX in table columns ● DAX to create new measures and calculated fields ● Understanding advanced DAX expressions – functions that combine calculations and database techniques ● the Time Intelligence DAX functions – why you need a table containing all possible dates

directright

#### Unit 5.4 - Reporting with PowerPivot

Creating powerful reports with PowerPivot ● Hierarchies, Perspectives and Sets ● Adding Key Performance Indicators (KPIs) to a Power Pivot report ● Creating CUBE formulae to report on your data in just the way you want to ● Use all these techniques to turn millions of rows of data into a dashboard that supports better decisions

## Pricing

Pricing is for 12 months access.

*The AUD price shown above is an estimate. Actual charges are based on the current exchange rate from GBP

## Requirements

Software:

• Required: Microsoft Excel
• Suggested: None

Completion Time: 27 hours (average)

Simon Hurst

Simon Hurst is a Chartered Accountant and has been involved in computer software for 26 years during which he has provided a wide range of training services to professionals of all levels. His expertise lies in all the main Microsoft applications: Access, Word, PowerPoint and Excel, about which he speaks frequently at conferences. He is also a prolific writer and published Excel author, contributing Excel articles to many websites and publications.

Simon has been running hands-on Excel training courses for over 20 years, dealing with all aspects of Excel from basic competence to advanced data analysis. He is able to bring these years of practical experience to our Advanced Excel course, ensuring that the course explains advanced concepts clearly and concisely, and with real, practical applications and even the occasional joke.