FREE Power Query and Multiple Files

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: July 30, 2020
Time: 1:00 PM AEST
Event: Excel Power Query and Multiple Files
Topic: Power Query and Multiple Files
Public: Public
Registration: Click here to register.

FREE Excel Introduction to Power Query

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: July 29, 2020
Time: 1:00 PM AEST
Event: Introduction to Excel Power Query
Topic: Introduction to Power Query
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 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: July 28, 2020
Time: 1:00 PM AEST
Event: Excel Guidelines
Topic: Excel Design Guidelines
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Yourself 2018

My annual review webinar where I examine four of my Excel Yourself articles from the monthly INTHEBLACK magazine from the past year.

Includes extra content not included in the magazine. Content applies to all Excel versions.

This year’s smorgasbord webinar includes

  • how to handle validations
  • dynamically highlighting the current row in a table (uses a macro)
  • centralising logic – how and why to do it – alternatives to the IF function
  • creating a slope chart (includes a macro to speed up the process)

As always there will be a few more tips and tricks shared in the session.

Date: December 12, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel Yourself 2018
Topic: FREE - Excel Yourself 2018
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: November 22, 2018
Time: 1.00 PM (AEDST)
Event: Webinar - Excel Data Validation
Topic: Excel Data Validation
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Mouse Shortcuts

Following up from the keyboard shortcuts session in October, in November we will look at quick ways to do common tasks with the mouse.

Some of the topics and techniques you will learn include:

  • the various cursor shapes and how to use them correctly
  • right clicking options you may not know exist
  • copying anything quickly with the mouse, even sheets
  • drawing perfect circles and squares
  • lining up charts and other shapes perfectly
  • cool techniques with the Fill Handle
  • something you may never have tried with the right mouse button
  • using the the Office clipboard
  • a trick to use the Series dialog

This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard.

Even if you prefer using the keyboard you might learn a few useful new techniques.

Date: November 13, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel 2016 Mouse Shortcuts
Topic: Excel 2016 Mouse Shortcuts
Public: Public
Registration: Click here to register.

Excel 2013-2016 Advanced PivotTables

 Price: $25 incl GST

Duration: 1 hour

This updated 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: October 24, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel 2013-2016 Advanced PivotTables
Topic: Excel 2013-2016 Advanced PivotTables
Public: Public
Registration: Click here to register.

Excel 2013-2016 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: October 22, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel 2013-2016 PivotTable Essentials
Topic: Excel 2013-2016 PivotTable Essentials
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: October 17, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel 2013-2016 Chart Hacks
Topic: Excel 2013-2016 Chart Hacks
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Keyboard Shortcuts

Yes you my 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: October 18, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel 2016 Keyboard Shortcuts
Topic: Excel 2016 Keyboard Shortcuts
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Keyboard Shortcuts

Yes you my 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: October 16, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel 2016 Keyboard Shortcuts
Topic: Excel 2016 Keyboard Shortcuts
Public: Public
Registration: Click here to register.

Chapter 12 – Charts – part two

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

Duration: 1.0 hour

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

We will continue with Chapter 12 which covers Charts. We are up to page 317 in the printed book. We will complete the chapter in this session.

The chart interface changed a lot in Excel 2013, The book was based on Excel 2010. This session will use Excel 2016, so you can see the new screens and options.

The session will cover

  • Chart basics
  • How to automate charts
  • Handling zeroes and missing data
  • How to create Dual Axis charts
  • Labeling High and Low points
  • Creating a Variance chart
  • Dashboard techniques

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

Date: October 25, 2018
Time: 1:00 PM AEDST
Event: Book Webinar - Chapter 12 - Charts pt 2
Topic: Book Chapter 12
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Conditional Formats

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: September 26, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel 2016 What If Techniques
Topic: Excel What If Techniques
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Conditional Formats

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: September 25, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel 2016 What If Techniques
Topic: Excel What If Techniques
Public: Public
Registration: Click here to register.

Chapter 12 – Charts

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

Duration: 1.0 hour

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

We will start Chapter 12 which covers Charts. We are up to page 299 in the printed book. This chapter will take two sessions to complete.

The chart interface changed a lot in Excel 2013, The book was based on Excel 2010. This session will use Excel 2016, so you can see the new screens and options.

The session will cover

  • Chart basics
  • How to automate charts
  • Handling zeroes and missing data
  • How to create Dual Axis charts
  • Labeling High and Low points
  • Creating a Variance chart
  • Dashboard techniques

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

Date: September 27, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 12- Charts
Topic: Book Chapter 12
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 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: August 23, 2018
Time: 1:00 PM AEST
Event: FREE Webinar - Excel 2016 Conditional Formats
Topic: Excel Conditional Formats
Public: Public
Registration: Click here to register.

Chapter 11 -Part 2 – Formatting

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

Duration: 1.0 hour

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

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

The session will cover

  • Formatting tips and tricks
  • Conditional Formats – built-in
  • Conditional Formats – changing defaults
  • Conditional Formats – formula – based
  • Printing tips

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

Date: August 30, 2018
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 11 Part 2- Formatting
Topic: Book Chapter 11
Public: Private
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 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.

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.