Power Query and Multiple Files

Price: $35 incl GST

Duration: 1 hour

Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.

Building on the skills covered in the Introduction session, we will start working with multiple files. For example you may have 12 separate CSV files in a folder. All with the same layout, one for each month of the year. Power Query can import all 12 files as if they were a single file and create a table for the whole year

This session covers
• importing multiple CSV files from a folder
• a technique to capture the file name in a field (column)
• importing multiple Excel files
• merging data from multiple tables
• Extracting header information into a column from multiple CSV files

As always, I will share other tips and tricks along the way.

 

Date: April 19, 2018
Time: 1:00 PM AEST
Event: Webinar - Excel 2016 Power Query and Multiple Files
Topic: Power Query and Multiple Files
Public: Public
Registration: Click here to register.

Excel 2016 Power Query Introduction

 Price: $35 incl GST

Duration: 1 hour

Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.

Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.

This session covers

  • fixing dates so that Excel can recognise them
  • formatting columns as text – retaining leading zeroes in CSV files
  • deleting unwanted rows and columns from your data
  • removing leading and trailing spaces
  • populating blank values with zeroes
  • populating blanks with entries from above
  • correcting trailing minus signs
  • unpivot a report – converting a report layout into a table layout
  • converting a MYOB report into a data table
  • capture header information in a column

As always, I will share other tips and tricks along the way.

 

Date: April 16, 2018
Time: 1:00 PM AEST
Event: Webinar - Excel 2016 Power Query Introduction
Topic: Introduction to Power Query
Public: Public
Registration: Click here to register.

Webinar – FREE – Macros 1  – Recording Tips, Tricks and Traps

Price: No Charge

Duration: 1 hour

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.

Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.

This is the first in a series of webinars dedicated to macros. Future sessions will expand on the techniques taught in this session.

All sessions include bonus macros that you can use straight away to save time and effort.

Date: April 13, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Macros 1- Recording Tips, Tricks and Traps [FULL]
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – FREE – Macros 1  – Recording Tips, Tricks and Traps

Price: No Charge

Duration: 1 hour

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.

Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.

This is the first in a series of webinars dedicated to macros. Future sessions will expand on the techniques taught in this session.

All sessions include bonus macros that you can use straight away to save time and effort.

Date: April 11, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Macros 1- Recording Tips, Tricks and Traps [FULL]
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – FREE – Macros 1  – Recording Tips, Tricks and Traps

Price: No Charge

Duration: 1 hour

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.

Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.

This is the first in a series of webinars dedicated to macros. Future sessions will expand on the techniques taught in this session.

All sessions include bonus macros that you can use straight away to save time and effort.

Date: April 12, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Macros 1- Recording Tips, Tricks and Traps [FULL]
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Macros 2 – Recorded To Remarkable (1.5 hours)

Price: $35 incl GST

Recorded macros have many limitations. Almost all of those limitations can be removed by editing the recorded macros and adding programming code. This creates macros that are more useful, flexible and easier to maintain.

This webinar focuses on the most common VBA code added to recorded macros. Basic error handling is included. A worked example is used to demonstrate all of the techniques.

(This session assumes you have completed the Macros 1 session or that you are comfortable with recording macros)

Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel. This session will help you take Excel to the next level.

This is the second in a series of webinars dedicated to macros. Future sessions will expand on the techniques taught in this and the first session.

All sessions include a manual and bonus macros that you can use straight away to save time and effort. This session includes two bonus User Defined Functions that work just like Excel’s built-in functions.

This third webinar in my macro series will demonstrate and explain how to interact with the user and how to debug (remove errors from) your VBA code.

Date: April 18, 2018
Time: 1:00 PM AEST
Event: Webinar - Macros 2 – Recorded To Remarkable (1.5 hours)
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Macros 3 – Interaction and Debugging Techniques (1.5 hours)

Price: $35 incl GST

Recorded macros can’t interact with the user. Macros that interact with the user are more user-friendly and flexible.

Learn the techniques that allow you to use one-way and two-way communication with the user whilst your macro is running.

Debugging involves removing errors from your code. As you build more complex macros you will need to improve your debugging skills.

Discover the built-in features in Excel that make this important process easier.

You need to have done the Macros 2 webinar or at least be familiar with editing recorded macros to get the most from this session.

Date: April 20, 2018
Time: 1:00 PM AEST
Event: Webinar - Macros 3 – Interaction and Debugging Techniques (1.5 hours)
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Macros 8 – VBA Variables

Price: $35 incl GST

Duration: 1 hour and 15 minutes

This session covers variables in depth and expands on previous sessions.

Learn how to make the most of variables in your VBA code. There are so many advantages to using variables, but you need to get a good understanding of them to get the most out of them.

This session covers

  • variable types, as well as when, how and why to use them
  • shortcuts that can reduce the typing involved with variables
  • useful examples to demonstrate variable usage
  • building user messages
  • selecting separate sheets based on user input
  • combining data sheets based on a criteria

 

