Microsoft Excel Expert
This course is an advanced course and is aimed at those who use Excel regularly. It covers features and functions that tackle issues arising from complex spreadsheet models. As such, delegates will need to have the experience to provide their own context to understand the need to apply these features. This course will also benefit those people who are already using VBA to achieve some of these tasks.
Drawing on the wealth of experience from experienced spreadsheet solution developers, by the end of this course you will be able to apply the tried and tested techniques to combine Excel functions and features to produce dynamic dashboards and fully automated reporting without the need for macros/VBA. The course does however provide the essential techniques for those who need to add considerable power to their VBA solutions. Throughout the course the group will develop a model to consolidate all features from this (and lower level) courses. Features and techniques from the model can be applied to broad range of solutions and considers the importance of design to ensure the model is flexible, accurate, easy to maintain and scalable.
Delegates should be totally comfortable with the content of the ‘Advanced’ course and have no problems using VLookups or the IF function. They will often be data analysts who use Excel as a significant part of their job. Those who have attended the Advanced course should apply and consolidate what they have learnt before attending this course.
Courses are delivered in 2007, 2010, 2013, 2016.
- - DSUM, DCOUNT
- - Understand Array formulas and when/why you should or should not use them.
- - Key concepts.
- - Edit and enter array formulas.
- - SUMPRODUCT with logical expressions as an alternative to array formulas.
- - Create formula-defined named ranges.
- - Create a dynamically expanding named range.
- - Techniques for developing complex formulas.
- - Understand how to use to the following functions and why:
- - INDEX, MATCH,
- - COLUMN, ROW,
- - INDIRECT, CHOOSE, OFFSET.
- - Use functions to create dynamically updating cascading Data Validation lists
- - Use form controls such as scroll bars and spinners to visually change worksheet variables.
- - Use referencing functions and form controls to create dynamic charts.
- - Automatically update/add data to charts.
- - Dynamic series comparisons with form controls; Scroll through a time series.
- - Use logical expressions and referencing formulas to apply dynamic conditional formatting formulas, e.g. automatically highlight weekends and bank holidays on date cells; automatically highlight ranges that are plotted on a dynamic chart.
- - Develop a small model from beginning to end using the full range of functions and features from this course. The model is not restricted to the content of this course but instead pulls in the full functionality of Excel. Design is also considered to allow for developing a flexible and scalable solution.