MS Excel Automation

"I would like to thank Mark for his support over the training sessions, the team are totally pumped at what they learnt and are already putting it into practice which is saving them hours if not days of time."
Patrick - Kerry Group

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

    Automation Introduction

    • Introduction to the Automation Course

  • 2

    Section 1 - Source to Report

    • 01 - The Source-to-Report Concept

    • 02 - The Manual Method

    • 03 - Partial Automation

    • 04 - Full Automation Example

  • 3

    Section 2 - Power Query

    • 01 - Where to Find Power Query

    • 02 - Different Data Sources

    • 03 - Difference between Transform, Load & Load To

    • 04 - Transform Overview

    • 05 - Editing a Query

    • 06 - Refreshing Data

    • 07 - Modifying Loaded Table

  • 4

    Section 4 - The Transform Tool Kit

    • 01 - Transform Introduction

    • 02 - Header Row

    • 03 - Data Types

    • 04 - Columns & Rows

    • 05 - Filters

    • 06 - Find & Replace

    • 07 - Text to Columns

    • 08 - Flash Fill

    • 09 - Fill Blanks

  • 5

    Section 5 - Different Data Sources

    • 01 - Introduction to Get Data from Folder

    • 02 - Get Data from Folder - Key Differences

    • 03 - Get Data from Folder - NB Points

    • 04 - Get Data from Folder - Worked example

    • 05 - Get Data from Table / Range

    • 06 - Get Data from Access Data Base

  • 6

    Section 6 - Power Query Tables

    • 01 - How they Work

    • 02 - Structured Reference Formula recap

    • Using Lookup Tables to Normalise the Data

  • 7

    Section 7 - Matching Data

    • 01- Append - Matching Data on Columns

    • 02 - Merge -Matching Data on Rows

  • 8

    Section 8 - Power Pivot

    • 01 - Power Pivot Introduction

    • 02 - Building Relationships

    • 03 - Pivot Tables from Power Pivot

    • 04 - Calculated Fields - Introduction to DAX

  • 9

    Section 9 - Integrating with Word and PowerPoint

    • 01 - Key Points

    • 02 - Worked Example - Word

    • 03 - Worked Example - PowerPoint

  • 10

    Section 10 - Intoduction to VBA

    • 01 - Where Power Query Falls Apart

    • 02 - VBA Core

    • 03 - Manipulating Code Snipets

    • 04 -Building a My Macros Tool Bar