Chapter 11 – Maintenance

Price: $25 incl GST or free if you own the book

Duration: 1.0 hour

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

We will start Chapter 11 which covers Formatting. We are up to page 255 in the printed book.

The session will cover

  • Formatting tips and tricks
  • Custom Number Formats
  • Merged cells alternative
  • Borders
  • Styles

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

Date: July 27, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 11 - Formatting
Topic: Book Chapter 11
Public: Private
Registration: Click here to register.

Chapter 10 – Maintenance

Price: $25 incl GST or free if you own the book

Duration: 1.0 hour

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

We will start and hopefully finish Chapter 10 which covers maintenance issues in Reporting models. We are up to page 239 in the printed book.

The session will cover techniques that simplify maintaining files. These include

  • using tables
  • automating dates
  • improving the user interface with checkboxes, option buttons and combo boxes

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

Date: June 27, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 10 - Maintenance
Topic: Book Chapter 10
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Copy and Paste Techniques

In Excel copying and pasting is one of the most commonly 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 session includes lots of keyboard shortcuts.

 

Date: June 14, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Copy and Paste Techniques [FULL]
Topic: Excel Copy and Paste
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Copy and Paste Techniques [FULL]

In Excel copying and pasting is one of the most commonly 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 session includes lots of keyboard shortcuts.

 

Date: June 13, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Copy and Paste Techniques [FULL]
Topic: Excel Copy and Paste
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Text Functions

Everyone knows how well Excel handles numbers, but not everyone knows that Excel can work well with text as well.

This session will cover Excel’s text functions and you will learn

  • different techniques to split text
  • techniques to extract text from text
  • how to easily join text
  • techniques for tweaking text for dates, numbers and upper and lower case
  • the formulas for extracting sheet and file names
  • two new functions in Excel 2016
Date: May 21, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Text Functions
Topic: Excel Text Functions
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Text Functions

Everyone knows how well Excel handles numbers, but not everyone knows that Excel can work well with text as well.

This session will cover Excel’s text functions and you will learn

  • different techniques to split text
  • techniques to extract text from text
  • how to easily join text
  • techniques for tweaking text for dates, numbers and upper and lower case
  • the formulas for extracting sheet and file names
  • two new functions in Excel 2016
Date: May 15, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Text Functions
Topic: Excel Text Functions
Public: Public
Registration: Click here to register.

Webinar – Array Formulas

Price $35 incl GST

Array formulas are an advanced topic. They are not for everyone. After macros and pivot tables they are the topic I get asked most about at training courses.

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 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
• dynamic sorting with an array formula
• dynamic extract unique values with an array formula

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

Date: May 28, 2018
Time: 1:00 PM AEST
Event: Webinar - Array Formulas
Topic: Array Formulas
Public: Public
Registration: Click here to register.

Webinar – INDIRECT Function

Price $35 incl GST

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

As always, you will discover a few other tips during the session.

Date: May 25, 2018
Time: 1:00 PM AEST
Event: Webinar - INDIRECT Function
Topic: INDIRECT Function
Public: Public
Registration: Click here to register.

VLOOKUP Function

 Price: $35 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: May 24, 2018
Time: 1:00 PM AEDST
Event: Webinar - VLOOKUP Function
Topic: VLOOKUP Function
Public: Public
Registration: Click here to register.

SUMPRODUCT Function ($35 – 1.5 hours)

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
  • How to perform 2-D summing calculations
Date: May 23, 2018
Time: 1:00 PM AEST
Event: Webinar - SUMPRODUCT Function (1.5 hours)
Topic: SUMPRODUCT Function
Public: Public
Registration: Click here to register.

Excel Logic Formulas

Price $35 incl GST

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

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

Date: May 18, 2018
Time: 1:00 PM AEST
Event: Webinar - Excel Logic Formulas
Topic: Logic Formulas
Public: Public
Registration: Click here to register.

Excel Lookup Formulas

Price $35 incl GST

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.

Date: May 17, 2018
Time: 1:00 PM AEST
Event: Webinar - Excel Lookup Formulas
Topic: Excel Lookup Formulas
Public: Public

https://attendee.gototraining.com/r/3310267062681105154

Summing Functions for Accountants

 Price: $35 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: May 16, 2018
Time: 1:00 PM AEST
Event: Webinar - Summing Functions for Accountants
Topic: Excel Summing Functions
Public: Public
Registration: Click here to register.

Chapter 9 – Range Names – continued

Price: $25 incl GST or $15 if you own the book

Duration: 1.0 hour

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

We will continue with Chapter 9 which covers range names. We are up to page 222 in the printed book.

Range names can improve formula creation and maintenance as well as making your models more structured. The session will cover the basics of creating and using range names.

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

Date: May 29, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 9 - Range Names continued
Topic: Book Chapter 9
Public: Public
Registration: Click here to register.

Power Query and Multiple Files

Price: $35 incl GST

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: April 19, 2018
Time: 1:00 PM AEST
Event: Webinar - Excel 2016 Power Query and Multiple Files
Topic: Power Query and Multiple Files
Public: Public
Registration: Click here to register.

Excel 2016 Power Query Introduction

 Price: $35 incl GST

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: April 16, 2018
Time: 1:00 PM AEST
Event: Webinar - Excel 2016 Power Query Introduction
Topic: Introduction to Power Query
Public: Public
Registration: Click here to register.

Webinar – FREE – Macros 1  – Recording Tips, Tricks and Traps

Price: No Charge

Duration: 1 hour

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. 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 sessions will expand on the techniques taught in this session.

All sessions include bonus macros that you can use straight away to save time and effort.

Date: April 13, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Macros 1- Recording Tips, Tricks and Traps [FULL]
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – FREE – Macros 1  – Recording Tips, Tricks and Traps

Price: No Charge

Duration: 1 hour

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. 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 sessions will expand on the techniques taught in this session.

All sessions include bonus macros that you can use straight away to save time and effort.

Date: April 11, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Macros 1- Recording Tips, Tricks and Traps [FULL]
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – FREE – Macros 1  – Recording Tips, Tricks and Traps

Price: No Charge

Duration: 1 hour

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. 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 sessions will expand on the techniques taught in this session.

All sessions include bonus macros that you can use straight away to save time and effort.

Date: April 12, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Macros 1- Recording Tips, Tricks and Traps [FULL]
Topic: Macros
Public: Public
Registration: Click here to register.

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

Price: $35 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.

All sessions include a manual and 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.

This third webinar in my macro series will demonstrate and explain how to interact with the user and how to debug (remove errors from) your VBA code.

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