Category Archives: Videos

Posted on

Free Webinar Recording – Introduction to Power Query

Feedback score 94.5% based on 91 responses

In October 2019 I ran my Introduction to Power Query webinar for free (previously it was a paid session). I want to get this information out to as many people as possible. please share this resource with colleagues and your network.

The detailed pdf manual and example file can be downloaded by using the button below.

Download 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

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Related Posts

Posted on

Free Webinar Recording – Excel Formatting Tips

Feedback score 94% based on 70 responses

In October 2019 I re-ran my Formatting Tips session. The detailed pdf manual and example file can downloaded by using the button below.

Download Materials

This session covers:

  • a format to avoid and the one to use in its place
  • keyboard and mouse shortcuts
  • how to use and create customer number formats
  • about Styles and how they can make your formatting more consistent
  • that colours can be used to filter, sort and even find things in your sheets
  • how to stop zeroes displaying plus other general formatting tips
  • a quick demo of Flash Fill

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Related Posts

Posted on

Free Webinar Recording – Interactive Excel Chart

Feedback score 93.7% based on 58 responses

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 pdf 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

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Download Materials

Related Posts

Posted on

Free Webinar Recording – Recorded Macros Tips Tricks and Traps

Getting started with recorded macros

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.

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 this month (August 2019) will expand on the techniques taught in this session. Click here to register.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Download Materials

Related Posts

Posted on

Free Excel Webinar Recording – Financial Functions Part 2

Feedback score 87%

In July 2019 I explained and demonstrated a number of Excel’s  financial functions – see below for more details.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

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.

Related Posts

Posted on

Twelve months of free Excel webinars

Free monthly Excel webinars

This month celebrates 12 consecutive free monthly Excel training webinars. One a month since August 2018.

You can view the recordings and download the materials using the table below. Sorted by Skill Level.

12 hours of free, practical CPD. Remember to keep your own records of recording viewing for CPD purposes.

Please share with the your network, enjoy!

Latest Free Excel Webinars

Free Courses (One hour each)LevelRecording LinkMaterials
Excel Keyboard ShortcutsBegRecordingMaterials
Excel Mouse Shortcuts BegRecordingMaterials
Excel Formatting TipsBegRecordingMaterials
Excel 2016 Conditional FormatsIntRecordingMaterials
Excel Custom Number FormatsIntRecordingMaterials
Excel Financial Functions Part 1
IntRecordingMaterials
Excel Financial Functions Part 2
IntRecordingMaterials
Dashboard Guidelines & TechniquesIntRecordingMaterials
Format As Table FeaturesIntRecordingMaterials
Excel Yourself 2018 Beg/Int/AdvRecordingMaterials
Mastering Excel's Form Controls
AdvRecordingMaterials
Excel 2016 What-If Techniques AdvRecordingMaterials
Excel Budget ChallengeAdvRecordingMaterials
Excel Recorded MacrosAdvRecordingMaterials
Interactive Excel ChartAdvRecordingMaterials
Introduction to Power QueryAdvRecordingMaterials
Sorted by Level - updated October 2019
Level Beg = Beginner, Int = Intermediate, Adv = Advanced

Related Posts

Posted on

Free Excel Webinar Recording – Financial Functions Part 1

Feedback score 93%

In June 2019 I explained and demonstrated a number of Excel’s  financial functions – see below for more details.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

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.

Related Posts

Posted on

Free Excel Webinar Recording – Dashboard #1 Guildelines & Techniques

Feedback score 92%

In May 2019 I examined and demonstrated some guidelines and techniques for creating charts for dashboards.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

In this session I focused on chart and dashboard guidelines plus some techniques used to create small charts for dashboards.

I also looked at creating a bullet chart, which is an alternative to gauge, speedo or dial chart.

The session covered

  • 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.

Related Posts

Posted on

Multiple Entries in an Excel Filtered List

Ctrl key to the rescue

When a list is filtered you are only seeing the rows that match the filter. The other rows are still there, just hidden. If you want to make the same entry in a group of filtered cells you can’t use the fill handle to drag and copy as you will overwrite the hidden rows. There is an easy way to do it.

Posted on

Free Excel Webinar Recording – Custom Number Formats

Feedback score 92%

In April 2019 I demonstrated many of Excel’s Custom Number Formats.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

In 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 will share a few other tips.

Related Posts

Posted on

Free Excel Webinar Recording – Mastering Form Controls

Feedback score 92%

In March 2019 I demonstrated how to use some of Excel’s Form Controls.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

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.

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

Related Posts

Posted on

Free Excel Webinar Recording – Format As Table Features

Feedback score 93%

In February 2019 I demonstrated how to use the Format as Table feature in Excel, including some advanced techniques.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

Formatted tables can allow you to create powerful reports like those in a relational databases.

Topics covered

  • advantages and limitations of formatted tables
  • keyboard shortcuts
  • using formatted tables with formulas
  • solutions to some of the limitations of formatted tables
  • using range names with formatted tables
  • using formatted tables with data validations
  • creating a running total
  • using PivotTables
  • Relationships (Data tab)

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

Related Posts

Posted on

Free Excel Webinar Recording – Budget Challenge

Feedback score 93%

In January 2019 I presented a webinar that examined a solution to a 4 dimension budget challenge. Download the materials using the button below and watch the video.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

NOTE: This is not a beginner’s session.

This webinar is based on a budget scenario which you need to read before the session starts. It is only 3 pages long and included in the materials.

Topics covered include

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

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

Related Posts

Posted on

Free Excel Webinar Recording – Excel Yourself 2018

Feedback score 89%

In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. Download the materials using the button below and watch the video.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

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.

Related Posts

Posted on

Free Excel Webinar Recording – Mouse Shortcuts

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.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

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 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.

Related Posts

Posted on

Free Excel Webinar Recording – Keyboard Shortcuts

Feedback score 93%

When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I decided to update my keyboard shortcut webinar.

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

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Webinar Materials

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.

Related Posts

Posted on

Free Excel Webinar Recording – What If Techniques

Feedback score 92%

My free Excel webinar for September 2018 covered What If Techniques. Download the materials using the button below and watch the video.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Download Webinar Materials

Content applies to Excel 2010 and later versions. You will need to install the Solver Add-in – instructions in the manual and  video.

  • 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
  • NEW – Forecast sheet

Related Posts

Posted on

Free Excel Webinar Recording – Conditional Formatting

Feedback score 94%

My free Excel webinar for August 2018 covered Conditional Formats. Download the materials using the button below and watch the video.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Download Webinar Materials

Content applies to Excel 2010 and later versions.

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

  • Data bars
  • Creating a progress bar using a Data bar
  • 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

Related Posts