Past Excel Webinar Details

Flexible training - detailed pdf manuals, watch when you want, pause & rewind

Recorded Excel Training Webinars

You can purchase recordings of webinars including the detailed pdf instruction manual and the Excel file. The recordings can also be downloaded to watch offline. You can watch the recording (pause and replay), following along with the example file provided. Use the Sign me up! button (top right) to always get first access to free Excel webinars and special offers. Each month there is at least one free webinar.

The price for each recorded webinar is $25 incl GST.

Webinar Passclick here to see how you can save money on both live and recorded webinars and earn bonus webinars. With a Webinar Pass the price is $20 each.

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

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


Dashboards 02 – Formula-based Dashboard

(average feedback score 94%)

Note: the first dashboard session was free and can be seen on this page.

This is the old school technique of creating dashboards in Excel.

We create summary reports from the data and from those summary reports and extract the chart data which is used to base our charts on.

We look at techniques to create small charts and how we can combine those on a dashboard.

The session includes

  • formula basics
  • formulas that work with dates
  • automating headings
  • SUMIFS function
  • INDEX function
  • formats for dashboards
  • using templates

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


Dashboards 03 – Sparklines and Conditional Formatting

(average feedback score 90%)

This is the third in the series and focuses on Sparkline charts added in Excel 2010 as well as using Conditional Formatting with dashboards.

Sparkline charts are small charts that are specifically designed for dashboards.

We add ticks and crosses using conditional formats to identify measures (good and bad) that may need review.

The Chart Data sheet structure has already been populated so we will review some of the formulas used in its creation.

The session includes

  • reviewing report formulas
  • MATCH function
  • SUMPRODUCT function
  • handling high score is bad vs low score is bad in a formula
  • default settings you may need to change for Sparkline charts
  • controlling the display of ticks and crosses
  • formats for dashboards
  • using templates

I will share other tips and tricks along the way.


Dashboards 04 – PivotCharts and Slicers

(average feedback score 94%)

This is the fourth in the series and focuses on creating a dashboard using PivotCharts with Slicers.

PivotTables are extremely powerful and allow you to summarise large data sets very quickly without using any formulas. PivotCharts are charts that are based on PivotTables and are extremely interactive. This dashboard will use Slicers to add interactivity to the dashboard so that users can change the dashboard.

The session includes

  • how a PivotTable works
  • creating a PivotTable that works well with a chart
  • formats for large values
  • making PivotCharts display better for dashboards
  • making changes to PivotTables to improve headings
  • sorting and Top 10 reports
  • use Slicers to control filters on multiple PivotTables
  • using the GETPIVOTDATA function to create specify reports/charts

I will so share a few other tips and tricks along the way.


Three Statement Budget

(average feedback score 90%)

This session examines how you can create a combined Profit & Loss, Balance Sheet and a Cash Flow budget.

We will focus on reviewing existing formulas and structures.

The techniques covered include

  • display and format techniques for budgets
  • functions used EDATE, IF, SUMIFS, INDEX, MATCH
  • using centralised logic calculations
  • different budget assumption calculations
  • handling single or monthly assumptions
  • getting a flexible starting position for the balance sheet
  • using a workings sheet
  • loan schedule
  • handling GST
  • validations that handle error cells

Plus I am will sharing extra tips along the way.


Gantt Chart Workshop (1.25 hours)

(average feedback score 93%)

A Gantt chart is a project management tool to help you manage multi-task projects.

There are many online apps that will help you create one but I thought I would use a Gantt chart as a way to demonstrate how to use many of Excel’s features together to create a reporting model.

The techniques covered include

  • date formulas
  • text formulas
  • conditional formats
  • scroll bar control
  • formatted tables
  • range names
  • data validation

 


VLOOKUP Function

(average feedback score 87%)

Over the years this popular function has been the subject of the most questions to me from CPA’s from around the world. So I thought I would dedicate a whole webinar to how and why to use it.

I have also included techniques to get around the limitations of the function. Most content applies to all Excel versions.

In this session I will cover

  • the Exact match VLOOKUP – when to use it
  • the Approximate match VLOOKUP – when to use it
  • tips and shortcuts on using VLOOKUP
  • common problems with VLOOKUP and their solutions
  • why Format is Table is so important
  • handling errors
  • handling multiple tables
  • removing the left column limitation
  • two column lookups (non array solution)
  • using the Relationship option to avoid VLOOKUPs (Excel 2013 & later versions only)

As always, I will share a few general tips along the way.


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%)

This session is now FREE – check out this page to watch and download the materials.

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

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 Chart Hacks

2010 (average feedback score 92%)

2013 (average feedback score 93%)

These sessions will show you how to achieve improved charts and how to create charts that aren’t built-in to Excel. Screen shots and instructions vary between XL2010 and XL2013. There are separate recordings for XL2010 and XL2013.

Topics covered:

  • using text boxes with charts
  • improving Excel’s default charts – before and after charts
  • Variance chart
  • High – Low Chart
  • Conditional colours in a chart
  • Waterfall/Bridge chart
  • Adding banding to a chart

The techniques covered can be used to solve other chart challenges. As always, you will also pick up a few tips and tricks along the way.


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.

 


 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.


Macros 1  – Recording Tips, Tricks and Traps

(average feedback score 87%)

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.

 


Macros 9 – Macro Reports and Add-in Creation

(average feedback score 90%)

Add-ins are special files that remove a number of limitations associated with macros. This session will show you how to create a macro that runs multiple reports and then turn it into an Add-in file that you can use to create the reports on any file.

The three reports created are

  1. Comments Report – lists details of all cells with comments, including a hyperlink – great for documenting a file
  2. Error Report – lists details of cells identified by Excel’s built-in error checker
  3. Adjustment Report – list details of cell formulas with added value entries eg =SUM(A1:A10)+100

These reports are great for documentation and assistance with debugging and auditing files. All the reports include a hyperlink to the cell involved.

Note: this is NOT a beginners session! You need experience with macros to get the most out of this session.

You will learn

  •  how to use a single macro that creates multiple reports
  •  to create a report to list all the cell comments in a file
  •  to create a report to list errors in a file
  •  to create a report to list the manual adjustments in a file
  •  the Select Case structure
  •  how to add options to the right click menu
  •  the advantages of Add-ins
  •  how to convert a file into an Add-in

This is a code intensive session with lots of great code examples. This is not a beginners session, you need to have experience with macros. As always, I will share a few general tips along the way.

 


 

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