Webinar – Dynamic Arrays – Advanced Functions
Price: AU$25
This is the fourth in the series and the third to focus on dynamic array functions.
This session requires the subscription version of Excel.
We will examine some advanced functions that allow you to get around a few problems you may face with dynamic arrays. We will also cover some advanced techniques and learn about custom functions.
This session covers
- the SCAN, REDUCE and MAP functions
- using wildcards for totals
- an OR condition technique for SUMIFS
- using the # symbol on the end of functions
- custom functions with and without LAMBDA
As always, I will share a few other tips along the way.
| Date: |
24/04/2024 |
| Time: |
1:00 PM AEST |
| Event: |
Dynamic Arrays - Advanced Functions |
| Topic: |
Dynamic Arrays - Advanced Functions |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinar – Dynamic Arrays – Intermediate Functions
Price: AU$25
This is the third in the series and the second to focus on dynamic array functions.
This session requires the subscription version of Excel.
This session covers.
- new dynamic array functions explained and demonstrated
- LET, LAMBDA, BYROW, BYCOL, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK and HSTACK
- see how to summarise a formatted table
- learn techniques to split and join text
- discover array syntax what it is and how it can be used
- see how to combine multiple ranges in a single formula
As always, I will share a few other tips along the way.
| Date: |
23/04/2024 |
| Time: |
1:00 PM AEST |
| Event: |
Dynamic Arrays - Intermediate Functions |
| Topic: |
Dynamic Arrays - Intermediate Functions |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinar – Dynamic Arrays – Basic Functions
Price: AU$25
This is the second in the series and the first to focus on dynamic array functions.
This session requires the subscription version of Excel.
This session covers.
- new dynamic array functions explained and demonstrated
- SEQUENCE, CHOOSECOLS, CHOOSEROWS, TAKE and DROP
- flexible loan schedule and flexible budget example
- Formatted tables and dynamic arrays
- Old functions that work well with spill ranges
- TRANSPOSE and INDEX
As always, I will share a few other tips along the way.
| Date: |
18/04/2024 |
| Time: |
1:00 PM AEST |
| Event: |
Dynamic Arrays - Basic Functions |
| Topic: |
Dynamic Arrays - Basic Functions |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinar – Introducing Excel’s Form Controls
Price: Free
Duration: 1 hour
Check boxes, option buttons (aka radio buttons), scroll bar (slider) and combo boxes (drop down list) are all easy to use interfaces. You can incorporate them into your spreadsheets to make the user experience as easy and intuitive as possible.
This session will cover.
- creating and using check boxes
- creating and using option buttons (aka radio buttons)
- creating and using a scroll bar (slider)
- creating and using a combo box (drop down list)
- using range names with controls
- building a loan calculator with all four controls
There will also be a few tips and keyboards shortcuts along the way.
| Date: |
13/12/2023 |
| Time: |
1:00 PM AEDST |
| Event: |
FREE - Introducing Excel's Form Controls |
| Topic: |
Excel Form Controls |
| Public: |
Public |
| Registration: |
Click here to register. |
FREE Webinar – Excel Conditional Formats
Conditional formats allow you to have cell formats automatically change based on the value in the cell and other cells. This is great for exception reporting. For example when you open a file all the overdue dates could have a red fill colour based on today’s date and the dates for the coming week will be coloured orange. Some of the techniques covered can also be applied to dashboards.
This session will take you through the basics, as well as an introduction to formula-based formats.
- Data bars
- Creating a progress bar using a Data bar
- Colour scales – traffic light colours
- Amending the default settings – getting the result you want
- Icon sets – icons can be better for colour blind people
- Cell-based rules – make the most of built-in features
- Working with dates automatically
- Formula-based rules – use formulas and functions to gain total control over conditions
As always you may pick up a few other tips along the way.
| Date: |
18/04/2023 |
| Time: |
1:00 PM AEST |
| Event: |
FREE Webinar - Excel Conditional Formats |
| Topic: |
Excel Conditional Formats |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinar – Dashboards #5 – Dynamic Arrays
Price: AU$25
This is the fifth in the series and focuses on creating a dashboard using dynamic arrays.
This session requires the subscription version of Excel.
This session covers.
- Creating and using spill ranges
- New functions SEQUENCE, UNIQUE, SORT, TEXTBEFORE, DROP, TAKE and VSTACK
- Using a single SUMIFS for detail and total calculations
- Handling current and prior year data
- Creating spill ranges that make drive dynamic charts
- Using range names to shorten formulas
- Using chart templates
As always, I will share other tips and tricks along the way.
| Date: |
27/03/2024 |
| Time: |
1:00 PM AEDST |
| Event: |
Dashboards #5 - Dynamic Arrays |
| Topic: |
Dashboards #5 - Dynamic Arrays |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinar – Dashboards #3 – Sparklines and Conditional Formats
Price: AU$25
This session requires the subscription version of Excel.
This is the third in the series and focuses on Sparkline charts 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
- Dynamic arrays
- 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
As always, I will share other tips and tricks along the way.
| Date: |
21/03/2024 |
| Time: |
1:00 PM AEDST |
| Event: |
Dashboards #3 - Sparklines and Conditional Formats |
| Topic: |
Dashboards #3 - Sparkline and Conditional Formats |
| Public: |
Public |
| Registration: |
Click here to register. |
FREE Webinar – Dashboard #1 – Guidelines and Techniques
This is the first session is a series of five webinars on Excel dashboards. The other four 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: |
14/03/2024 |
| Time: |
1:00 PM AEDST |
| Event: |
FREE - Dashboard #1 - Guidelines and Techniques |
| Topic: |
Excel Dashboard Guidelines |
| Public: |
Public |
| Registration: |
Click here to register. |
FREE Webinar – Excel Keyboard Shortcuts
Yes you may be really good with the mouse, but sometimes it is quicker to use the keyboard.
When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I thought I would update my keyboard shortcut webinar and run it this month.
Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:
- wrap text and other useful formats
- applying row and column grouping
- selecting a table quickly
- copying visible cells only
- apply and remove Freeze Panes
- returning after following a hyperlink
- how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
- get the most out of the Tab key
There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.
| Date: |
20/09/2022 |
| Time: |
1:00 PM AEST |
| Event: |
FREE - Excel Keyboard Shortcuts |
| Topic: |
Excel Keyboard Shortcuts |
| 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 10 years ago.
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
As always I will share a few extra tips along the way.
| Date: |
16/08/2022 |
| Time: |
1:00 PM AEST |
| Event: |
FREE - Interactive Excel Charts |
| Topic: |
Interactive Excel Charts |
| 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: |
13/04/2022 |
| 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 #2 – Formulas and Functions
Price: AU$25
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: |
19/03/2024 |
| Time: |
1:00 PM AEDST |
| Event: |
Dashboards #2 - Formulas and Functions |
| Topic: |
Excel Dashboard Formulas |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinar – Excel Payroll Budget
Price: $25 incl GST
Duration : 1h 30 min
This session uses the subscription version of Excel.
This session examines an existing payroll budget structure. Payroll is one of largest expenses in many organisations. Getting it right is an important step in the budget process.
Often the payroll budget is separate to the main budget due to confidentiality issues. This payroll model handles up 250 staff and could be scaled even further.
The session covers
- Global input and assumptions
- Handling oncosts like Super and Payroll Tax
- Including pay increases
- Includes Casuals
- Using check boxes in a budget
- Using Conditional formats for warnings
- Enabling start and end dates for employees
- Handling overtime and annual leave
- Validations
- Functions: INDEX, IF, IFERROR, ROUND, SUMIFS, MATCH, COUNTIFS, SUMPRODUCT
- Using dynamic array formulas
- Using range names
Plus, as always, I will share extra tips along the way.
| Date: |
22/02/2024 |
| Time: |
1:00 PM AEDST |
| Event: |
Excel Payroll Budget - Subscription Version (1hr 30min) |
| Topic: |
Payroll Budget |
| Public: |
Public |
| Registration: |
Click here to register. |
Excel Budget and Power Query
Price: $25 incl GST
Duration: 1 hour
This session requires the subscription version of Excel.
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
- use dynamic arrays to create formulas
- 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 pivot tables 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: |
27/02/2024 |
| Time: |
1:00 PM AEDST |
| Event: |
Excel Budget and Power Query |
| Topic: |
Budget and Power Query |
| Public: |
Public |
| Registration: |
Click here to register. |
Introduction to Power Pivot
FREE
Duration: 1.5 hours
Power Pivot removes the limit of one table, one report for your Pivot Tables. You can combine data from multiple sources into a single report. You can summarise tables in relational databases without involving the database administrator or having to have a View created.
Its a pivot table on steroids (in a good way). Power Pivot can report on virtually any relational data base as well as bringing together data from different sources into a single report. Eg combining a csv file; an Access database and an Excel table.
We will use an Access database in this session. YOU DO NOT NEED TO HAVE ACCESS INSTALLED ON YOUR PC/LAPTOP.
The session will cover
- background information on using relational databases
- creating a PowerPivot report from a large relational database
- adding tables to the data model to expand the reporting possibilities
- techniques to make the report more reader-friendly
- hiding fields to simplify the interface
- using Slicers
- an introduction to DAX
- books and websites to help you learn more
| Date: |
25/11/2021 |
| Time: |
1:00 PM AEDST |
| Event: |
FREE Intro to Power Pivot (1.5 hours) |
| Topic: |
Introduction to PowerPivot |
| Public: |
Public |
| Registration: |
Click here to register. |
Excel PivotTable Essentials
NO CHARGE
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: |
18/11/2021 |
| Time: |
1:00 PM AEDST |
| Event: |
FREE Excel PivotTable Essentials |
| Topic: |
Excel PivotTable Essentials |
| Public: |
Public |
| Registration: |
Click here to register. |
Excel Advanced Range Names
Price: $25 incl GST
Duration: 1 hour
This session covers many advanced techniques. These techniques can be applied to reporting models, dashboards, budgets and forecasts.
The session covers
- 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
- The INDIRECT function and range names
- 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
- Advanced range reference techniques
As always, I will share a few general tips along the way.
| Date: |
23/09/2021 |
| Time: |
1:00 PM AEST |
| Event: |
Excel Advanced Range Names |
| Topic: |
Excel Advanced Range Names |
| Public: |
Public |
| Registration: |
Click here to register. |
FREE – Custom Number Formats
In this webinar we examine Custom Number Formats which hide away at the bottom of the Number Format tab. These custom made formats offer some useful techniques.
They can
- display negatives in red and with brackets
- format mobile phone numbers correctly
- display numbers and text together and still perform calculations
- hide zeroes
- display rounded numbers to thousands and millions
- display the day of the week
- create customised dates
- be saved and be available in new sheets and files using a Template
- be saved as a Style
See examples and demonstrations of many different custom number formats and learn how to create your own.
As always I will share a few other tips.
| Date: |
16/05/2023 |
| Time: |
1:00 PM AEST |
| Event: |
FREE - Custom Number Formats |
| Topic: |
FREE - Excel Custom Number Formats |
| Public: |
Public |
| Registration: |
Click here to register. |
Excel Range Name Essentials
Price: $25 incl GST
Duration: 1 hour
Learn the basics, plus some powerful techniques to make your range names more dynamic. This session has everything you need to get started with using range names effectively.
Even if you have been using range names this session provides some useful shortcuts and techniques that you may not be aware of.
This session covers
- the three ways to create a name (some are much easier than others)
- naming rules and suggestions
- keyboard shortcuts
- name intersections
- formula-based names
- using names with formatted tables
As always, I will share a few general tips along the way.
| Date: |
21/09/2021 |
| Time: |
1:00 PM AEST |
| Event: |
Excel Range Name Essentials |
| Topic: |
Excel Range Name Essentials |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinars – Advanced Excel (3 webinars)
This is a three-part series of live webinars run over three consecutive days.
Each session will be about 1.5 hours long.
The sessions will take you through the following topics
- Advanced summing techniques
- Logic formulas
- INDEX-MATCH combination
- Text functions
- INDIRECT function
- Power Query single, multiple files and merging
- Advanced Pivot Tables
- Relationships
- GETPIVOTDATA function
Along the way there will be lots of shortcuts and advice on creating effective spreadsheets.
| Date: |
14/09/2021—16/09/2021 |
| Time: |
1:00 PM AEST |
| Event: |
FREE - Advanced Excel - 3 webinars |
| Topic: |
Advanced Excel |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinars – Excel Level One Series (3 webinars)
This is a FREE three-part series of live webinars run over three consecutive days.
Each session will be about 1.5 hours long.
The series will take you through the basics of Excel and teach you how to
- set up shortcuts
- use the mouse effectively
- navigate round a file
- create basic formulas and functions
- apply formats
- use shortcut keys to save time
- printing tips
Along the way there will be lots of shortcuts and advice on creating effective spreadsheets.
One registration signs you up for all three sessions. If you can’t make a session its OK, they are all recorded and you get the recording the next day.
| Date: |
27/07/2021—29/07/2021 |
| Time: |
1:00 PM AEST |
| Event: |
FREE Excel Level One - Beginners - 3 webinars |
| Topic: |
Excel Level One Series |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinar – Dashboards #4 – PivotCharts and Slicers
Price; AU$25
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: |
23/04/2020 |
| Time: |
1:00 PM AEST |
| Event: |
Dashboards #4 - PivotCharts and Slicers |
| Topic: |
Dashboards #4 - PivotCharts and Slicers |
| 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: |
15/03/2022 |
| Time: |
1:00 PM AEDST |
| Event: |
FREE - Dashboard #1 - Guidelines and Techniques |
| Topic: |
Excel Dashboard Guidelines |
| Public: |
Public |
| Registration: |
Click here to register. |
Webinar – XLOOKUP Function (1.5 hours)
Price: $25 incl GST
This is a new function that warrants its own webinar. It can replace VLOOKUP and HLOOKUP. It can also replace the INDEX-MATCH combination.
NOTE: You need the subscription version of Excel to use XLOOKUP.
You will
- learn the basics of the XLOOKUP function
- discover how easy it is to handle N/A! errors
- see how to look from the bottom, up
- use XLOOKUP to return a reference
- see how to use XLOOKUP with dynamic arrays
- learn how to return multiple columns
- discover how to do wildcard searches
- see how to select from multiple tables
- learn how to do two-way look ups
- discover how to do two-column lookups
- return multiple rows with the FILTER function
As always, you will discover a few other tips during the session.
| Date: |
29/08/2023 |
| Time: |
1:00 PM AEST |
| Event: |
XLOOKUP Function (1.5hrs) |
| Topic: |
XLOOKUP Function |
| 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 pivot tables 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: |
25/02/2021 |
| Time: |
1:00 PM AEDST |
| Event: |
Excel Budget and Power Query |
| Topic: |
Budget and Power Query |
| Public: |
Public |
| Registration: |
Click here to register. |
FREE Webinar – Excel What If Techniques
In this webinar we will examine Excel’s built-in What-If techniques. This is an introductory session on the topic.
In Excel it is easy to change a few cells and see the results. You can use trial and error to achieve a result. There are a few built-in features that can make this process a lot easier and save you a lot of time. In terms of financial models and budgets you may also want to handle best, worst and base case scenarios. There is also a new feature in Excel 2016 called a Forecast Sheet that can automatically create a forecast for you.
The session introduces the following five topics
- Goal Seek – simple what-if changes
- Solver – advanced what-if analysis
- Scenario Manager – handling different sets of inputs
- Data Tables – single and double variable sensitivity analysis
- NEW – Forecast sheet
As always you may pick up a few other tips along the way.
| Date: |
15/11/2022 |
| Time: |
1:00 PM AEDST |
| Event: |
FREE Webinar - Excel What If Techniques |
| Topic: |
Excel What If Techniques |
| Public: |
Public |
| Registration: |
Click here to register. |
FREE Webinar – Excel Keyboard Shortcuts
Yes you may be really good with the mouse, but sometimes it is quicker to use the keyboard.
When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I thought I would update my keyboard shortcut webinar and run it this month.
Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:
- wrap text and other useful formats
- applying row and column grouping
- selecting a table quickly
- copying visible cells only
- apply and remove Freeze Panes
- returning after following a hyperlink
- how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
- get the most out of the Tab key
There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.
| Date: |
13/10/2020 |
| Time: |
1:00 PM AEDST |
| Event: |
FREE - Excel Keyboard Shortcuts |
| Topic: |
Excel Keyboard Shortcuts |
| Public: |
Public |
| Registration: |
Click here to register. |