Past Excel Webinar Details

Watch when you want, pause and rewind

Recorded Training Webinars

You can purchase access to recordings of webinars including the detailed pdf instruction manual and the Excel file. You can watch the recording (pause and replay), following along with the example file provided. Use the Subscribe button (top right) to always get first access to free Excel webinars and special offers. The next free Excel webinar will be held in June 2017.

The price for a recorded webinar is $20.00 incl GST. Discounts apply for multiple purchases  $35 for two and $50 for three.

The recordings could count towards your CPD (Continuing Professional Development) requirements. Currently there are 29 recorded webinars listed below, totaling over 34 hours of video time, including 9 hours of macro content.

Click here to contact Neale Blackwood to purchase any of the recorded webinars below

Recorded Excel Webinars

($20 for one, $35 for two – incl GST)


Budget and Power Query

(average feedback score 89%)

Discover how to use Power Query to solve two major issues in multi-file budget systems. Example uses a P&L budget. These techniques have been applied to a budget, but could also be used for forecasting and reporting.

Learn how to

  • quickly create a budget template to speed up development time
  • link all budget files to central tables that expand
  • create dynamic drop down lists
  • compile multiple Excel budget files into a single budget layout
  • create a normalised budget listing ready for use in PivotTables and formula-based reports
  • use folders for versions and easily update the folder in your compilation file

This is a practical, hands-on application of Power Query in a budgeting setting.


Power Query and Multiple Files

(average feedback score 92%)

Building on the skills covered in the Introduction to Power Query session (see below), in this session we work with multiple files. For example you may have 12 separate CSV files in a folder. All with the same layout. One file for each month of the year. Power Query can import all 12 files as if they were a single file and create a single table for the whole year. This opens up possibilities for bringing together budgets and forecasts or consolidating multiple Excel files.

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

Introduction to Power Query

(average feedback score 90%)

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 – how to convert a report layout into a data table layout
  • converting a MYOB report into a data table

Introduction to Array Formulas

(average feedback score 91%)

Array formulas can perform calculations that no other formula can perform. Array formulas can be complex, confusing and difficult to understand. In this session I hope to remove some of the confusion and explain how they work with some useful examples and techniques.

If you use arrays, or have ever wondered what they are, then this session will be useful.

WARNING: You need to have at least intermediate Excel skills to be able to follow this webinar. Whilst I will attempt to simplify the complexity for you, the last two examples in the session have quite complex formulas that may make your head hurt. The session includes

  • advantages and disadvantages of arrays
  • array syntax – what it is and how to use it with other non-array formulas
  • simple single cell array formulas
  • single cell validation formula
  • multi-cell array formulas demonstrated and explained
  • dynamically extract a sorted range using an array formula
  • dynamically extract unique values from a range with an array formula

Introduction to Power Pivot

(average feedback score 88%)

PowerPivot removes the limit of one table, one report for your Pivot Tables. You can combine data from multiple sources into a single report. You can can summarise tables in relational databases without involving the database administrator or having to have a View created. Its a pivot table on steroids. PowerPivot can report on virtually any relational data base as well as bringing together data from different sources into a single report. Eg combining a csv file; an Access database and an Excel table.
The session will cover

  • background information on using relational databases
  • creating a PowerPivot report from a large relational database
  • adding tables to the data model to expand the reporting possibilities
  • techniques to make the report more reader-friendly
  • hiding fields to simplify the interface
  • using Slicers
  • books and websites

Advanced Range Names (1.5 hours)

(average feedback score 90%)

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 covers

  • 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

 


 

Essential Range Names

(average feedback score 89%)

Learn the basics, plus some powerful techniques to make your range names more dynamic. This session has everything you need to get started with using range names effectively. Even if you have been using range names this session provides some useful shortcuts and techniques that you may not be aware of.

This session covers

  • the three ways to create a name (some are much easier than others)
  • naming rules and suggestions
  • keyboard shortcuts
  • name intersections
  • formula-based names
  • using names with formatted tables

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


Excel Tips plus Q&A (1.25 hours)

(average feedback score 92%)

A different format than normal for this session. We start with some tips & tricks and then move on to questions sent in by attendees.

This session’s manual covers the following topics.

  • round to a specific number eg 5 cents
  • adding a note to a formula
  • AutoCorrect tricks – save yourself some typing
  • inserting a line break in a cell or a textbox
  • finding formats

The second part of the session (not in the manual) covered questions relating to

  • extracting a column letter
  • extracting the sheet name
  • working with different currency formats
  • Handling data formatted as text
  • Using the Paste Special dialog to switch signs and transpose
  • Displaying a dash for zero percent
  • Not displaying zeroes

 

