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.

Chapter 8 – Errors and Text Functions – cont’d

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

Duration: 1.0 hour

The eleventh 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. We will start from page 176 in the printed version.

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

 

 

Date: April 28, 2017
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 8 Errors and Text Functions
Topic: Book Chapter 8
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.