What you'll learn
Rapidly increase the speed and efficiency of your work with the shortcuts, tips, tools & techniques used.
Master the key functions in Excel, applying them together and in isolation in a range of commercial contexts.
Utilize Goal Seek, Data Tables and Scenario modelling techniques to answer Management's most important questions.
Harness the power of Pivot Tables to organize, summarize, slice, dice & dissect data...fast!
Format your work to look professional and presentable, gaining trust and usability.
Lookup data with INDEX & MATCH, and learn Excel's new XLOOKUP function.
Unlock the power of SUMIFS & SUMPRODUCT in your summaries, and master IF, AND & OR to reduce risk in your work.
Gain broad, advanced, commercially-orientated knowledge of Excel suited to Accounting & Finance, Analyst, Consultant and Strategy positions, as well as other roles with a commercial focus.
Be able to apply in multiple ways the most useful and relevant Excel functions to your analysis and spreadsheets.
Learn how to construct formula containing multiple functions.
Utilize a range of inbuilt Excel tools in your data analysis and work.
Be able to format your work to look professional and presentable.
Extend your knowledge of Excel functionality relevant to analysis.
Increase the speed with which you work in Excel and produce analysis.
WHAT’S IN THE COURSE?
This course is split into 4 main sections:
Excel Essentials – shortcuts, tips, tools and techniques that will rapidly speed up your work so that you’re operating as fast and efficiently as you can be.
Exploring the Ribbon - what’s in there, what’s important, what’s secondary
Customizing the Quick Access Toolbar to speed up your work
Top keyboard shortcuts for fast navigation and undertaking regular actions
A look at a very powerful add-in (NB: this add-in is available on Windows only, not Mac)
Other tips and techniques to work as efficiently as you can
Functions, Formulae & Calculations – a comprehensive look at the theory, advice and demos of the most important and relevant functions, top tips for formula construction & auditing, and the creation and use of Named Ranges.
Overview of the Function Library
Most useful functions and their application: INDEX, MATCH, INDEX & MATCH vs VLOOKUP, XLOOKUP, SUMIFS, SUMPRODUCT, COUNTIFS, MAX & MIN, RANK, IF, AND, OR, TRUE & FALSE
Formula construction – top tips for building formula involving multiple functions
Formula auditing tools – navigating your calculations and finding errors
Range names – how to create, use and edit to make your work more transparent
Data & Analysis Tools – Excel’s most useful tools – Pivot Tables, Goal Seek and Data Tables – as well as techniques for introducing scenarios into your spreadsheets and modelling.
Pivot Tables - how to create, use and customize
What-If tools – Goal Seek to find inputs
What-If tools – Data Tables to sensitize inputs
Techniques for creating scenarios and flexing inputs
Formatting & Views – ways to make your work presentable, presentational and professional, to increase trust in it and improve its usability.
Importance of formatting your spreadsheets
Cell styles - their use and customizing
Conditional formatting of outputs
Presenting the data you want through Sorting & Filtering
Improving the look of work through Freeze Panes, Grouping & Hiding
Data Validation to make your spreadsheet more robust & usable