FREE Webinar – Excel Designs Guidelines

This webinar has lots of tips and suggestions to help you create better spreadsheets. These are techniques that I have used successfully on a regular basis and can recommend to other users.

The guidelines will help you use and create spreadsheets that

  • are more reliable
  • identify errors
  • are easier to use and understand
  • have more flexible formulas (can be copied to more places)
  • are more structured
  • are easy to navigate

Along the way you’ll pick up shortcuts and quick ways to do many different tasks.

Date: April 18, 2017
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Design Guidelines
Topic: Excel Design Guidelines
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Designs Guidelines

This webinar has lots of tips and suggestions to help you create better spreadsheets. These are techniques that I have used successfully on a regular basis and can recommend to other users.

The guidelines will help you use and create spreadsheets that

  • are more reliable
  • identify errors
  • are easier to use and understand
  • have more flexible formulas (can be copied to more places)
  • are more structured
  • are easy to navigate

Along the way you’ll pick up shortcuts and quick ways to do many different tasks.

Date: April 20, 2017
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Design Guidelines
Topic: Excel Design Guidelines
Public: Public
Registration: Click here to register.

Advanced Range Names

 Price: $35 incl GST

Duration: 1.5 hours

This is one topic that can add both structure and flexibility to your Excel models. These techniques can be applied to most Excel files including reporting models, budgets and financial models. Range names have many advantages. Range names can

  • make formulas faster to create and easier to read, understand and maintain
  • reduce the need for absolute and mixed references (fewer $ signs in formulas)
  • define input areas
  • allow you to easily navigate in a large workbook
  • make working with macros more reliable
  • create dynamic ranges that expand and contract as data is added or removed
  • create robust linking between files

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 will cover

  • 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

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

 

Date: April 27, 2017
Time: 1:00 PM AEST
Event: Webinar - Advanced Range Names (1.5 hours)
Topic: Range Names
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Date and Time Calculations

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

This is a re-run of a session I did in 2014 – I have added a couple of short topics.

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 I will share some other tips along the way.

Date: February 22, 2017
Time: 1:00 PM AEDT
Event: FREE Webinar - Excel Date and Time Calculations
Topic: Excel Yourself 2016
Public: Public
Registration: Click here to register.

FULL – FREE Webinar – Excel Date and Time Calculations

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

This is a re-run of a session I did in 2014 – I have added a couple of short topics.

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 I will share some other tips along the way.

Date: February 15, 2017
Time: 1:00 PM AEDT
Event: FULL - FREE Webinar - Excel Date and Time Calculations
Topic: Excel Yourself 2016
Public: Public
Registration: Click here to register.

Advanced Budgeting Techniques

 Price: $25 incl GST

Duration: 1 hour

If you work on a calendar year it’s nearly that time of the year when your thoughts turn to budgets.

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

Excel is the main application used to create budgets for small to medium organisations. Even large organisations use it as part of their budget process.

You can create flexible budgeting models in Excel by combining its many built-in features with its various functions.This webinar with demonstrate techniques to

  • choose monthly allocation methods on a line by line basis
  • select different inflation rates to apply 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 options
  • create a flexible validation system

These techniques could also be applied to forecasting and financial modelling.

Date: February 16, 2017
Time: 1:00 PM AEDST
Event: Webinar - Advanced Budgeting Techniques
Topic: Advanced Budget Techniques
Public: Public
Registration: Click here to register.

Excel Budget Template (1.5 hrs)

Price: $35.00 incl GST

Duration: 1.5 hours

In this webinar I will share a budget structure I have used successfully to create a budget that allows a couple of levels. 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

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 21, 2017
Time: 1.00 PM (AEDST)
Event: Webinar - Excel Budget Template
Topic: Budget Template
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 pivottables 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 23, 2017
Time: 1:00 PM AEDST
Event: Webinar - Excel Budget and Power Query
Topic: Budget and Power Query
Public: Public
Registration: Click here to register.

Chapter 8 – Lookup Functions – cont’d

Price: $20 incl GST or $10 if you own the book

Duration: 1.0 hour

The ninth in my series of webinars based on my book. The book is the manual.

In this session we will continue working through Chapter 8 which focuses on Excel’s non-summing functions.

If you own the book you can use the answer to a question as the voucher code to attend for $10.

 

 

Date: February 27, 2017
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 8 Lookup Functions
Topic: Book Chapter 8
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Yourself 2016

This is my annual smorgasbord session. 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

As always I will share some other tips along the way.

Date: December 19, 2016
Time: 1:00 PM AEDT
Event: FREE Webinar - Excel Yourself 2016
Topic: Excel Yourself 2016
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Yourself 2016

This is my annual smorgasbord session. 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

As always I will share some other tips along the way.

Date: December 15, 2016
Time: 1:00 PM AEDT
Event: FREE Webinar - Excel Yourself 2016
Topic: Excel Yourself 2016
Public: Public
Registration: Click here to register.

Chapter 8 – Logic and Lookup Functions – cont’d

Price: $20 incl GST or $10 if you own the book

