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: September 26, 2019
Time: 1:00 PM AEST
Event: Macros 9 - Reports and Add-in Creation
Topic: Macro Reports and Add-ins
Public: Public
Registration: Click here to register.

Webinar – Macros 5 – Function Macros

Price: $25 incl GST

Duration: 1 hour

Excel Function macros allow you to create your own cell-based functions, just like Excel’s built-in functions. These are called User Defined Functions (UDFs) and can be used to simplify complex calculations.

Macro Functions also allow you to create re-useable code which can centralise your business logic and make code maintenance easier.

This session will examine a number of Functions to explain how, why and when to use them.

This session will cover

  • the SUMBOLD UDF – add up bold values
  • the JOIN UDF – joins text togther from a range
  • functions for sheet, file and user names
  • alternatives for the SEARCH and FIND functions
  • the SUMCOLOUR UDF – adds up specific values in cells with a certain fill colour
  • an “Are You Sure?” function
  • a function to identify if a one of a list of words is used in a cell
Date: September 18, 2019
Time: 1:00 PM AEST
Event: Macros 5 - Function Macros
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Macros 6 – Workshop 1

Price: $25 incl GST

Duration: 1 hour

Learn how to bring together many of the techniques from previous sessions to solve real world problems.

A variety of macros are covered showing you different ways to achieve similar outcomes. Lots of tips and tricks along the way.

The four macros covered are

  • conditionally hide rows (different macros shown)
  • conditionally hide columns (also run via an event)
  • clear input cells (different macros shown)
  • convert a MYOB report in to a data table
Date: September 24, 2019
Time: 1:00 PM AEST
Event: Macros 6 - Macro Workshop 1
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Excel Data Validation

Price: $25.00 incl GST

Duration: 1.5 hours

Excel has a built-in Data Validation feature that you can use to limit and control the entries that go into certain cells. You can use this to make sure users comply with your input requirements.

Unfortunately the feature has one failing. Learn what it is and how to handle it.

This session will take you through some useful techniques to create flexible data validations including in-cell drop down lists, as well as how to cope with the major failing of the system.

The session covers

  • setting upper and lower parameters – fixed and flexible
  • creating custom error messages
  • how to validate the validations and show the user when an error has been made
  • creating in-cell drop down lists
  • creating flexible lists that automatically expand and don’t require any complicated formulas
  • how to create dependent drop downs – one drop down affects the entries in a second drop down

As usual I will throw in some useful general tips along the way.

Date: September 25, 2019
Time: 1.00 PM (AEST)
Event: Excel Data Validation
Topic: Excel Data Validation
Public: Public
Registration: Click here to register.

Excel Advanced PivotTables

 Price: $25 incl GST

Duration: 1.5 hours

This session assumes you are already using pivottables. Topics covered include

  • Calculated Fields – what they are and how and why to use them
  • Calculated Items – what they are and how and why to use them
  • Advanced filtering
  • Useful Report Layouts
  • Conditional Formatting
  • Adding relationships so you can report on two tables as once
  • Data normalisation and variance analysis example using Power Query
  • Two handy macros that can speed up pivot table creation

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

Date: September 19, 2019
Time: 1:00 PM AEST
Event: Excel Advanced PivotTables
Topic: Excel Advanced PivotTables
Public: Public
Registration: Click here to register.

Excel PivotTable Essentials

 Price: $25 incl GST

Duration: 1 hour

This updated session will cover the basics of pivot table creation as well as solving some common frustrations. Topics covered include

  • pivot cache what it is and why it is important
  • data rules that make creating pivot tables easier
  • format as table and why you need to use it
  • populating missing entries in the data – manually and with Power Query
  • the basics of creation
  • pivot charts
  • filtering with slicers
  • grouping

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

Date: September 17, 2019
Time: 1:00 PM AEST
Event: Excel PivotTable Essentials
Topic: Excel PivotTable Essentials
Public: Public
Registration: Click here to register.

FREE Webinar – Interactive Excel Charts

Price: No Charge

Duration: 1 hour

My very first webinar was on this topic 7 years ago. I am re-running the session using the latest Excel version with a detailed pdf manual. We will cover form controls plus a few other advanced Excel techniques.

This session covers

  • using controls with charts to make the user experience easier
  • tips for using controls on spreadsheets
  • flexible formula techniques to make reports more dynamic
  • a technique to stop figures plotting on charts
  • using range names with controls and reports
  • general charting tips

 

Date: September 11, 2019
Time: 1:00 PM AEST
Event: FREE - Interactive Excel Charts
Topic: Interactive Excel Charts
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 29, 2019
Time: 1:00 PM AEST
Event: Macros 8 - VBA Variables (1.25 hrs)
Topic: Macros
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: August 27, 2019
Time: 1:00 PM AEST
Event: Macros 4 – Automating Macros with Events
Topic: Macros
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 22, 2019
Time: 1:00 PM AEST
Event: Macros 3 – Interaction and Debugging Techniques (1.5 hours)
Topic: Macros
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 15, 2019
Time: 1:00 PM AEST
Event: Macros 2 – Recorded To Remarkable (1.5 hours)
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.

Session includes bonus macros that you can use straight away to save time and effort.

