CPA Regional Victorian Excel Sessions

May and June 2019

Neale Blackwood is heading over to regional Victoria in May and June this year. The table below has all the dates, times and topics. The detailed content for each session is listed below the table.

These are all organised by CPA Australia – you need to register and pay through them. Neale Blackwood has written and will present each session.

All sessions are half-day sessions (3.5 hours). Please arrive 15 minutes early to set up. You will need to bring your own laptop and I recommend an external mouse. Morning or afternoon tea is provided. Lunch WILL be provided if you attend BOTH sessions on a day.

The price for each half-day session is $192.50 for CPA members and $211.75 for non-members (includes GST). There is an 10% early-bird discount for each session if you book more than four weeks ahead of the date.

Day/DateStart TimeTopicVenue / LocationLinks
Tue, 14/05/199 AM
Macros in ExcelLighthouse Theatre, WarrnamboolRegister
Tue, 14/05/191 PMDashboards in Excel Lighthouse Theatre, WarrnamboolRegister
Thu, 16/05/199 AMIntermediate ExcelRydges GeelongRegister
Thu, 16/05/191 PMAdvanced ExcelRydges GeelongRegister
Fri, 17/05/199 AMPower QueryCraig's Hotel, BallaratRegister
Fri, 17/05/191 PMMacros in ExcelCraig's Hotel, BallaratRegister
Mon, 20/05/199 AMIntermediate ExcelComfort Inn Julie-Anna, Bendigo Register
Mon, 20/05/191 PMDashboards in ExcelComfort Inn Julie-Anna, Bendigo Register
Wed, 22/05/199 AMRange Names in ExcelParklake Hotel Pty Ltd, SheppartonRegister
Wed, 22/05/191 PMDashboards in ExcelParklake Hotel Pty Ltd, SheppartonRegister
Fri, 24/05/199 AMPower QueryRydges GeelongRegister
Fri, 24/05/191 PMDashboards in ExcelRydges GeelongRegister
Tue, 18/06/199 AMDashboards in ExcelCentury Inn Hotel, TraralgonRegister
Tue, 18/06/191 PMPower QueryCentury Inn Hotel, TraralgonRegister
Thu, 20/06/199 AMPower QuerySunraysia Institute, MilduraRegister
Thu, 20/06/191 PMMacros in ExcelSunraysia Institute, MilduraRegister

Power Query in Excel 2016

(Ballarat, Geelong, Traralgon, Mildura)

This half-day course covers using the new Power Query feature in Excel.

Note: Power Query is built-in to Excel 2016 and called Get & Transform in the Data ribbon tab. It is a free Microsoft Add-in with its own tab in Excel 2010 and Excel 2013. If you are using Excel 2010 or 2013 you must download and installed the add-in prior to arriving at the session. Do not leave it to the last minute to install as IT staff may need to be involved in the download and installation process.

Power Query automates and simplifies the importation and cleansing of data from other systems and other files. In this session you will learn how to import and cleanse data from Excel tables, CSV files and other Excel files.

This session includes

  • Extracting data from Excel tables, single and multiple CSV and Excel files.
  • Fixing dates, missing data and extracting information from headers.
  • Converting MYOB report – learn the code to convert MYOB reports into a data layout.
  • Merge multiple tables together – add fields from other data tables to your data.
  • Appending tables – adding multiple tables into a single table.
  • Convert report layouts – convert a report layout into a data base structure.
  • Worked example – using centralised data in a budget and then combining multiple budget files into a single budget table.
  • Macro to refresh all queries in a file.

Learning Objectives:

  • Automate extracting and cleansing data from multiple sources.
  • Convert different report formats into table-based data sets.
  • Create a centralised data system for budgets to ensure all users are accessing a single version of the data.
  • Combine multiple budget files with multiple sheets into a single budget data set.

Audience:

Accounting and finance staff who want to automate and speed up importing and cleansing data from other files and systems.


Intermediate Excel 2016

(Geelong, Bendigo)

This half-day course is aimed at helping professionals use Excel to gain a better understanding of their data. Including how to best organise and present their data.

The session covers general guidelines to use when creating spreadsheets plus many tips, tricks and shortcuts to help you save time and make the most of Excel.

Topics include

  • Data Handling – Format as Table. Have your data automatically expand when new rows are added.
  • Filtering/Sorting – see how to arrange your data based on different criteria.
  • Data Validations – limit/control what to user can enter in a cell.
  • Custom Lists – speed up the creation of commonly used lists.
  • PivotTables – an introduction to PivotTables. Simply drag and drop to create a report with no formulas. How to use Slicers to simply filtering your reports.
  • Functions – 3D SUM, SUMIFS, SUBTOTAL, COUNTIF, IF, IFERROR and VLOOKUP functions
  • Conditional Formats – automatically adjust formats based on a cells content. Automatically apply formats that make identifying exceptions and variances easier and improve dashboards. Learn how to amend the default settings to get the format/effect you want.
  • Charts – charts to use and avoid. Before and after examples to improve your charts. What structures to use and guidelines on creating more readable charts.
  • Sparkline charts – small cell-based charts that are quick to create and work well with dashboards.

Learning Objectives:

  • Discover the most flexible structures to use in Excel and how to apply them.
  • Learn how to effectively use Excel’s summing functions.
  • Find out how to summarise large data sets using PivotTables.
  • Learn lookup and error handling functions enabling you to extract specific data from your data tables.
  • Learn better ways to use charts to display your data.

Audience:

Accounting and finance staff who use Excel to analyse and report on data.


Advanced Excel 2016 (includes dashboards)

