Free Excel Webinar Recordings & Materials

Live Sessions

Use the Sign me up! button (top right) to be the first to find out about free Excel webinars and special webinar offers. Each month there is at least one free webinar.

---

Recorded Sessions

Currently there are more than 30 free recorded Excel training sessions listed below. Each session is about an hour long. They all include a detailed pdf instruction manual (not a PowerPoint deck) and an Excel example file.

The first few minutes of each video covers the live webinar instructions – you can ignore that.

For CPD purposes you will need to keep a record of your viewing details.

---

The table below has the sequence I recommend you watch the webinars.

Below the table are the details of each webinar in date sequence – most recent at the top.

Free Excel Webinar Sequence

Free Courses (One hour each)	Level	Link	Materials
1. Excel Keyboard Shortcuts	Beg	Recording	Materials
2. Excel Mouse Shortcuts	Beg	Recording	Materials
3. Excel Formatting Tips	Beg	Recording	Materials
4. Excel Formula and Function Tips	Beg/Int	Recording	Materials
5. Excel Copy & Paste Tips & Tricks	Beg/Int	Recording	Materials
6. Data Handling Techniques	Beg/Int	Recording	Materials
7. Excel Guidelines	Beg/Int	Recording	Materials
***8. Excel Date and Time Calculations - NEW!	Int	Recording	Materials
9. Excel Text Functions	Int	Recording	Materials
10. Excel 2016 Conditional Formats	Int	Recording	Materials
11. Excel Custom Number Formats	Int	Recording	Materials
12. Excel Financial Functions Part 1	Int	Recording	Materials
13. Excel Financial Functions Part 2	Int	Recording	Materials
14. Excel 2013 What's New & Different	Int	Recording	Materials
15. Underused Excel Features	Int	Recording	Materials
16a. Excel 2013 Chart Tips and Tricks	Int	Recording	Materials
16b. Excel 2010 Chart Tips and Tricks	Int	Recording	Materials
16c. Excel 2016 Chart Tips and Tricks	Int	Recording	Materials
17. Dashboard Guidelines & Techniques	Int	Recording	Materials
18. Format As Table Features	Int	Recording	Materials
***19. Excel Yourself 2019	Beg/Int/Adv	Recording	Materials
20. Excel Yourself 2018	Beg/Int/Adv	Recording	Materials
21. Excel Yourself 2017	Beg/Int/Adv	Recording	Materials
22. Excel Yourself 2016	Beg/Int/Adv	Recording	Materials
23. Excel Yourself 2015	Beg/Int/Adv	Recording	Materials
24. Excel Yourself 2014	Beg/Int/Adv	Recording	Materials
25. Excel Yourself 2013	Beg/Int/Adv	Recording	Materials
26. Excel Yourself 2012	Beg/Int/Adv	Recording	Materials
27. Excel Interactive Chart	Advanced	Recording	Materials
28 Mastering Excel's Form Controls	Advanced	Recording	Materials
29. Excel 2016 What-If Techniques	Advanced	Recording	Materials
30. Excel Budget Challenge	Advanced	Recording	Materials
31. Introduction to Power Query	Advanced	Recording	Materials
32. Recording Macros Tips, Tricks & Traps	Advanced	Recording	Materials

The recommended sequence to watch my free Excel webinars.
Last updated December 2019.
Beg = Beginner, Int = Intermediate, Adv = Advanced
*** recent recordings

---

Excel Date and Time Calculations

(average feedback score 95 %)

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

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 there will be a few other tips and tricks shared during the session.

Recording Link – Excel Date & Time Calculations

Materials Link – Excel Date & Time Calculations

---

Excel Yourself 2019

(average feedback score 93 %)

In December 2019 I reviewed four of my articles from 2019 plus some new content.

Recording Link – Excel Yourself 2019

Materials Link – Excel Yourself 2019

---

Excel 2016 Chart Tips and Tricks

(average feedback score 94 %)

This webinar, from November 2019, is focused on showing you how to create and modify charts in Excel 2016 with a minimum of fuss.

The Chart interface changed in Excel 2013 and I take you through some of the changes.

You will see best practice design and formatting techniques demonstrated and explained.

See how to create dynamic charts that automatically change based on selections made or data added.