Excel Data Validation (1.5 hours)

(average feedback score 95%)

Excel has a built-in Data Validation feature that enables you to limit and control the entries that go into certain cells. You can use this to make sure users comply with your input requirements. Unfortunately the feature has one failing. Learn what it is and how to handle it.

This session will take you through some useful techniques to create flexible data validations including in-cell drop down lists.

The session covers

  • setting upper and lower parameters – fixed and flexible
  • creating custom error messages
  • how to validate the validations and show the user when an error has been made
  • creating in-cell drop down lists
  • creating flexible lists that automatically expand and don’t require any complicated formulas
  • how to create dependent drop downs – one drop down affects the entries in a second drop down

 Excel Data Cleansing Techniques (1.5 hours)

(average feedback score 89%)

If you get the data right in Excel, then everything else becomes much easier. Pivot Tables can be created and formula-based reports become much more straightforward to create. Sometimes the data that comes into Excel is badly structured or has issues that stop you using Pivot Tables or formulas. This session will take you through a number of techniques that solve many of those problems and prepares the data ready for reporting.

The techniques can also be applied to other issues in Excel. The session will include:

  • Format As Table
  • filling blanks with zeroes
  • populating missing entries
  • removing leading and trailing spaces
  • removing spaces with a formula
  • fixing and converting dates
  • converting values to numbers
  • changing the structure from vertical to horizontal
  • text to columns
  • VLOOKUP
  • Removing duplicates
  • working with vertical data


Excel Pivot Table Essentials

(average feedback score 93%)

Pivot tables allow you to summarise and report on large data sets in seconds.This updated session will cover the basics of pivot table creation as well as solving some common frustrations. Topics covered include

  • pivot cache what it is and why it is important
  • data rules that make creating pivot tables easier
  • format as table and why you need to use it
  • populating missing entries in the data
  • the basics of creation
  • pivot charts
  • filtering with slicers
  • grouping
  • using external data

You will probably pick up a few other tips and tricks along the way.


Advanced Excel Pivot Tables (1.5 hours)

(average feedback score 90%)

Pivot tables allow you to summarise and report on large data sets in seconds.This new session is one and a half hours long and assumes you are already using pivot tables. Topics covered include

  • Calculated Fields – what they are and how and why to use them
  • Calculated Items – what they are and how and why to use them
  • Sorting and custom sorts
  • Advanced filtering
  • The GETPIVOTDATA function – the secret to using it effectively
  • Useful Report Layouts
  • Conditional Formatting
  • Data normalisation and variance analysis example
  • PowerPivot demonstration
  • Two handy macros that can speed up pivot table creation

You will no doubt pick up a few other tips and tricks along the way.


Excel Budget Template (1.5 hours)

(average feedback score 85%)

I will share a template structure that is flexible and easy to maintain. This session will take you through building the model and how you can amend it. Techniques covered in the webinar will include

  •  3D summing
  • INDIRECT function to extract budget data
  • using table structures to make maintenance easier
  • creating a sheet to upload into a system
  • validations

This session is focused on budget structures, not allocations.


SUMPRODUCT Function (1.5 hours)

(average feedback score 92%)

I am excited about this webinar because it covers my favorite Excel function. SUMPRODUCT is the Swiss Army knife of functions. It can sum, count and perform criteria-based calculations that normally require special array formulas. The SUMIF and SUMIFS functions have two important limitations that SUMPRODUCT doesn’t, discover what they are.

The session outcomes will include

  • The limitations of the SUMIF and SUMIFS functions
  • The limitations and advantages of the SUMPRODUCT function
  • Explanation of the standard use of the SUMPRODUCT function
  • Explanation of undocumented techniques of the SUMPRODUCT function
  • How to create powerful multi-criteria calculations without an array formula
  • Learn about array syntax and how you can use it with SUMPRODUCT
  • Discover how you can use INDEX, OFFSET and other functions with SUMPRODUCT

INDIRECT Function

(average feedback score 94%)

Few Excel functions warrant their own webinar – INDIRECT does. It can allow you to do things that no other function can do. It is often the function of last resort as it allows you to use text to create cell references and solve difficult issues.

  • The outcomes from this session will be
  • Learning the basics of the INDIRECT function
  • Discovering a common problem with sheet references and how to fix them
  • Seeing how to extract data from structured and unstructured data
  • Finding out the power and flexibility of using ranges names with the INDIRECT function
  • Learning how to link one data validation list to another


 Excel Dashboard Techniques – Part One

(average feedback score 90%)