Date: April 27, 2018
Time: 1:00 PM AEST
Event: Webinar - Macros 8 - VBA Variables
Topic: Macros
Public: Public
Registration: Click here to register.

Macros 4 – Automating Macros with Events

Price: $35 incl GST

Duration: 1 hour

You can automate your macros by taking advantage of Excel’s built-in event monitoring system. Excel watches what you do and some of your actions can trigger what is called an event.

When an event is triggered it can run macro code. Things like opening or closing the file can trigger an event. In a sheet changing an entry will trigger an event.

For example you can choose what sheets to display when a file is opened and build in robust input validation checks to your sheets.

These techniques can improve the user experience and ensure macros are run when you need them run.

Date: April 23, 2018
Time: 1:00 PM AEST
Event: Webinar - Macros 4 – Automating Macros with Events
Topic: Macros
Public: Public
Registration: Click here to register.

Chapter 9 – Range Names

Price: $25 incl GST or $15 if you own the book

Duration: 1.0 hour

The thirteenth in my series of webinars based on my book. The book is the manual.

In this session we will start Chapter 9 which covers range names. We are up to page 209 in the printed book.

Range names can improve formula creation and maintenance as well as making your models more structured. The session will cover the basics of creating and using range names.

If you own the book you can use the answer to a question as the voucher code to attend for $15.

Date: April 30, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 9 - Range Names
Topic: Book Chapter 9
Public: Public
Registration: Click here to register.

Advanced Budgeting Techniques

 Price: $35 incl GST

Duration: 1 hour

If you work on a calendar year it’s nearly that time of the year when your thoughts turn to budgets.

If you have a Jul-Jun financial year you might want to get a head start on next year’s budget/forecast.

Excel is the main application used to create budgets for small to medium organisations. Even large organisations use it as part of their budget process.

You can create flexible budgeting models in Excel by combining its many built-in features with its various functions.This webinar with demonstrate techniques to

  • choose monthly allocation methods on a line by line basis
  • select different inflation rates to apply on a line by line basis
  • choose between percentage-based allocations or per unit allocations
  • turn inflation rates off and on at the click of a Check Box
  • use drop down lists for selecting options
  • create a flexible validation system

These techniques could also be applied to forecasting and financial modelling.

Date: March 15, 2018
Time: 1:00 PM AEDST
Event: Webinar - Advanced Budgeting Techniques
Topic: Advanced Budget Techniques
Public: Public
Registration: Click here to register.

Excel Budget Template (1.5 hrs)

Price: $45.00 incl GST

Duration: 1.5 hours

In this webinar I will share a budget structure I have used successfully to create a budget that allows a couple of levels. This structure has some flexibility and incorporates a number of advanced Excel features that work well together. The budget template is scalable and can be applied to numerous scenarios.

If you have a Jul-Jun financial year you might want to get a head start on next year’s budget/forecast.

The Budget Template session outcomes will include
• discover a scalable, flexible Excel budget structure
• learn formulas that can easily consolidate budgets from multiple sheets, making uploads into accounting systems easier
• find out about a little-used function that can make allocations easier to create
• see how to use tables to add structure and flexibility to your Excel files
• a range name trick that involves formatted tables
• adding validations to your budgets

As always, you will also pick up a few other tips and tricks along the way.

This is a longer than normal session (1.5 hours) and covers many advanced topics that can simplify your budget process.

Date: March 20, 2018
Time: 1.00 PM (AEDST)
Event: Webinar - Excel Budget Template
Topic: Budget Template
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Yourself 2017

This is my annual smorgasbord session. I revisit four of my magazine articles from the year and add some extra content.

I will revisit four of my magazine articles from 2017 and add some extra content. We cover a few functions (two new ones); an automated filtering solution using a macro and how to calculate moving annual totals. The Excel file includes the examples from ten of my articles from 2017.

This session covers four of my articles from 2017.
• XNPV and XIRR functions
• Moving annual totals
• Advanced Filter automation
• TEXTJOIN and CONCAT functions (new to some versions of Excel 2016)

Content applies to Excel 2007 and later versions, except the two new Excel 2016 functions.

As always I will share some other tips along the way.

Date: March 13, 2018
Time: 1:00 PM AEDT
Event: FREE Webinar - Excel Yourself 2017
Topic: Excel Yourself 2017
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Yourself 2017

This is my annual smorgasbord session. I revisit four of my magazine articles from the year and add some extra content.

I will revisit four of my magazine articles from 2017 and add some extra content. We cover a few functions (two new ones); an automated filtering solution using a macro and how to calculate moving annual totals. The Excel file includes the examples from ten of my articles from 2017.

This session covers four of my articles from 2017.
• XNPV and XIRR functions
• Moving annual totals
• Advanced Filter automation
• TEXTJOIN and CONCAT functions (new to some versions of Excel 2016)