Learn about the feature added in Excel 2010 called Sparkline charts.

I also covered some useful date functions plus a quick demo of a Waterfall chart.

Recording Link – Excel 2016 Chart Tips and Tricks

Materials Link – Excel 2016 Chart Tips and Tricks

 

---

Excel Formula & Function Tips

(average feedback score 95 %)

This October 2019 session covers lots of tips, tricks and techniques to speed up the formula creation process. You will learn:

• how to quickly start a formula
• the benefits of the numeric keypad
• to use AutoComplete to save typing
• how to easily insert $ signs to fix references
• about the calculation sequence
• how to use the colours Excel displays when editing formulas
• the tricks on selecting large ranges
• using formulas and Format as Table together
• a helpful technique when working with ranges in other sheets

Recording Link – Excel Formula Tips

Materials Link – Excel Formula Tips

 

---

Introduction to Power Query

(average feedback score 94.5%)

In October I did a free session on Power Query. 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

Recording Link – Introduction to Power Query

Materials Link – Introduction to Power Query

 

---

 

(average feedback score 93.7%)

In September 2019 I re-ran my very first webinar that was based on an August 2012 INTHEBLACK feature article. I have provided a detailed manual and example files which you can download at the link below.

This session covers

• using controls with charts to make the user experience easier
• tips for using controls on spreadsheets
• flexible formula techniques to make reports more dynamic
• a technique to stop figures plotting on charts
• using range names with controls and reports
• general charting tips

Materials Link – Excel Interactive Chart

 

---

Recorded Macros – Tips, Tricks and Traps

(average feedback score 93 %)

In August 2019 I re-ran my introductory macro session.

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 paid sessions will expand on the techniques taught in this session.

This session includes bonus macros that you can use straight away to save time and effort.

Recording Link – Recorded Macros

Materials Link – Recorded Macros

---

Financial Functions Part 2

(average feedback score 87 %)

In July 2019 following on from the response and feedback from my June webinar, I covered more financial functions. These are more related to comparing and analysing cash flows. I have also included a couple of requested schedules.

This session will cover the following functions

• NPV – Net Present Value of regular cashflows (learn to trick to using it correctly)
• XNPV – Net Present Value of irregular cashflows
• IRR – Internal Rate of Return of regular periodic cashflows
• XIRR – Internal Rate of Return of irregular periodic cashflows
• Discounted Payback period schedule
• Flexible Loan schedule – handles lump sum payments

As with all my sessions, I will throw in a few other shortcuts along the way.

Recording Link – Financial Functions Part 2

Materials Link –  Financial Functions Part 2

---

Excel 2016 Financial Functions Part 1

(average feedback score 93 %)

The June 2019 free webinar examined some of Excel’s financial functions. These functions take three or more arguments, but there are a few things you need to know to use them correctly. Most involve loans or calculations associated with the time value of money.

This session covered the following six functions

• PV – Present Value of future regular cash outflow
• PMT – periodic loan repayment calculation
• CUMIPMT – cumulative interest – great for loan schedules
• RATE – interest rate
• FV – future value of regular investment
• NPER – number of periods

The session finishes with a loan model that calculates the “missing” value for a loan scenario based on two out of three inputs. The IFERROR function is also discussed.

Recording Link – Financial Functions Part 1

Materials Link –  Financial Functions Part 1

---

Excel Dashboard Guidelines and Techniques

(average feedback score 92%)

In May 2019 I covered chart and dashboard guidelines plus some techniques used to create small charts for dashboards.

We also look at creating a bullet chart which is an alternative to speedo, gauge or dial charts.

The session covers

• general chart guidelines
• questions to ask yourself to develop better charts
• best charts to use
• charts and formats to avoid
• using text boxes on dashboards
• how to create small charts
• chart templates
• lining up and re-sizing charts
• useful number formats for dashboard charts
• using the #N/A error with charts
• how to create a bullet chart

As always I shared a few other tips.

Dashboard Recording Link

Dashboard Materials Link

---

Excel’s Custom Number Formats

(average feedback score 92%)

In April 2019 with this brand new webinar we examine Custom Number Formats which hide away at the bottom of the Number Format tab.

These custom-made formats offer some useful techniques. They can

