CPA Regional Victorian Excel Sessions

June 2019

Neale Blackwood is heading over to regional Victoria in 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).

Day/DateStart TimeTopicVenue / LocationLinks
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

(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.


 

Macros in Excel 2016

(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

(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.