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.

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.

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

Some actions clear the clipboard

Some actions in Excel will clear the clipboard after you have copied or cut.

Two common ones are

  • inserting a new sheet
  • clearing a filter – using Clear in the Data menu (keyboard shortcut Alt a c )

So if you are going to do either of these, do them before copying or cutting.

Increase Font Size in VBA Window

Easy change

Yes, you can make the text in the VBA window easier to read.

It is also great for training.

To open the VBA Window press Alt + F11.

Click the Tools menu and then Options  – see image below.

Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below.

Click OK and the font size will now be increased in the code window.

See below for a comparison between 10 and 14 point.

Must easier to read!

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

Unlocking coloured cells in Excel

Styles and Find solutions

When you create an Excel file that handles inputs it is best practice to colour code the input cells. The colour you choose isn’t important but making sure you use it consistently is. You may need to unlock the input cells if you plan to add sheet protection to the file. Here’s a couple of ways to do that.

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.

Full Screen

To switch to Full Screen mode – great for presentations – use

Ctrl + Shift + F1

Press it again to revert to the normal view.