• display negatives in red and with brackets
• format mobile phone numbers correctly
• display numbers and text together and still perform calculations
• hide zeroes
• display rounded numbers to thousands and millions
• display the day of the week
• create customised dates
• be saved and be available in new sheets and files using a Template
• be saved as a Style

See examples and demonstrations of many different custom number formats and learn how to create your own.

As always I share a few other tips.

Recording Link – Excel Custom Number Formats

Materials Link – Excel Custom Number Formats

---

Mastering Excel’s Form Controls

(average feedback score 92%)

The March 2019 webinar demonstrated Excel’s built-in Form Controls. Learn how to build better spreadsheet interfaces using form controls like check boxes, scroll bars, option buttons and combo boxes.

Form controls can reduce the number of input errors; simplify and speed up input plus add structure to a file.

Discover the function that you need to use to get the most out of these controls. Find out a few tips and tricks to make creating and using form controls easier.

Recording Link – Excel Form Controls

Materials Link – Excel Form Controls

---

Excel Format As Table Features

(average feedback score 93%)

Format as Table Webinar recording link

Format as Table Webinar Materials

---

Excel Budget Challenge

(average feedback score 93%)

The January 2019 free webinar covered a solution to a budget challenge.

In November 2018 I became aware of a multi-dimension budget challenge. The challenge finished at the end of November.

This session includes a lot of useful content and it brings together a number of different techniques. The content is worthy of a paid session but since it was a public challenge I am presenting the session for free.

The materials includes the challenge documentation, the pdf manual and the solution file. I assume you have read the documentation. You also receive the blank Excel file if you want to create your own solution.

Techniques/topics covered include

• using INDEX-MATCH (better alternative to VLOOKUP)
• 3-D formulas to summarise all report sheets and techniques to make using them easy
• using a reporting template to speed up creation
• validations
• extracting sheet names
• automating reports

Budget Challenge Webinar recording link

Budget Challenge Webinar Materials

---

Excel Yourself 2018

(average feedback score 89%)

In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. Use the buttons below to download the materials and watch the video.

Includes extra content not included in the magazine.

This year’s smorgasbord webinar includes

• how to handle validations
• dynamically highlighting the current row in a table (uses a macro)
• centralising logic – how and why to do it – alternatives to the IF function
• creating a slope chart (includes a macro to speed up the process)

As always there will be a few more tips and tricks shared in the session.

Excel Yourself 2018 Webinar recording link

Excel Yourself 2018 Webinar Materials

 

---

Excel 2016 Mouse Shortcuts

(average feedback score 93%)

When I ran some face to face training sessions recently I was reminded how much people LOVE shortcuts. I did a keyboard shortcut webinar session in October 2018.

In November 2018 I shared lots of mouse shortcuts. Download the materials using the button below and watch the video.

Some of the topics and techniques you will learn include:

• the various cursor shapes and how to use them correctly
• right clicking options you may not know exist
• copying anything quickly with the mouse, even sheets
• drawing perfect circles and squares
• lining up charts and other shapes perfectly
• cool techniques with the Fill Handle
• something you may never have tried with the right mouse button
• using the Office clipboard
• a trick to use the Series dialog

This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard.

Even if you prefer using the keyboard you might learn a few useful new techniques.

Mouse Shortcuts Webinar recording link

Mouse Shortcuts Webinar Materials link

---

Excel 2016 Keyboard Shortcuts

(average feedback score 93%)

The October 2018 free webinar looked at keyboard shortcuts. Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:

• wrap text and other useful formats
• applying row and column grouping
• selecting a table quickly
• copying visible cells only
• apply and remove Freeze Panes
• returning after following a hyperlink
• how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
• get the most out of the Tab key

There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.

Keyboard Shortcuts Webinar recording link

Keyboard Shortcuts Webinar Materials link

---

Excel 2016 What If Techniques

(average feedback score 92%)

In the September 2018 webinar I examine Excel’s built-in What-If techniques. In Excel it is easy to change a few cells and see the results. You can use trial and error to achieve a result. There are a few built-in features that can make this process a lot easier and save you a lot of time. In terms of financial models and budgets, you may also want to handle best, worst and base case scenarios. The session introduces the following 5 topics

