CPA - Excel Secrets and Shortcuts

As part of the CPA Congress, Dr Liam Bastick, Director of Corality (Melbourne) was invited to deliver a series of presentations and workshops. These presentations were held in New South Wales, Queensland and Tasmania.

Presentation: Excel Secrets and Shortcuts

In his presentation, “Excel Secrets and Shortcuts”, Liam Bastick explained various tools to save time and increase productivity when working with Excel.

“I’m passionate about best practice financial modelling so it was great fun to present a session like this. I had nearly 100 accountants in the room, laptops at the ready, learning a truck load or new tips and tricks.” - Liam Bastick

Importance of knowing Excel tips and shortcuts

Financial modellers spend considerable time constructing, maintaining and validating models in Excel. To work faster and be more productive, financial modellers should use Excel shortcuts and useful formulae.

Presentation snapshot

The presentation “Excel Secrets and Shortcuts” covers the following topics:

• Keyboard shortcuts
• Formats and styles
• Data validation

Keyboard shortcuts are useful because they enable us to work quicker in Excel.

Styles are predefined formats applied to a cell, and are commonly used to separate various sections of a model (assumptions, headings, sensitivities). This increases the readability and therefore the usability of the model.

Data validation allows us to create lists similar to Drop Down boxes, so that we can restrict the range of entries allowable in cells. This increases data integrity and reduces human errors.

 Screenshot - CPA - Excel Secrets and Shortcuts

Useful functions

• IF
• SUMIF
• SUMPRODUCT
• LOOKUP
• EOMONTH
• INDEX
• MATCH

Key findings

Make sure to:

• Know and use keyboard shortcuts to work faster in Excel
• Use styles to identify different sections of models and increase model usability
• Use data validation to reinforce integrity
• Know how to apply various functions used in lookup and data analysis

Download presentation

Click here to view and download a condensed version of the presentation.

COMMENTS