(Geelong)

  • Bring in your data and fix dates, formats and other data issues automatically. See Note at bottom about versions
  • Advanced PivotTables – performing calculations within a PivotTable. How to use the Show Values As options to automatically display values and percentages. Applying conditional formats to PivotTables.
  • Relationships – how to use Excel like a relational database by using the Relationships feature. This allows you to report on more than one table in a PivotTable.
  • Dashboards – charting; conditional formatting and other techniques to help you create better dashboards
  • Note: Power Query is built-in to Excel 2016 and called Get & Transform in the Data ribbon tab. It is a free Microsoft Add-in with its own tab in Excel 2010 and Excel 2013. If you are using Excel 2010 or 2013 you must download and installed the add-in prior to arriving at the session. Do not leave it to the last minute to install as IT staff may need to be involved in the download and installation process.

Learning Objectives:

  • Learn how to create more flexible summing and lookup formulas.
  • Automatically import and cleanse data in one step.
  • Discover how to report on multiple tables in a single PivotTable plus perform calculations that are commonly done outside the PivotTable.
  • Build an automated dashboard.

Audience:

Accounting and finance staff who want to automate reporting processes or analyse and report on large data sets including creating dashboards.


Macros in Excel 2016

(Warrnambool, Ballarat, Mildura)

This half-day course covers creating Excel macros from scratch. It assumes no prior macro or programming experience. Macros use the programming language VBA (Visual Basic for Applications).

Macros are small programs that can capture and reproduce processes in Excel. This means you can create a macro that will perform tasks that may be repetitive; time consuming or prone to error. Once created the process can be run at the click of a button. Macros can make delegation of tasks much easier.

This session will include

  • Setting things up – what you need to do to prepare for using macros.
  • Terminology – macros are a separate part of Excel and there are new terms and techniques.
  • Recording macros – tips, trick and traps – no coding required.
  • Running macros – run from icons, buttons or shortcut keys.
  • Limitations of recorded macros – find out what recorded macros can and can’t do.
  • Editing Recorded macros – discover how to remove all the limitations of recorded macro by making changes to the recorded code.
  • Speeding up macros – find the code that makes them work even faster.
  • Using variables in your code – simplify your code using variables.
  • Looping – effectively repeat steps multiple times by looping within your code.
  • Logic – use the If statement to have your code handle different situations differently.
  • Interact – learn techniques to interact with the user.
  • Handle errors – learn the 6 lines of code that handle all Excel macro errors.
  • Debugging – you will make mistakes – see how to capture errors and correct them.

Learning Objectives:

  • Create recorded macros.
  • Edit recorded macros.
  • Use variables, looping, logic and error handling in your code.
  • Write code that interacts with the users.
  • Debugging techniques.

Audience:

Accounting and finance staff who want to automate processes, eliminate repetitive tasks, delegate procedures and save time and effort.


Dashboards in Excel 2016

(Warrnambool, Bendigo, Shepparton, Geelong, Traralgon)

This half-day course covers the techniques you need to create dashboard reports in Excel.  The course is a practical, how-to, hands-on session. An intermediate knowledge of Excel is assumed.

Data visualisation theories and recommendations will be discussed.

Three separate dashboards will be built from scratch to demonstrate all the techniques covered in the session.

Note: This is a standalone session and may include topics from other Excel training sessions.

In this course you will learn

  • General visualisation guidelines for charts and dashboards.
  • Excel’s chart interface and associated shortcuts.
  • The best structures to use with charts.
  • The functions you need to master to extract chart data.
  • How to perfectly line up and re-size charts.
  • Charts to avoid and the ones to use.
  • Hacks to add functionality to charts.
  • Tips to reduce chart size and remove clutter.
  • Effective number formats to use with small charts.
  • How to use textboxes and automated headings with dashboards.
  • Using chart templates to speed up creation.
  • Sparkline charts which are cell-base charts ideal for dashboards.
  • Conditional formatting techniques for dashboards.
  • How to use PivotTables and PivotCharts with dashboards.

Learning Objectives:

  • Learn best practice dashboard and visualisation guidelines and techniques.
  • Create a dashboard using Excel formulas, formats and standard charts.
  • Create a dashboard using Sparklines and conditional formats.
  • Create a dashboard using PivotTables, Slicers and Timeline.

Audience:

Accounting and finance staff who want to automate reporting processes and create automated dashboards.


Range Names in Excel 2016

(Shepparton)

This half-day course covers how to use range names to add structure and flexibility into your Excel files. Range names can be used to improve and simplify reports, budget and forecasts. They can make formulas easier to create, read, understand and maintain.

The session will include

  • Rules – there are a few rules when it comes to range names.
  • Suggestions – there are some tricks to creating effective range names.
  • Three ways to create names – learn all three plus how and when to use each one.
  • Name Box – what it is and how to use it.
  • Dynamic names – how to create names that automatically expand as new data is added.
  • Formatted tables – how to use range names with formatted tables to overcome a few of the limitations of formatted tables.
  • Intersections – learn how to create flexible formulas using range intersections.
  • Other uses – creating reliable hyperlinks, and more robust links to external files, creating drop down in cells.
  • Functions – see how use range names with functions – simple and advanced examples.
  • INDIRECT function – perform amazing calculations with the INDIRECT function and range names.
  • Worked example – a reporting model using many of the techniques from the session.

Learning Objectives:

  • Advantages and disadvantages of ranges names
  • Different ways to create range names
  • Using range names with formulas
  • Create flexible reporting, budget and forecast structures using range names
  • Other uses of range names including hyperlink, external linking and flexible drop down lists

Audience:

Accounting and finance staff who want to improve their reporting and budgeting systems and make them more structured and flexible whilst also easier to maintain and change.