• Goal Seek – simple what-if changes
• Solver – advanced what-if analysis
• Scenario Manager – handling different sets of inputs
• Data Tables – single and double variable sensitivity analysis
• Forecast Sheet (Excel 2016 and later versions)

Excel 2016 What If Techniques Recording Link

Materials Link – Excel 2016 What If Techniques

---

Excel 2016 Conditional Formatting Techniques

(average feedback score 94 %)

In August 2018 I covered conditional formats in Excel 2016. These allow you to have cell formats automatically change based on the value in the cell and other cells. This is great for exception reporting. For example when you open a file all the overdue dates could have a red fill colour based on the today’s date and the dates for the coming week will be coloured orange. Some of the techniques covered can also be applied to dashboards.

This session will take you through the basics, as well as an introduction to formula-based formats.

The session will include:

• Data bars
• Creating a progress bar using Data bars
• Colour scales – traffic light colours
• Amending the default settings – getting the result you want
• Icon sets – icons can be better for colour blind people
• Cell-based rules – make the most of built-in features
• Working with dates automatically
• Formula-based rules – use formulas and functions to gain total control over conditions

As always you may pick up a few other tips along the way.

Excel 2016 Conditional Formats Recording

Materials Link – Excel 2016 Conditional Formats

---

Excel Copy and Paste Tips and Tricks

(average feedback score 93 %)

My free Excel webinar for June 2018 covered Copy and Paste Tips and Tricks.

The session focuses on the Paste Special dialog plus a little known pasting feature that is great for dashboards.

It covers the hows and whys of

•  Paste Values,  Paste Formulas,  Paste Formats
•  Converting negative to positives
•  Fixing Text numbers in-situ
•  Applying a Factor to a range
•  Paste Link – how and why to use it
•  Transpose (switching rows to columns and visa-versa)
•  Paste Picture Link (great for dashboards)

The session includes lots of keyboard shortcuts. As always, I will be sharing a few other tips during the session.

Recording Link – Copy Paste

Materials Link – Copy Paste

---

---

 

Excel Yourself 2016

(average feedback score 89 %)

This is my annual smorgasbord session from December 2016. I revisit five of my magazine articles from the year and add some extra content. We cover a couple of functions; a filtering solution; how to reduce file size plus a feature added in Excel 2013 that can read your mind (sort of).

There are two dates to choose from – content is the same on both days. If you can’t attend you can still enroll and you will receive the recording link after the event.

This session covers five of my articles from the year. Topics are:

• the new Flash Fill feature (Excel 2013) – why, how and when to use it
• how to quickly adjust multiple calculations using a factor
• the binary file file type – advantages and disadvantages
• a reporting solution using the INDIRECT function
• Slicers – the easy way to filter data

Recording Link – Excel Yourself 2016

Materials Link – Excel Yourself 2016

---

Excel Data Handling Techniques

(average feedback score 90 %)

In this October 2016 session I have revamped a session I ran three years ago and added some new content. Learn the easy way to Filter and Sort. See how to use colours for filtering and sorting. See how versatile Advanced Filtering can be.

This session covers tips, tricks and techniques to help you manipulate data tables in Excel.
You will learn:

• How and why you should use Format As Table
• Filtering tips
• Sorting tips
• How to create and use Custom Lists
• How, why and when to use Advanced Filter
• How to extract unique entries
• Sorting and Filtering by colours, including using conditional formats

Recording Link – Data Handling Techniques

Materials Link – Data Handling Techniques

---

 

Excel 2013 Chart Tips and Tricks

(average feedback score 91 %)

This webinar, from June 2016, is focused on showing you how to create and modify charts in Excel 2013 with a minimum of fuss.

The Chart interface changed in Excel 2013 and I take you through some of the changes.

You will see best practice design and formatting techniques demonstrated and explained.

See how to create dynamic charts that automatically change based on selections made or data added.

Learn about the feature added in Excel 2010 called Sparkline charts.

Recording Link – Excel 2013 Chart Tips and Tricks

Materials Link – Excel 2013 Chart Tips and Tricks

---

Excel 2010 Chart Tips and Tricks

(average feedback score 87 %)

This webinar, from June 2016, is focused on showing you how to create and modify charts in Excel 2010 with a minimum of fuss.

