Excel tips and tricks for business series



This series covers the essentials of using Excel in a business context. We look at the core functions and features in Excel that help people build up spreadsheets and solve problems in a manner that is consistent, robust, flexible and transparent.
Back
We start off with functions and basic features in the grid, along with keyboard shortcuts to improve speed and accuracy. Then, we move on to the data analysis and reporting tools, also known as Power Pivot and Power Query, to show how you can easily convert and transform mountains of data into insights and analysis in the space of minutes. Finally, we wrap up by taking a whirlwind tour of the big changes that have taken place in Excel over the last 10 years. For more detailed information, click on the 'Agenda' tab.

Learning outcomes

  • Learn keyboard shortcuts to improve your speed and accuracy
  • Pick up better ways to use formulae to solve common business problems
  • Use Power Pivot to perform analysis over multiple linked datasets
  • Use Power Query to transform your data and reduce your manual workload each month
  • Get an overview of what has arrived into Excel in the last 10 years

Benefits

Attendees will learn how to save time by applying Excel formulae and features more effectively.
Back

SESSION 1: Spreadsheet best practices and fundamentals

This session introduces the notion of good spreadsheet practices, and the fundamental rules and guidelines to follow if you want professional, well-built spreadsheets.

  • Understand the importance of applying good spreadsheet practices
    • Consistency
    • Robustness
    • Flexibility
    • Transparency
  • Learning the importance of having a standardised approach to building spreadsheets in an organisation
     

SESSION 2: Key Excel functions

This session discusses the use of different Excel functions, which ones can be replaced by others, and useful functions you might not be aware of

  • Lookup functions: VLOOKUP, INDEX/MATCH, XLOOKUP, and more
  • Conditional functions: SUMIF(S), COUNTIF(S), IF, etc.
  • Why you should never use VLOOKUP and HLOOKUP
  • Text functions
  • Date functions
  • Useful miscellaneous functions like OFFSET, MOD and others.
     

SESSION 3: Key Excel features

More than just functions, Excel has a range of capabilities that make spreadsheets more functional and easier to use.

  • Working with Tables
  • Using PivotTables and PivotCharts
  • Styles
  • Data Validation
  • Conditional Formatting
  • A (very!) brief introduction to VBA / macros and what to consider if you are going to use them
     

SESSION 4: Using Power Pivot

If you use PivotTables, you need to learn how to use Power Pivot.  Enhance your data analysis capabilities by running your PivotTables over multiple linked datasets.

  • What is Power Pivot, and how does it differ from PivotTables
  • How to get Power Pivot
  • Introduction to the Data Model – the back-end of Excel
  • Importing data into the Data Model
  • Connecting tables to do analysis over multiple dimensions
  • Using disconnected tables to run scenarios
     

SESSION 5: Using Power Query

Power Query has completely changed the way that people perform regular reporting tasks.  If you want to save hours, days each day/week/month that you perform reports, this session is for you!

  • What is Power Query
  • Connecting to data sources
  • Transforming data and recording your steps
  • Merge, append and combine different data sources
     

Session 6: Modern Excel – what you may have missed in the last ten years

Excel has changed a lot over time – if you’re just learning about Power Pivot and Power Query, consider that they’ve been a part of Excel for the better part of 10 years now.  There have been even more recent changes – this session will cover a number of them, in addition to some items outside of, but still within, the Excel ecosystem.

  • Dynamic arrays
  • LET and LAMBDA
  • Analyze Data
  • Data Types
  • Forms
  • Office Scripts
  • Power Automate
Back
Tim Heng

Tim Heng has over 15 years’ experience in developing and reviewing financial models across a range of industries and sectors, throughout Australia and internationally. He works with clients to develop bespoke modelling solutions for business challenges, using a combination of Excel, VBA, Power BI and database analytics.

He has worked across the world, with clients in Australia, Canada, Hong Kong, Indonesia, Japan, Malaysia, New Zealand, Singapore, United Kingdom, United States and Vietnam, as well as numerous projects based throughout Africa. These assignments have involved the development of strategic corporate and project-based financial models, model reviews, developments of dashboards and reports, and training of analysts and senior staff.

Tim has written articles for the CompAct and Modelling Section newsletters published by the Society of Actuaries (US), and has been a regular presenter at conferences, training courses and webinars for Chartered Accountants Australia New Zealand (CAANZ) and CPA Australia. He is recognised by Microsoft as a Most Valuable Professional specialising in Microsoft Excel since 2017.
See MoreSee Less

EVENT DETAILS


Topic: Reporting

Sub-Topic: Financial Reporting, Personal Effectiveness, Solve Problems

Format: Recorded Webinar

Proficiency Level: All

CPD: Upto 9 hours