Aims

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.

Objectives

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 a broad range of solutions and considers the importance of design to ensure the model is flexible, accurate, easy to maintain and scalable.

Prerequisites

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.

Versions

Courses are delivered in 2010, 2013, 2016, 2019 & O365

What will I learn?

Advanced Filtering Database Statistical Functions

  • DSUM, DCOUNT

Array Formulas

  • 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.

Named Ranges

  • Create formula-defined named ranges.
  • Create a dynamically expanding named range.

Further Referencing Functions

  • Techniques for developing complex formulas.
  • Understand how to use to the following functions and why:
    • INDEX, MATCH,
    • COLUMN, ROW,
    • INDIRECT, CHOOSE, OFFSET

Cascading List Boxes

  • Use functions to create dynamically updating cascading Data Validation lists

Form Controls

  • Use form controls such as scroll bars and spinners to visually change worksheet variables.

Dynamic Charts

  • 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

Conditional Formatting

  • 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 Model

  • 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.

How we deliver

Training can be delivered at your offices in either of the following ways:

  • Online

    Online training is ideal if you have staff in different geographical locations who have the same Microsoft or Adobe training requirements.

    Our expert trainers can deliver Watch and Learn sessions to up to 100 people. Alternatively, they can produce an interactive session that allows delegates to complete practical exercises within the Microsoft or Adobe apps of your choosing.

  • Face to face

    Choose from one or half day training sessions or our popular 90-minute Sprint sessions, which can be delivered in your offices or at another location of your choice.

    All we need is a room with a table and chairs and we look after the rest. We can even provide laptops and a projector should you not have spare IT equipment to use.

Sprint learning

Short on time, but still want to boost staff performance?
Book a Sprint Session!

Each Sprint Session follows a 90-minute format for ultra-focused team learning.

Pick a topic, tell us what you want to achieve from your session, and we’ll put together a short, fun, entirely bespoke programme that meets the needs of your team.

Designed to cater for 10-15 delegates at a time

Developed by our team to cover the subject matter of your choice

Delivered online or face-to-face

Stay up to date

Subscribe to our mailing lists:

I would like to receive the following emails:
Mailing lists

We are collecting your email address to send you the emails you select above and we require your consent. By clicking the Subscribe button, we are treating this as your consent. To withdraw your consent, please use the unsubscribe option at the bottom of the received email(s)