Excel Project – Index/Contents sheet

 Price: $25 incl GST

Duration: 1.25 hours

Index or a Contents sheet are required for Excel files that have multiple tabs.

The techniques covered in this session can also be applied to Instruction sheets and other sheets to improve the general navigation around your Excel files.

Six techniques are covered

  • Manual hyperlinks
  • Using cell links to navigate
  • Applying the HYPERLINK function
  • Using range names (including an unbreakable hyperlink)
  • Macros
  • Using an old Excel macro plus new dynamic arrays

As always lots of shortcuts will be shared along the way.

Date: August 26, 2021
Time: 1:00 PM AEST
Event: Excel Project - Index Sheet (1.25 hrs)
Topic: Index Sheet
Public: Public
Registration: Click here to register.

Webinars – Excel Level Two – Intermediate Series (3 webinars)

This is a FREE three-part series of live webinars run over three consecutive days.

Each session will be about 1.5 hours long.

The sessions will take you through the following topics

  • Format as table – why and how to use it
  • Filtering – tips and tricks
  • Sorting – tips, trick and traps
  • Data Validation – limiting entries in cells
  • Custom Lists – save time and effort with commonly used lists
  • Introduction to Pivot Tables – creating reports with no formulas
  • Slicers – how to filter a Pivot Table
  • Functions – SUM, SUMIFS, SUBTOTAL, COUNTIFS, IF, VLOOKUP, IFERROR
  • Conditional formats – formats that automatically update
  • Introduction to Charts and Sparklines – tips and tricks on creating useful graphs

Along the way there will be lots of shortcuts and advice on creating effective spreadsheets.

Date: August 17, 2021—August 19, 2021
Time: 1:00 PM AEST
Event: FREE Excel Level Two - Intermediate - 3 webinars
Topic: Excel Level Two Series
Public: Public
Registration: Click here to register.

Excel COUNTIFS Function

Price: $25 incl GST

Duration: 1 hour

The COUNTIFS function has been around for many versions.

COUNTIFS offers the ability to perform single or multiple criteria counting. Including using wildcards like * and ? in the criteria to represent unknown characters.

It offers different techniques to determine important results like

  • Counting positives or negatives
  • Determining if a list contains duplicates
  • Identifying the first or last occurrence of an entry in a list
  • Comparing two lists
  • Validating an in-cell drop-down list selection
  • Summarising many validations
  • Creating flexible conditional formats
  • Applying filters based on multiple columns

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

Date: August 24, 2021
Time: 1:00 PM AEST
Event: NEW - Excel COUNTIFS Function
Topic: Excel COUNTIFS function
Public: Public
Registration: Click here to register.

Webinars – Excel Level One Series (3 webinars)

This is a FREE three-part series of live webinars run over three consecutive days.

Each session will be about 1.5 hours long.

The series will take you through the basics of Excel and teach you how to

  • set up shortcuts
  • use the mouse effectively
  • navigate round a file
  • create basic formulas and functions
  • apply formats
  • use shortcut keys to save time
  • printing tips

Along the way there will be lots of shortcuts and advice on creating effective spreadsheets.

One registration signs you up for all three sessions. If you can’t make a session its OK, they are all recorded and you get the recording the next day.

Date: July 27, 2021—July 29, 2021
Time: 1:00 PM AEST
Event: FREE Excel Level One - Beginners - 3 webinars
Topic: Excel Level One Series
Public: Public
Registration: Click here to register.

FREE Excel Introduction to Power Query

Duration: 1 hour

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 – converting a report layout into a table layout
  • converting a MYOB report into a data table
  • capture header information in a column

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

 

Date: May 13, 2021
Time: 1:00 PM AEST
Event: FREE - Introduction to Excel Power Query
Topic: Introduction to Power Query
Public: Public
Registration: Click here to register.

Webinar – Dashboards #4 – PivotCharts and Slicers

Price; AU$25

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

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

Date: April 23, 2020
Time: 1:00 PM AEST
Event: Dashboards #4 - PivotCharts and Slicers
Topic: Dashboards #4 - PivotCharts and Slicers
Public: Public
Registration: Click here to register.

Excel Budget and Power Query

 Price: $25 incl GST

Duration: 1 hour

Discover how to use Power Query to solve two major issues in multi file budget systems. Example uses a P&L budget.

If you have a Jul-Jun financial year you might want to get a head start on next year’s budget/forecast.

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 pivot tables and formula-based reports
  • how to 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.

 

Date: February 25, 2021
Time: 1:00 PM AEDST
Event: Excel Budget and Power Query
Topic: Budget and Power Query
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Yourself 2020 – Part 2

Each year I run a webinar to review a few of my magazine articles from the year and go into a bit more depth.

This year, in the spirit of it being a different year, I thought I would run two webinars.

Part 1 – this one on existing Excel features that everyone should have and
Part 2 – a session on new features, only in the subscription version of Excel

In this session we will cover

  • introduction to dynamic arrays
  • dynamic array function s UNIQUE and FILTER
  • more dynamic array functions SORT, SORTBY, SEQUENCE and RANDARRAY
  • XLOOKUP function

I may also share a few bonus tips and tricks

Date: December 17, 2020
Time: 1:00 PM AEDST
Event: FREE - Excel Yourself 2020 - Part 2
Topic: FREE - Excel Yourself Review Pt 1
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Keyboard Shortcuts

Yes you may be really good with the mouse, but sometimes it is quicker to use the keyboard.

When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I thought I would update my keyboard shortcut webinar and run it this month.

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.

 

Date: October 13, 2020
Time: 1:00 PM AEDST
Event: FREE - Excel Keyboard Shortcuts
Topic: Excel Keyboard Shortcuts
Public: Public
Registration: Click here to register.