You will see best practice design and formatting techniques demonstrated and explained.

See how to create dynamic charts that automatically change based on selections made or data added.

Learn about the feature added in Excel 2010 called Sparkline charts.

Recording Link – Excel 2010 Chart Tips and Tricks

Materials Link – Excel 2010 Chart Tips and Tricks

---

 

Excel Yourself 2015

(average feedback score 87.3 %)

The January 2016 free session covered five of my INTHEBLACK magazine articles from 2015.

Topics covered:

• Displaying numbers as text, but still performing calculations with them
• Identifying possible duplicates when data is missing
• Converting a single budget model to handle multiple scenarios
• Using Styles
• Reporting on multiple tables in Excel 2013 without VLOOKUP (requires Excel 2013 or later)

Recording Link – Excel Yourself 2015

Materials Link – Excel Yourself 2015

---

Excel’s Underused Features

(average feedback score 87.6 %)

The October 2015 free session covered features that many users are unaware of but can speed up some useful tasks.

A combination of no manual and regular new versions mean that many people don’t know about Excel’s time saving features.

This session will explain and demonstrate some of the features that I see underused by many users. When I show these features to users they are usually both surprised and thankful.

Some of the features covered include
• Format as Table
• Removing Duplicates
• Grouping rows and columns
• Automated Subtotals
• Cell comments and Text Boxes
• Using Windows within Excel
These features offer a lot of time savings opportunities, which will be explained in the session.

Content applies to Excel 2007 and later versions. Much of the content also applies to Excel 2003.

Recording Link – Excel Underused Features

Materials Link – Excel Underused Features

---

Excel 2013 – What’s New & Different

(average feedback score score 89.3%)

My June 2015 free session was the first to use Excel 2013.  I thought I’d start with what’s new. Many organisations have a policy to have the second last version of Office, so Excel 2013 may soon be getting rolled out to a PC near you.

This session will cover some of the new features plus some of the changed features. Some of the topics covered include:

• how to stop the Start screen from displaying
• new functions in Excel 2013
• Flash Fill – what it is and what it can and can’t do
• Slicers and Formatted Tables
• the new Timeline slicer
• changes to charts
• PowerPivot what it is and a demonstration of what it can do

Even if you don’t have Excel 2013 you can see what’s in store if you upgrade.

Recording Link – Excel 2013 What’s New

Materials Link – Excel 2013 What’s New

---

 Excel Yourself 2014

(average feedback score score 87.6%)

In December 2014 I reviewed 5 of my 11 articles that appeared in the CPA Australia INTHEBLACK magazine during the year. The webinar’s companion Excel file contains 10 out of the 11 Excel examples from the 2014 articles.

The session covered the following smorgasbord of topics

• a macro to unhide an individual column on the left of the active column but on the right of a large group of hidden columns
• amending the RANK function to provide a sequential ranking – no ties
• creating unique codes from duplicate codes so each can be accessed individually
• how to create a bullet chart (better and smaller than a speedo or gauge chart)
• how to use formulas to create a data table from badly laid out data

Recording Link – Excel Yourself 2014

Materials Link – Excel Yourself 2014

---

Excel Yourself 2012

(average feedback score 90.1%)

The April 2014 webinar follows on from my popular webinar from Dec 2013. I have gone back another year and review 6 of my articles from the CPA Australia INTHEBLACK magazine during 2012. This is a veritable smorgasboard of topics including the SUBTOTAL function; formulas for sheet names; conditional formatting using formulas; a macro that links the print footer to a cell; how to use the TEXT function and fixing a problem with the RANK function.

Recording Link – Excel Yourself 2012

Materials Link – Excel Yourself 2012

---

Excel Yourself 2013

(average feedback score 91.2%)

The December 2013 webinar featured six of my eleven Excel Yourself topics from the CPA Australia INTHEBLACK magazine for 2013.

See the articles come alive. The companion Excel file contains ALL the examples from ALL the year’s articles – including the two macros. Extra content was also included.

Topics covered include

• creating a MAXIF function
• summing the top 5 values in a range
• creating a Pareto chart
• performing workday calculations
• deleting zero rows

Recording Link – Excel Yourself 2013

Materials Link – Excel Yourself 2013

---