Creating Excel Spreadsheets using Industry Best Practice

We have all opened up a colleague’s Excel Spreadsheet and been confronted with an overwhelming feeling of confusion. Several hours later we have either given up on the task, inadvertently broken the spreadsheet or worse we may not know we broke it. This online workshop will give you the skills to tackle these issues.

Excel Online Training Webinars


  • Date: Apr 2nd, 2019
  • Time:12:00pm AEDT
  • Price: $180
  • Skill Level: Int to Advanced
  • Duration: 90 mins
  • CPD hours: 90 mins
  • Live Q&A
  • Instructor Led
  • Class size (max): 25
  • Exercise Materials
  • Great Reviews
  • Recorded Playback

Learning Outcomes in a Nutshell

We will guide you through many Industry Best Practices in order to create spreadsheets that are robust, transparent and easy to use. These simple rules and guidelines will save you many hours of painful searching through formulas to find that hidden error. Your colleagues and future self will be forever grateful!

Course Content

Microsoft Excel Green Logo Spreadsheets are great, but… 

  • Quick history of errors in spreadsheets
  • Principles of good spreadsheets

Microsoft Excel Green Logo The start of the Spreadsheet creation process

  • Planning your spreadsheet
  • Documenting your spreadsheet
  • Make it logical: top-to-bottom, left-to-right

Microsoft Excel Green Logo The Input, Calculation, Output principle

  • Separating your worksheets by function
  • Using cell formatting to distinguish use

Microsoft Excel Green Logo Good modelling techniques and practices

  • Don’t hardcode values in formulas
  • Keeping formulas short and simple (rule of thumb/24 seconds)
  • Using flags instead of nested Ifs
  • Using VLOOKUP instead of nested Ifs
  • Using spaces in formulas to improve readability
  • Avoiding hiding rows and columns
  • Calculating once and use links
  • Avoiding daisy-chaining, link to the source
  • One column = one purpose
  • Alternative to merge cells
  • Formatting your numbers appropriately
  • Formatting your numbers appropriately
  • When to round your figures
  • Be careful selecting multiple sheets
  • Providing titles on Worksheets, Charts and Tables
  • Using hyperlinks between worksheets

Microsoft Excel Green Logo Linking to other workbooks

  • Try to avoid multiple workbooks
  • Using named cells and ranges
  • Documenting relationships
  • Using Import and Export areas

Microsoft Excel Green Logo Capturing errors

  • Formulas and Functions to trap errors
  • Creating a Master Error sheet
  • Creating an error header warning
  • Creating an error header warning

Microsoft Excel Green Logo Protecting your spreadsheets

  • How strong are passwords?
  • Protecting files
  • Creating a Master Error sheet
  • Workbook protection
  • Worksheet protection
  • Hiding worksheets

Microsoft Excel Green Logo Good practices for Visual Basic for Application (VBA) coding

  • What are the pitfalls of VBA
  • Document your code well
  • Option Explicit
  • Protecting VBA code

Microsoft Excel Green Logo Spreadsheet auditing tools

  • Evaluating formulas step-by-step
  • Finding precedents and dependants
  • Using keyboard shortcuts
  • Using Excel’s Go To Special
  • Spreadsheet Compare
  • 3rd Party tools (Australian made)

 

Course Prerequisites
To get the most out of this webinar, make sure you have the software ready to go.

Skill Requirements:

  • Intermediate to Advanced users with knowledge of Excel functions and some basic knowledge of what VBA/Macros are.

Software Requirements:

  • Excel 2007 onwards. Spreadsheet Compare is only available with certain MS Office versions.

Meet our Trainer

Matt Burr is a Business and Data Analyst with extensive experience in both the public and private sector.

After completing a double Bachelor Degree in Economics and Commerce at the Australian National University, Matt spent over 10 years working in the Construction, Defence, Resources and Health sectors before bringing his experience and knowledge to our Canberra venue as the lead Trainer.

Matt brings current technical knowledge and relevant real-world applications which results in great learning outcomes for our students. In the last 6-months Matt has trained over 250 students, many repeat clients, maintaining an excellent student satisfaction rating of 4.7 out of 5.


How our Webinars Work

  • Click Book Now, select your Webinar and Sign Up
  • Receive Email with Login Details
  • 5 Days ahead of course check your login credentials work
  • Day of course Log On to join the Webinar
  • Q&A session will be facilitated and explained by the presenter

EXCEL CONSULTING Microsoft WORKSHOPS


Why train with Excel Consulting?

“We are passionate about Professional Development and continue to invest in our trainers, courses and material so our students can achieve fantastic learning outcomes.”

presentation

Instructor Led

cityscape-2

Remote Login

notebook

Materials

clock_re

Short Sessions

certificate

Expert Trainers

chat

Live Q&A


If you are hungry to learn more then Book Now or check out our Excel Specialist Courses.