Duration: 1.0 hour

The ninth in my series of webinars based on my book. The book is the manual.

In this session we will continue working through Chapter 8 which focuses on Excel’s non-summing functions.

If you own the book you can use the answer to a question as the voucher code to attend for $10.

 

 

Date: December 20, 2016
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 8 Logic and Lookup Functions
Topic: Book Chapter 8
Public: Public
Registration: Click here to register.

Macros 9 – Report and Add-in Creation

 Price: $25 incl GST

Duration: 1 hour

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.

To see an example of the report code check out a recent blog post. This session will cover adding more features and more reports and how to convert the reports into an Add-in with a simple user interface.

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.

 

Date: November 29, 2016
Time: 1:00 PM AEDST
Event: Webinar - Macros 9 - Reports and Add-in Creation
Topic: Macro Reports and Add-ins
Public: Public
Registration: Click here to register.

Chapter 7 – Summing Functions – cont’d

Price: $20 incl GST or $10 if you own the book

Duration: 1.0 hour

The eighth in my series of webinars based on my book. The book is the manual.

In this session we will continue working through Chapter 7 which focuses on Excel’s summing functions – the backbone of reporting models.

If you own the book you can use the answer to a question as the voucher code to attend for $10.

 

 

Date: November 30, 2016
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 7 Summing Functions
Topic: Book Chapter 7
Public: Public
Registration: Click here to register.

VLOOKUP Function

 Price: $25 incl GST

Duration: 1 hour

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

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

Date: November 22, 2016
Time: 1:00 PM AEDST
Event: Webinar - VLOOKUP Function
Topic: VLOOKUP Function
Public: Public
Registration: Click here to register.

Excel 2010 Dashboard Techniques Part One

 Price: $25 incl GST

Duration: 1 hour

This session uses Excel 2010.

This is the first in a series of webinars on creating dashboards. This session will focus on small, yet useful charts.

Excel is an ideal platform to create dashboards, as it can bring together data from multiple sources to allow you to include financial and non-financial data. The session will include:

  • techniques that allow you to create small charts – the building blocks of dashboards
  • the charts to avoid and their alternatives
  • Sparkline charts (cell-based charts new in Excel 2010) their advantages and limitations
  • techniques that allow you to remove axes, legends and titles
  • re-size and layout out your charts perfectly.

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

 

Date: November 15, 2016
Time: 1:00 PM AEDST
Event: Webinar - Excel 2010 Dashboard Techniques Part One
Topic: Dashboard Charts
Public: Public
Registration: Click here to register.

Excel 2010 Dashboard Techniques Part One

 Price: $25 incl GST

Duration: 1 hour

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? Do yo know their limitations?

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 AGGREGATE function in Excel 2010 that can ignore error cells when summing.

 

 

Date: November 16, 2016
Time: 1:00 PM AEDST
Event: Webinar - Summing Functions for Accountants
Topic: Excel Summing Functions
Public: Public
Registration: Click here to register.

Excel 2013 Dashboard Techniques Part One

 Price: $25 incl GST

Duration: 1 hour

This session uses Excel 2013.

This session will focus on small, yet useful charts.

Excel is an ideal platform to create dashboards, as it can bring together data from multiple sources to allow you to include financial and non-financial data. The session will include:

  • techniques that allow you to create small charts – the building blocks of dashboards
  • the charts to avoid and their alternatives
  • Sparkline charts (cell-based charts new in Excel 2010) their advantages and limitations
  • techniques that allow you to remove axes, legends and titles
  • re-size and layout out your charts perfectly.

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

 

Date: November 17, 2016
Time: 1:00 PM AEDST
Event: Webinar - Excel 2013 Dashboard Techniques Part One
Topic: Dashboard Charts
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Data Handling Tips

I have revamped a session I ran three years ago. Learn the easy way to Filter and Sort. See how to use colours for filtering and sorting. See how versatile Advanced Filtering can be.

“The mind, once expanded to the dimensions of larger ideas, never returns to its original size.” Oliver Wendell Holmes

Expand your ideas about handling data and add some new strings to your bow.

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.

Earn a CPD hour in your lunch hour and learn some useful data handling tips at the same time.

Excel Data Handling Tips

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

 

Date: October 20, 2016
Time: 1:00 PM AEDT
Event: FREE Webinar - Excel Data Handling Tips
Topic: Data Handling Tips
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Data Handling Tips

I have revamped a session I ran three years ago. Learn the easy way to Filter and Sort. See how to use colours for filtering and sorting. See how versatile Advanced Filtering can be.

“The mind, once expanded to the dimensions of larger ideas, never returns to its original size.” Oliver Wendell Holmes

Expand your ideas about handling data and add some new strings to your bow.

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.

Earn a CPD hour in your lunch hour and learn some useful data handling tips at the same time.

Excel Data Handling Tips

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

 

Date: October 18, 2016
Time: 1:00 PM AEDT
Event: FREE Webinar - Excel Data Handling Tips
Topic: Data Handling Tips
Public: Public
Registration: Click here to register.