Content applies to Excel 2007 and later versions, except the two new Excel 2016 functions.

As always I will share some other tips along the way.

Date: March 21, 2018
Time: 1:00 PM AEDT
Event: FREE Webinar - Excel Yourself 2017
Topic: Excel Yourself 2017
Public: Public
Registration: Click here to register.

Chapter 8 – Sundry Functions – cont’d

Price: $25 incl GST or $15 if you own the book

Duration: 1.0 hour

The twelfth in my series of webinars based on my book. The book is the manual.

In this session we will look at the DATE, OFFSET, CHOOSE, COUNTIF and the INDIRECT functions – we may also touch on array formulas.

We are up to page 190 in the printed book.

If you own the book you can use the answer to a question as the voucher code to attend for $15.

Date: March 29, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 8 - continued
Topic: Book Chapter 8
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Designs Guidelines

This webinar has lots of tips and suggestions to help you create better spreadsheets. These are techniques that I have used successfully on a regular basis and can recommend to other users.

The guidelines will help you use and create spreadsheets that

  • are more reliable
  • identify errors
  • are easier to use and understand
  • have more flexible formulas (can be copied to more places)
  • are more structured
  • are easy to navigate

Along the way you’ll pick up shortcuts and quick ways to do many different tasks.

Date: April 18, 2017
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Design Guidelines
Topic: Excel Design Guidelines
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Designs Guidelines

This webinar has lots of tips and suggestions to help you create better spreadsheets. These are techniques that I have used successfully on a regular basis and can recommend to other users.

The guidelines will help you use and create spreadsheets that

  • are more reliable
  • identify errors
  • are easier to use and understand
  • have more flexible formulas (can be copied to more places)
  • are more structured
  • are easy to navigate

Along the way you’ll pick up shortcuts and quick ways to do many different tasks.

Date: April 20, 2017
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Design Guidelines
Topic: Excel Design Guidelines
Public: Public
Registration: Click here to register.

Advanced Range Names

 Price: $35 incl GST

Duration: 1.5 hours

This is one topic that can add both structure and flexibility to your Excel models. These techniques can be applied to most Excel files including reporting models, budgets and financial models. Range names have many advantages. Range names can

  • make formulas faster to create and easier to read, understand and maintain
  • reduce the need for absolute and mixed references (fewer $ signs in formulas)
  • define input areas
  • allow you to easily navigate in a large workbook
  • make working with macros more reliable
  • create dynamic ranges that expand and contract as data is added or removed
  • create robust linking between files

This is a longer than normal session (1.5 hours) and covers many advanced techniques. These techniques can be applied to reporting models, dashboards, budgets and forecasts.

The session will cover

  • Naming conventions – what they are and how to use them to simplify formula creation
  • Non-formula uses for range names – create an unbreakable hyperlink
  • Linking between files – technique to reduce broken, or corrupted links
  • Advanced techniques – creating a sheet name range name and some advanced ways to refer to ranges
  • Functions – that work well with range names, especially the INDIRECT function
  • One Formula to Rule Them All – an amazing technique that can be applied to many situations – a single formula to extract Actuals, Budgets and Forecasts
  • Controls – using range names with controls

As always, I will share other tips and tricks along the way.

 

Date: April 27, 2017
Time: 1:00 PM AEST
Event: Webinar - Advanced Range Names (1.5 hours)
Topic: Range Names
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Date and Time Calculations

In my first free session of the year you can learn how to do calculations with dates and times in Excel. Discover the functions that handle different types of calculations.

This is a re-run of a session I did in 2014 – I have added a couple of short topics.

See how to avoid some of the frustrations that dates and times can cause. Topics covered include:

  • adding months to a date
  • formulas for the first and last day of the month
  • using today’s date in a formula
  • handling days of the week calculations
  • automating dates in headings
  • how to avoid the traps in time calculations
  • remove times from dates
  • generating random dates (for testing)

As always I will share some other tips along the way.

Date: February 22, 2017
Time: 1:00 PM AEDT
Event: FREE Webinar - Excel Date and Time Calculations
Topic: Excel Yourself 2016
Public: Public
Registration: Click here to register.

FULL – FREE Webinar – Excel Date and Time Calculations

In my first free session of the year you can learn how to do calculations with dates and times in Excel. Discover the functions that handle different types of calculations.

This is a re-run of a session I did in 2014 – I have added a couple of short topics.

See how to avoid some of the frustrations that dates and times can cause. Topics covered include:

  • adding months to a date
  • formulas for the first and last day of the month
  • using today’s date in a formula
  • handling days of the week calculations
  • automating dates in headings
  • how to avoid the traps in time calculations
  • remove times from dates
  • generating random dates (for testing)

As always I will share some other tips along the way.

Date: February 15, 2017
Time: 1:00 PM AEDT
Event: FULL - FREE Webinar - Excel Date and Time Calculations
Topic: Excel Yourself 2016
Public: Public
Registration: Click here to register.