FREE Webinar – Power BI Get Data

This session focuses on using the Get Data option in Power BI which is the same as Power Query in Excel.

This session is based on an Excel Power Query session I ran last year and it uses Power BI to import the data.

You need to have Power BI Desktop installed (its free) before the session. This may involve your IT section, so don’t leave it too late. Download link below.

https://powerbi.microsoft.com/en-us/downloads/

 

This session will cover

  • fixing dates so they are recognised as date
  • 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
Date: October 15, 2020
Time: 1:00 PM AEDST
Event: FREE - Power BI Get Data
Topic: FREE - Power BI
Public: Public
Registration: Click here to register.

Macros 4 – Automating Macros with Events

Price: $25 incl GST

Duration: 1 hour

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.

Date: September 17, 2020
Time: 1:00 PM AEST
Event: Macros 4 – Automating Macros with Events
Topic: Macros
Public: Public
Registration: Click here to register.

Excel Project – Index/Contents sheet

 Price: $25 incl GST

Duration: 1.25 hours

Index or a Contents sheet are required for Excel files that have multiple tabs.

The techniques covered in this session can also be applied to Instruction sheets and other sheets to improve the general navigation around your Excel files.

Six techniques are covered

  • Manual hyperlinks
  • Using cell links to navigate
  • Applying the HYPERLINK function
  • Using range names (including an unbreakable hyperlink)
  • Macros
  • Using an old Excel macro plus new dynamic arrays

As always lots of shortcuts will be shared along the way.

Date: August 27, 2020
Time: 1:00 PM AEST
Event: Excel Project - Index Sheet (1.25 hrs)
Topic: Index Sheet
Public: Public
Registration: Click here to register.

Webinar – Macros 2 – Recorded To Remarkable (1.5 hours)

Price: $25 incl GST

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.

Session includes a manual, 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.

Date: August 18, 2020
Time: 1:00 PM AEST
Event: Macros 2 – Recorded To Remarkable (1.5 hours)
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Macros 8 – VBA Variables (1.25 hrs)

Price: $25 incl GST

Duration: 1 hour and 15 minutes

This session covers variables in depth and expands on previous sessions.

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

 

Date: August 25, 2020
Time: 1:00 PM AEST
Event: Macros 8 - VBA Variables (1.25 hrs)
Topic: Macros VBA Variables
Public: Public
Registration: Click here to register.

Webinar – Macros 3 – Interaction and Debugging Techniques (1.5 hours)

Price: $25 incl GST

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.

Date: August 20, 2020
Time: 1:00 PM AEST
Event: Macros 3 – Interaction and Debugging Techniques (1.5 hours)
Topic: Macros Interaction
Public: Public
Registration: Click here to register.

FREE Power Query and Multiple Files

Duration: 1 hour

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.

Building on the skills covered in the Introduction session, we will start working with multiple files. For example you may have 12 separate CSV files in a folder. All with the same layout, one for each month of the year. Power Query can import all 12 files as if they were a single file and create a table for the whole year

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

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

Date: May 18, 2021
Time: 1:00 PM AEST
Event: FREE - Excel Power Query and Multiple Files
Topic: Power Query and Multiple Files
Public: Public
Registration: Click here to register.

Excel Chart Hacks

 Price: $25 incl GST

Duration: 1 hour 15 minutes

This session will show you how to achieve improved charts and how to create charts that aren’t built-in to Excel.

Topics covered:

  • using text boxes with charts
  • improving Excel’s default charts – before and after charts
  • Variance chart
  • High – Low Chart (column and line)
  • Conditional colours in a chart
  • Waterfall/Bridge chart
  • Adding banding to a chart
  • handling expanding data ranges
  • Join line charts

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

Date: May 20, 2021
Time: 1:00 PM AEST
Event: Excel Chart Hacks (1.25hrs)
Topic: Excel Chart Hacks
Public: Public
Registration: Click here to register.

Webinar Excel Budget Template (1.5 hrs) – Subscription Version

Price: $25.00 incl GST

Duration: 1.5 hours

This session is based on the subscription version of Excel – if you buy this session you will also get access to the all version recorded session.

In this webinar I will share a budget structure I have used successfully to create a budget that allows a couple of levels. I have updated the template with new dynamic array functionality. This structure has some flexibility and incorporates a number of advanced Excel features that work well together. The budget template is scalable and can be applied to numerous scenarios.

If you have a Jul-Jun financial year you might want to get a head start on next year’s budget/forecast.

The Budget Template session outcomes will include
• discover a scalable, flexible Excel budget structure
• learn formulas that can easily consolidate budgets from multiple sheets, making uploads into accounting systems easier
• find out about a little-used function that can make allocations easier to create
• see how to use tables to add structure and flexibility to your Excel files
• a range name trick that involves formatted tables
• adding validations to your budgets
• using new dynamic arrays within a budget structure

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

This is a longer than normal session (1.5 hours) and covers many advanced topics that can simplify your budget process.

Date: February 18, 2021
Time: 1.00 PM (AEDST)
Event: Excel Budget Template Subscription Version (1.5 hrs)
Topic: Budget Template
Public: Public
Registration: Click here to register.

FREE – Excel Copy and Paste Techniques

In Excel copying and pasting are two of the most performed tasks. Did you know there are many options that can control and speed up your pasting?

This session will focus on the Paste Special dialog plus a little known pasting feature that is great for dashboards.

The session will cover 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 (great for dashboards)
  •  The Office clipboard

The session includes lots of keyboard shortcuts.

Date: January 28, 2021
Time: 1:00 PM AEST
Event: FREE - Excel Copy & Paste Techniques
Topic: Excel Copy and Paste
Public: Public
Registration: Click here to register.