Data Analysis with Excel - Advanced

Harnessing the Power of Excel

Course level

Advanced

Prerequisites

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheet users and / or have already attended the ‘Data Analysis with Excel – Intermediate’ course. This course is designed for users who use Excel on a regular basis, and are comfortable with using its tools and functions.

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

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

  Course Outline and Objective

  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 is an intense spreadsheet workout with a focus on keeping things dynamic and expandable. Covering structured references, dynamic searching techniques, data modelling, and through the exercises we will use some of the more unexplored functions in Excel.

  Learning outcomes include obtaining a greater understanding of:

  • Advanced listing techniques
    • Real-time data list drop-down menu
    • Dynamic selection tips
  • Facilitating calculations
    • Create dynamic named ranges
    • Build complex nesting formulae
    • Using structured table references
    • A ‘wildcard’ technique
    • Techniques in building robust spreadsheet models
  • Key Functions
    • DSUM; SUMPRODUCT (& building conditions)
    • PMT
    • Lookup: OFFSET / CHOOSE
    • CELL / ROWS
    • GROWTH
    • INDIRECT
  • Techniques for summarising & presenting data
    • Advanced Pivot Table techniques
    • Referencing structured tables inside formulae
    • Fast calculations with Data Tables
  • Further advanced Excel tools & features
    • Use of the ‘What-If Analysis?’ features
    • Data modelling with Scenario Manager
    • Analysis ToolPak: histogram; calculating correlation
  • Excel dashboard – an introduction
    • Understanding what is required on a dashboard
    • Charting techniques
    • Testing the dashboard
    All exercises and project files used on the course will be available to take home.
    

    "A CPD certificate of completion will be provided by BPP Professional Education Limited upon request, following attendance."

    
    

    Course Dates

    Location	Dates	Time	Availability
    London	01 Oct 2020	9:30 AM - 4:30 PM	Spaces	Book Now
    London	17 Nov 2020	9:30 AM - 4:30 PM	Spaces	Book Now
    London	15 Dec 2020	9:30 AM - 4:30 PM	Spaces	Book Now