This is the first in a series of webinars on creating dashboards. This session will focus on small, yet useful charts. Excel is an ideal platform to create dashboards, as it can bring together data from multiple sources to allow you to include financial and non-financial data. The session will include:

  • techniques that allow you to create small charts – the building blocks of dashboards
  • the charts to avoid and their alternatives
  • Sparkline charts (cell-based charts new in Excel 2010) their advantages and limitations
  • techniques that allow you to remove axes, legends and titles
  • re-size and layout out your charts perfectly.


Excel Lookup Formulas

(average feedback score 88%)

This session explains and demonstrates Excel’s lookup functions and formulas. Discover how you can extract data from data tables using formula. (There’s a lot more to lookups than just the VLOOKUP function!)

  • VLOOKUP and HLOOKUP functions – how to use them and why they don’t always work and what breaks them
  • INDEX-MATCH combination technique – create flexible lookups
  • Error handing – see how to handle the #N/A! error differently from other errors
  • Working with vertical data – discover how to convert vertical data (like addresses) into a table layout
  • Two criteria lookups – learn the formula that can extract data based on two criteria
  • Extracting data with multiple instances of entries – find out how to extract the first, second or third instance from a data table.

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


 Excel Logic Calculations

(average feedback score 97%)

Discover how to add decision making structures into your spreadsheets to make them more flexible. There’s a lot more to learn than just the IF function.

The session covers

  • using logic calculations without the IF function
  • validations
  • the IF, AND as well as the OR functions
  • nesting IF functions and formula shortening techniques
  • working with parameters for reasonableness checks
  • error handling
  • IS functions

 

Advanced Budgeting Techniques

(average feedback score 92%)

Learn advanced techniques that enable you to

  • choose monthly allocation methods on a line by line basis
  • select different inflation rates to apply to different cost types 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 your options
  • create a flexible validation system

The techniques demonstrated and explained can also be applied to forecasting or financial modelling.


Macros 1  – Recording Tips, Tricks and Traps

(average feedback score 87%)

This session is now available free – click here for details.

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.


Macros 2 – Recorded To Remarkable (1.5 hours)

(average feedback score 86%)

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.


Macros 3 – Interaction and Debugging Techniques (1.5 hours)

(average feedback score 88%)

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.


Macros 4 – Automating Macros with Events

(average feedback score 88%)

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.


 Macros 5 – Function Macros

(average feedback score 90%)

Excel Function macros allow you to create your own cell-based functions, just like Excel’s built-in functions. These are called User Defined Functions (UDFs) and can be used to simplify complex calculations.

Macro Functions also allow you to create re-useable code which can centralise your business logic and make code maintenance easier.

This session will examine a number of Functions to explain how, why and when to use them. This session will cover

  •  SUMBOLD UDF – add up bold values
  • JOIN UDF – joins text togther from a range
  • functions for sheet, file and user names
  • alternatives for the SEARCH and FIND functions
  • SUMCOLOUR UDF – adds up specific values in cells with a certain fill colour
  • an “Are You Sure?” function
  • a function to identify if a one of a list of words is used in a cell

 

Macros 6 – Macro Workshop 1

(average feedback score 90%)

Learn how to bring together many of the techniques from previous sessions to solve real world problems.

The four macros covered are

  • conditionally hide rows
  • conditionally hide columns
  • clear input cells
  • convert a MYOB report in to a data table.

A variety of macros are covered showing you different ways to achieve similar outcomes. Lots of tips and tricks along the way.


Macros 7 – Macro Workshop 2

(average feedback score 95%)

The second Macro Workshop looks at

  • allowing the user to select file(s) to use in your macro
  • a table technique to hide sheets
  • using sheet controls to interact with your macro
  • entering formulas into a range cells via a macroWe will go through the macros and see how and why they work.As always, I will share other tips and tricks along the way.

Macros 8 – VBA Variables

(average feedback score 88%)

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 plus the why, when and how to use them
  • the shortcuts that reduce the typing involved with variables
  • handy examples to demonstrate variable usage
  • variables and building user messages
  • selecting separate sheets based on user input
  • combining data sheets based on a criteria

The session finishes with a worked example where we bring together many of the techniques discussed.

 


 Summing Functions for Accountants

(average feedback score 92%)

Everyone uses the SUM function, but have you ever used a 3D SUM function? This technique is flexible and allows you to easily include or exclude sheets from totals.

Do you know how and why to use SUMIF and SUMIFS functions? If you need to do a multi-criteria SUM in Excel 2003 have you tried the SUMPRODUCT function? Discover some of the powerful uses of SUMPRODUCT.

Find out why you shouldn’t use the SUM function for subtotalling and how you should do subtotals. Also learn about a new function in Excel 2010 that can ignore error cells when summing.


 

 Click here to contact Neale Blackwood to purchase any of the recorded webinars above