Date: August 13, 2019
Time: 1:00 PM AEST
Event: FREE Macros 1- Recording Tips, Tricks and Traps
Topic: Macros
Public: Public
Registration: Click here to register.

Excel 2013-2016 Chart Hacks

 Price: $25 incl GST

Duration: 1 hour

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

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

Date: July 25, 2019
Time: 1:00 PM AEST
Event: Excel 2013-2016 Chart Hacks
Topic: Excel 2013-2016 Chart Hacks
Public: Public
Registration: Click here to register.

FREE Webinar – Financial Functions Part 2

Following on from my June webinar, I will cover more financial functions. These are more related to comparing and analysing cash flows. I have also included a couple of requested schedules.

This session will cover the following functions

  • NPV – Net Present Value of regular cashflows (learn to trick to using it correctly)
  • XNPV – Net Present Value of irregular cashflows
  • IRR – Internal Rate of Return of regular periodic cashflows
  • XIRR – Internal Rate of Return of irregular periodic cashflows
  • Discounted Payback period schedule
  • Flexible Loan schedule – handles lump sum payments
Date: July 23, 2019
Time: 1:00 PM AEST
Event: FREE - Excel Financial Functions Part 2
Topic: Financial Functions
Public: Public
Registration: Click here to register.

FREE – Final Book Webinar – Q & A session

Price: Free – open to all

Duration: 1.0 hour

The 25th in my series of webinars based on my book.

Anyone can submit questions (owners of my book get priority) either at registration or via email.

Example data provided will be made generic before being used in the session.

Date: June 13, 2019
Time: 1:00 PM AEST
Event: FREE - Final Book Webinar - Q & A session
Topic: Book Q & A
Public: Public
Registration: Click here to register.

FREE Webinar – Financial Functions Part 1

Excel has many functions dedicated to financial calculations. These functions take three or more arguments and there are a few things you need to know to use them correctly.

Most involve loans or calculations associated with the time value of money.

This session will cover the following functions

  • PV – Present Value
  • PMT – loan payments
  • CUMIPMT – cumulative interest for loan schedules
  • RATE – interest rate
  • FV – future value
  • NPER – number of period

The session finishes with a loan model that calculates the “missing” value for a loan scenario.

Date: June 11, 2019
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Financial Functions Part 1
Topic: Financial Functions
Public: Public
Registration: Click here to register.

Webinar – Dashboards #4 – PivotCharts and Slicers

Price $25 incl GST

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: May 30, 2019
Time: 1:00 PM AEST
Event: Webinar - Dashboards #4 - PivotCharts and Slicers
Topic: Dashboards #4 - PivotCharts and Slicers
Public: Public
Registration: Click here to register.

Webinar – Dashboards #3 – Sparklines and Conditional Formats

Price $25 incl GST

This is the third in the series and focuses on Sparkline charts added in Excel 2010 as well as using Conditional Formatting with dashboards.

Sparkline charts are small charts that are specifically designed for dashboards.

We will add ticks and crosses using conditional formats to identify measures (good and bad) that may need review.

The Chart Data sheet structure has already been populated so we will review some of the formulas used in its creation.

The session includes

  • reviewing report formulas
  • MATCH function
  • SUMPRODUCT function
  • handling high score is bad vs low score is bad in a formula
  • default settings you may need to change for Sparkline charts
  • controlling the display of ticks and crosses
  • formats for dashboards
  • using templates

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

Date: May 29, 2019
Time: 1:00 PM AEST
Event: Webinar - Dashboards #3 - Sparklines and Conditional Formats
Topic: Dashboards #3 - Sparkline and Conditional Formats
Public: Public
Registration: Click here to register.

Webinar – Dashboards #2 – Formulas and Functions

Price $25 incl GST

This is the old school technique of creating dashboards in Excel.

We will create summary reports from the data and from those summary reports we will extract the chart data which we will then base our charts on.

We will look at techniques to create small charts and how we can combine those on a dashboard.

The session includes

  • formula basics
  • formulas that work with dates
  • automating headings
  • SUMIFS function
  • INDEX function
  • formats for dashboards
  • using templates

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

Date: May 9, 2019
Time: 1:00 PM AEST
Event: Webinar - Dashboards #2 - Formulas and Functions
Topic: Array Formulas
Public: Public
Registration: Click here to register.

FREE Webinar – Dashboard #1 – Guidelines and Techniques

This is first session is a series of four webinars on Excel dashboards. The other three sessions are paid sessions.

In this free session we will focus on chart and dashboard guidelines plus some techniques used to create better dashboards.

We will also look at creating a bullet chart which is a better alternative to a gauge or dial chart.

The session will cover

  • general chart guidelines
  • questions to ask yourself to develop better charts
  • best charts to use
  • charts and formats to avoid
  • using text boxes on dashboards
  • how to create small charts
  • chart templates
  • lining up and re-sizing charts
  • useful number formats for dashboard charts
  • using the #N/A error with charts
  • how to create a bullet chart

As always I will share a few other tips.

Date: May 8, 2019
Time: 1:00 PM AEST
Event: FREE Webinar - Dashboard #1 - Guidelines and Techniques
Topic: FREE - Excel Dashboard Guidelines
Public: Public
Registration: Click here to register.