Data analysis with Excel – advanced

Date:
18 September 2019
Time:
9.30am-4.30pm
Location:
London, Birmingham

Member price: £530 + VAT

Non-member price: £624 + VAT


London, 18 September 2019
Birmingham, 16 October 2019
London, 11 December 2019

Email BPP to book


What you will learn

Many of us struggle with the sheer amount of data that is available in this information age. In many cases, reports are built to present the data, but find that the end result still has a limiting structure and usability.

This one day course provides attendees with hands-on techniques with a focus on keeping things dynamic and expandable. Covering structured references, dynamic listing techniques, scenario modelling, and through the exercises we will get to use some of the more unexplored functions and areas in Excel.

Who should attend

The course material includes advanced features of Excel and participants will gain the maximum benefit from this course if they have already attended the Data analysis with Excel – intermediate course and / or are already competent spreadsheet users. This course is designed for users who use Excel on a regular basis, and are looking to further their knowledge in producing more robust spreadsheets.

At a minimum, it is assumed that participants will know how to:

  • Use some of the functions explained on the Intermediate level of this course
  • Navigate confidently in Excel
  • Use absolute cell references (e.g =$A$1)
  • Create and use nested functions, or at least have an understanding of the benefits of nesting functions together.

View full event details

Content

Advanced listing techniques

  • Tricks in creating dependent lists
  • Dynamic selection tips
  • Dynamic extraction of unique values

Facilitating calculations

  • Dynamic and expandable named ranges
  • Multi-nested functions / expandable referencing
  • Structured table references / table nomenclature
  • Aggregate calculation using the ‘wildcard’ technique

Key functions

  • DSUM / SUMPRODUCT / SUMIFS (and building conditions)
  • Advanced uses of: OFFSET / CHOOSE / INDEX / MATCH
  • ROWS
  • INDIRECT

Further advanced techniques for summarising and presenting data

  • Mini Pivot Table reports
  • Fast calculations with Data Tables
  • Data modelling with Scenario Manager
  • Histogram and Pareto Charts

Excel dashboard (extracts)

  • Creative charting techniques
  • Building a visual analysis
  • Tips and tricks in custom formatting

Bonus VBA exercises

  • Apply simple automation to speed up processes

All exercises and project files used on the course will be available to take home. Laptops with Excel 2013 will be provided on the course. The knowledge and skills gained can be used across all modern versions of Excel.

Location

London, Birmingham

Contacts

BPP
Tel: 0330 060 3303
Email: ldicas@bpp.com

Topics

  • Business and Financial management
  • England and Wales
  • Training courses
  • London and Home Counties
  • Birmingham and the Midlands
  • Technology

Previous Page