CPA Australia Live Excel Sessions 2020

Perth (March) and Darwin (April)

Neale Blackwood is running a series of Excel training sessions for CPA Australia in Perth and Darwin in March and April this year. The table below has all the dates, times and topics. The detailed content for each session is listed below the table. (Note: these sessions are all in computer rooms).

Points to note

  • These are all organised by CPA Australia – you need to register and pay through their website – registration links in the table below.
  • There are full and half-day sessions. Please arrive 15 minutes early to set up.
  • Morning and/or afternoon tea is provided. Lunch WILL be provided if you attend BOTH sessions on a day and on the full day session.
  • The price for each half-day session is $220 for CPA members and $253 for non-members (includes GST).
  • The price for each full-day session is $440 for CPA members and $506 for non-members (includes GST).
  • There are 10% early bird discounts available too.
Day/DateTimesTopicVenue / LocationRegistration Links
Mon, 23 March9am to 12:30pmExcel Macros - half dayCliftons, PerthRegister
Mon, 23 March1:30 to 5pmExcel Dashboards - half dayCliftons, PerthRegister
Tue, 24 March9am to 5pmAdvanced Excel - Full dayCliftons, PerthRegister
Tue, 7 April9am to 12:30pmExcel Macros - half dayCrimson Innovations, Winnielie Darwin NTRegister
Tue, 7 April1:30 to 5pmExcel Dashboards - half dayCrimson Innovations, Winnielie Darwin NTRegister
Wed, 8 April9am to 5pmAdvanced Excel - Full dayCrimson Innovations, Winnielie Darwin NTRegister

 

Macros in Excel 2016 (Half-day AM Session)

Excel Versions: Content applies to Windows Excel 2010 and later versions. Apple users will have different shortcuts and may not have the same functionality as Windows.

Excel macros enable you to automate tasks which can speed up your work and allow you to delegate more tasks. Recorded macros are only a small part of macros. There are a many more useful techniques that allow you to handle errors; interact with the user; loop through code and use logic to handle different situations.

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 (Half-day PM Session)

Excel Versions: Content applies to Windows Excel 2010 and later versions. Apple users will have different shortcuts and may not have the same functionality as Windows.

Dashboards allow you to report on multiple metrics in a small area. This session will give you the theories, tools and techniques needed to create effective dashboards in Excel. Two dashboards will be created from scratch.

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.

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

_________________________________________________________________________________

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.

_________________________________________________________________________________

Audience:

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

 


Advanced Excel (Full day)

Versions: Content applies to Windows Excel 2016 and later versions. Most content applies to Windows Excel 2013 and 2010. Apple desktop or laptop users will have different shortcuts and may not have the same functionality as Windows.

Learn techniques that can be applied to reporting, budgeting and forecasting. Discover the secrets to doing any type of conditional summing. Find a flexible alterative to VLOOKUP. See how to create useful charts both built-in and non-standard. Find the best way to get data into Excel and some tricks to using PivotTables. Learn about the function that can perform impossible calculations. See how range names can make creating reports easier.

This full-day course is aimed at intermediate level Excel users who need to create more advanced and automated reports .

The session covers techniques that allow you to automate processes and reduce manual amendments. Discover how to make the most of Excel’s built-in data features (many of them new) that work well with large data sets. Learn how to create more reliable and robust reports.

Topics include

  • SUMIFS function – summing using multiple conditions.
  • SUMPRODUCT function – Excel most flexible and versatile function.
  • INDIRECT function – the function of last resort – create “impossible” calculations.
  • Logic functions – how to build complex decision-making capabilities into your files including the IF, AND, OR and IFS functions.
  • Error handling functions – using IFERROR and IFNA functions to cope with all Excel errors.
  • Lookup functions – a more flexible alternative to VLOOKUP. Find out how to use INDEX and MATCH to create flexible lookups.
  • Power Query – an introduction to Excel’s built-in automated data cleansing & importation feature. Bring in your data and fix dates, formats and other data issues automatically. See Note at bottom about versions and Power Query.
  • 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. Using Slicers for filtering, discover why you should.
  • 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.
  • Range Names – see how to create more robust and flexible formulas using range names. See how range names and formatted table work together. Reduce the use of $ signs in your formulas and create self-documenting formulas.
  • Charts – learn about Excel’s new charts plus a few charts hacks that allow you to create charts that are not built-in to Excel. Discover how to create dynamic charts that automatically expand.
  • 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.
  • Make the most of formatted tables.
  • Create shorter, more readable and understandable 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 specialist charts like waterfalls and variance charts.
  • Build charts that automatically adjust to new data.

_________________________________________________________________________________

Audience:

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