Upcoming Webinar Details

May 2018

The currently scheduled webinars are listed below.

All start at 1PM Sydney time and go for an hour except the SUMPRODUCT webinar which goes for 1.5 hours.

To register, click here.

Tuesday Monday 21 May [FINISHED]

Text Functions (FREE) [FULL]

You know how well Excel handles numbers, but not everyone knows that Excel has built-in functions and features to work with text as well.

This session covers Excel’s text functions and features, in it you will learn

  • the different techniques to split text
  • techniques to extract text from text
  • how to easily join text
  • techniques for tweaking text for dates, numbers, upper and lower case
  • the formulas for extracting sheet and file names

As always, I will be sharing a few other tips during the session.

To watch the recording and download the materials click here.

Wednesday 16th May [FINISHED]

Summing Functions for Accountants ($35)

(average feedback score 92%)

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?

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

Thursday 17th May [FINISHED]

Excel Lookup Formulas ($35)

(average feedback score 88%)

This session explains and demonstrates Excel’s lookup functions and formulas.

Discover how you can extract data from data tables using formula. (There’s a lot more to lookups than just the VLOOKUP function!)

  • VLOOKUP and HLOOKUP functions – how to use them and why they don’t always work and what breaks them
  • INDEX-MATCH combination technique – create flexible lookups
  • Error handing – see how to handle the #N/A! error differently from other errors
  • Working with vertical data – discover how to convert vertical data (like addresses) into a table layout
  • Two criteria lookups – learn the formula that can extract data based on two criteria
  • Extracting data with multiple instances of entries – find out how to extract the first, second or third instance from a data table.

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

Friday 18th May [FINISHED]

Excel Logic Calculations ($35)

(average feedback score 97%)

Discover how to add decision making structures into your spreadsheets to make them more flexible.

There’s a lot more to learn than just the IF function.

The session covers

  • using logic calculations without the IF function
  • validations
  • the IF, AND as well as the OR functions
  • nesting IF functions and formula shortening techniques
  • working with parameters for reasonableness checks
  • error handling
  • IS functions

Wednesday 23 May [FINISHED]

SUMPRODUCT Function (1.5 hours) ($35)

(average feedback score 92%)

I am excited about this webinar because it covers my favorite Excel function. SUMPRODUCT is the Swiss Army knife of functions.

It can sum, count and perform criteria-based calculations that normally require special array formulas.

The SUMIF and SUMIFS functions have two important limitations that SUMPRODUCT doesn’t, discover what they are.

The session outcomes will include

  • The limitations of the SUMIF and SUMIFS functions
  • The limitations and advantages of the SUMPRODUCT function
  • Explanation of the standard use of the SUMPRODUCT function
  • Explanation of undocumented techniques of the SUMPRODUCT function
  • How to create powerful multi-criteria calculations without an array formula
  • Learn about array syntax and how you can use it with SUMPRODUCT
  • Discover how you can use INDEX, OFFSET and other functions with SUMPRODUCT

Thursday 24th May

VLOOKUP Function

(average feedback score 87%)

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 & later versions only)

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

Friday 25th May

INDIRECT Function ($35)

(average feedback score 94%)

Few Excel functions warrant their own webinar – INDIRECT does. It can allow you to do things that no other function can do.

It is often the function of last resort as it allows you to use text to create cell references and solve difficult issues.

  • The outcomes from this session will be
  • Learning the basics of the INDIRECT function
  • Discovering a common problem with sheet references and how to fix them
  • Seeing how to extract data from structured and unstructured data
  • Finding out the power and flexibility of using ranges names with the INDIRECT function
  • Learning how to link one data validation list to another

Tuesday 29 May

Introduction to Array Formulas ($35)

(average feedback score 91%)

Array formulas can perform calculations that no other formula can perform. Array formulas can be complex, confusing and difficult to understand. In this session I hope to remove some of the confusion and explain how they work with some useful examples and techniques.

If you use arrays, or have ever wondered what they are, then this session will be useful.

WARNING: You need to have at least intermediate Excel skills to be able to follow this webinar. Whilst I will attempt to simplify the complexity for you, the last two examples in the session have quite complex formulas that may make your head hurt. The session includes

  • advantages and disadvantages of arrays
  • array syntax – what it is and how to use it with other non-array formulas
  • simple single cell array formulas
  • single cell validation formula
  • multi-cell array formulas demonstrated and explained
  • dynamically extract a sorted range using an array formula
  • dynamically extract unique values from a range with an array formula