Watch Intro Video

Course Outline

By combining Excels Advanced Tools of Power Query, Power Pivot and Macros (VBA), this course will expose you to the time saving world of Excel Automation.

  • 1

    Section 1 - Automation Introduction

    • Introduction to the Automation Course

  • 2

    Section 2 - Source-to-Report

    • 01 - Source-to-Report Example

  • 3

    Section 3 - Source-to-report - Recorded Macro

    • 01-Recording a Macro - Intro

    • 02-Recording a Macro - Rows

    • 03-Recording a Macro - Columns

    • 04-Recording a Macro - Testing & Correcting

    • 05-Recording a Macro - Using it

  • 4

    Section 4 - Source-to-Report - Power Query

    • 01-Power Query - Intro

    • 02-Power Query - Rows

    • 03-Power Query - Columns

    • 04-Power Query - Testing, Correcting & Using

  • 5

    Section 5 - Power Query Basics

    • 01-Installing Power Query - For Excel 2010 & 2013

    • 02 - The differences between Excel Versions

    • 03-Different Data Sources

    • 04-Diff between Load, Load To and Transform

    • 05-Transform Basics

    • 06-Editing a Query

    • 07-Refreshing Data

    • 08-Modifying the Loaded Table

  • 6

    Section 6 - The Transform Toolset

    • 01-Transform Intro

    • 02-Rows and Columns

    • 03-Fill

    • 04-Formatting

    • 05-Replace Values

    • 06-Split 1

    • 07-Split 2

    • 08-Filter

    • 09-Column from Example 1

    • 10-Column from Example 2

    • 11-Column from Example 3

    • 12-Transform Recap

    • 13-Troubleshooting - Content Change

    • 14-Troubleshooting - Structure Change

    • 15-Transform Extension Challenge

    • 16-Transform Extension Solution

  • 7

    Section 7 - Different Data Sources

    • 01 - From Text CSV - Understanding Regional Settings

    • 02 - From Text CSV - with the same Locale Regional Settings

    • 03 - From Text CSV - with different Locale Regional Settings

    • 04 - From Text CSV - with Customised Regional Settings

    • 05-From Folder - Intro

    • 06-From Folder - The Sample File

    • 07-From Folder - File Filters

    • 08-From Folder - Recap Example

    • 09-From Folder - Using From File Code

    • 10-From an Access DB - Basics

    • 11-From an Access DB - that is Password Protected

    • 12-From Table-Range - Basics

    • 13-From Table -Range - Disguised Table

    • 14-From Table-Range - Transposing Data

  • 8

    Section 8 - Matching Data

    • 01-On Rows - Merge

    • 02-On Columns - Append

  • 9

    Section 9 - Power Pivot

    • 01-Adding the Power Pivot Ribbon

    • 02-Loading to the Data Model

    • 03-Std Pivot - using Index-Match

    • 04-Power Pivot - using the Data Model

    • 05-Power Pivot v Std Pivot - Duel 1

    • 06-Power Pivot v Std Pivot - Duel 2

    • 07-Power Pivot v Std Pivot - Duel 3

    • 08-Power Pivot - Summary

  • 10

    Section 10 - Integrating with Word and Point

    • 01-Integration Intro

    • 02-Linking Charts

    • 03-Linking Tables

    • 04-Formula driven tables

    • 05-Linking Charts & Tables Summary

    • 06-Updating Linked Data

  • 11

    Section 11 - VBA

    • 01-VBA Intro

    • 02-VBA Editor Window

    • 03-VBA Objects

    • 04-VBA Worksheets

    • 05-VBA Ranges

    • 06-VBA Variables

    • 07-VBA Properties

    • 08-VBA Methods

    • 09-VBA Last Row and Column

    • 10-VBA For Loop

    • 11-VBA If Statement

    • 12-Code from Scratch Example

    • 13-Code from Web Example

    • 14-Creating a My Macros Ribbon