Data Analysis With Excel - Intermediate

Pricing

Cost for BPP PD Members: £415 + VAT 6 CPD Hours
(£599.00 + VAT for non BPP PD Members Click Here for details of our membership scheme).

Harnessing the Power of Excel

Course level

Intermediate

Course Objective

In preparation for the advanced level of this course, find out how to:

  • interpret, present and search for data from large quantities of information
  • synthesise information into a logical framework for analysis and summarise it into a meaningful format.

Delegates

This course will benefit those using Excel on a regular basis and wanting to learn more about performing various forms of analysis and exploring more complex functions and calculations. Also support staff, finance professionals and analysts wanting to increase their functional knowledge of Excel.

It is strongly recommended that this course is attended prior to Data Analysis with Excel – Advanced as this course prepares delegates in working with many of the core functions and disciplines that are studied in more detail on the advanced course.

Course Content

This one day course offers quick tips and tricks to better utilising the power of Excel. The course involves analysing and extracting data quickly from data sets, using a variety of reporting tools, array formulae and CSE keystrokes, nesting functions, data manipulation, as well as an introduction to macro automation.

This course is very hands-on and explores some of the more complex and advanced areas and functions within Excel, which include complex nesting techniques, performing unconventional and flexible lookups, summarising and analysing trends, and controlling charts via Pivot Tables.

We also write formulae to develop bespoke conditional formats, and learn a few tricks in dealing with spreadsheet errors and automating repetitive tasks.

By the end of the course, delegates will be able to:

  • Apply more complex calculations to their spreadsheets
  • Learn to make quicker decisions to synthesise and summarise lots of information
  • Work with pivot charts and tables
  • Create bespoke formats and write formula-based rules
  • Apply advanced text manipulation and other types of calculations to create error-free spreadsheets
  • Perform various forms of analysis on business data

Learning outcomes include obtaining a greater understanding of:

  • Time savers / fast keys / shortcuts
  • Key functions
    • IF / MAX / IFERROR / ISERROR
    • INDEX / MATCH / OFFSET / LOOKUP / VLOOKUP
    • LEFT / RIGHT / MID / LEN
    • Introduction to ARRAY functions
    • Complex nesting techniques
  • Lookup in-depth
    • Flexible lookup techniques for unconventional datasets
    • Problems and limitations of a VLOOKUP
    • Practical solutions for complex datasets
  • Data manipulation
    • Advanced use of text-string functions
    • Text extraction with nesting
  • Additional forms of analysis
    • Control a chart via a Pivot Table
    • Summarise data and automate chart elements
    • Frequency distribution table
  • Bespoke and extensive conditional formatting
    • Create immediate visuals using Data Bars
    • Writing formulas to control range formatting
  • Introduction to scenario building
    • Experiment with goal seek
    • Introduction to solver analysis
  • Bonus macro and VBA exercises
    • Applying meaningful macro functionality
    • Viewing and simple editing of macro VBA code

    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.

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


    Course Dates

    Location Dates Time Availability  
    London 19 Sep 2017 9:30 AM - 4:30 PM  Limited Book Now
    Birmingham 17 Oct 2017 9:30 AM - 4:30 PM  Spaces Book Now
    London 12 Dec 2017 9:30 AM - 4:30 PM  Spaces Book Now
    London 22 Feb 2018 9:30 AM - 4:30 PM  Spaces Book Now
    London 19 Apr 2018 9:30 AM - 4:30 PM  Spaces Book Now
    Manchester 10 May 2018 9:30 AM - 4:30 PM  Spaces Book Now
    London 07 Jun 2018 9:30 AM - 4:30 PM  Spaces Book Now
    London 20 Sep 2018 9:30 AM - 4:30 PM  Spaces Book Now
    Birmingham 23 Oct 2018 9:30 AM - 4:30 PM  Spaces Book Now
    London 11 Dec 2018 9:30 AM - 4:30 PM  Spaces Book Now

Membership Scheme

You and your firm could be saving £000's on your CPD training. Find out how to join our membership scheme and receive immediate benefits.

Tailored training

We can tailor any of our public courses to meet your needs as well as providing completely bespoke training solutions. Learn more about tailored training.

Special Assistance Policy