Microsoft Excel 2013 Expert

Duration: 1 Day
Costs: Open courses: From £245 + VAT
In-house: Price on application - request a quote >>
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 broad range of solutions and considers the importance of design to ensure the model is flexible, accurate, easy to maintain and scalable.
Pre-requisites: 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.

Course Outline

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.

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

In-House Training

If you have a number of people in your organisation with the same training need, in-house training programmes are an ideal solution.

We pride ourselves on delivering training that is relevant to your business – ensuring staff see the benefits and how they can save time and work more efficiently.

We can deliver both tailored training sessions or the more traditional Foundation, Intermediate and Advanced levels.

Delivery Methods

Training can be delivered at your offices or we can help to select a suitable venue. We can deliver training in various different ways with the more popular being:

  • Group classroom training
  • 1-to-1 training sessions to address individual needs
  • Webinars/Online training

Laptop Hire

Laptop hire is available for most of our courses. We provide PC laptops with the appropriate software pre-loaded as well as projectors, mice and power extension leads.

Pre-Course Skills Assessments

Our focus is always on providing the most relevant training for each client. Before any course we offer pre-course assessments in order for us to ascertain your exact requirements, so you don't waste any time or money on training that isn't right for you and your business.

Course Materials

We provide electronic step by step training reference manuals and issue attendance certificates on completion of the training. That way once we finish, you'll still have everything you've learned at your fingertips.

Post Training Support

In order to make sure you get the best service and expertise from TrainingU, we offer unlimited lifetime post training support giving you peace of mind, should you need help with anything.

Open Course Dates

Dates Venue Offer Prices  
7 November 2017 St Albans  £175 £245 Book
Now

We offer with all our public scheduled courses:

  • Free pre-course skills assessment
  • Lunch and refreshments
  • Course reference guide
  • Attendance certificate
  • Lifetime post training support
  • Small groups of up to 8 people

Courses start at 9:30am and finish at 4:30pm

We offer with all our public scheduled courses:

  • Free pre-course skills assessment
  • Lunch and refreshments
  • Printed course reference guide
  • Attendance certificate
  • Lifetime post training support
  • Small groups of up to 8 people

Courses start at 9:30am and finish at 4:30pm