Data analysis with Excel - intermediate

Member price for Online Live: £379 + VAT
Non-member price for Online Live: £446 + VAT

Member price for England: £625 + VAT
Non-member price for England: £735 + VAT

Timing: 9.30am-4.30pm

Overview

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

This course contains some more advanced functions and complex nesting techniques which are used to control tables and charts. There are segments where we clean up data and create robust formulas.

Who should attend

Those using Excel on a regular basis wishing to learn more about performing various forms of analysis. Also support staff, finance professionals and analysts wanting to increase their functional knowledge of Excel.

What you will learn

• Time Savers, fast keys
• Pivot Tables & Charts
  • Control a chart via a Pivot Table
  • Summarise data and automate the chart
• Deep dive into the ‘LOOKUP Family’
  • Flexible lookup techniques
  • Understanding the limitations of a VLOOKUP
  • Extensive practice with XLOOKUP
  • Practical solutions for complex data sets
• Functions
  • Logical
  • Conditional & Error
  • Text-string (for manipulation)
  • Nesting (for increased power)
  • Array
• The CSE keystroke method
  • Simplifying IF functions
  • Multi-cell array functions
• Uncover hidden trends in business data
  • The calculative technique using buckets and intervals
  • Complex problem solving
  • Quick remodelling to meet targets
• Bonus topics:
  • Extensive Conditional Formatting
  • Using formula-based rules
  • Homework topics for further study
• Increased automation techniques using macros
  • Applying meaningful macro functionality
  • Automating formulas

All exercises and project files used on the course will be available to take home

Information for Online Live dates

Please read the following guidance carefully, which only applies if you are attending this course online:

• In addition to the Online Classroom, you will need to have your own version of Excel open to complete the exercises
• It does prove to be challenging to follow-on with the exercises using only 1 screen as you will need to be regularly switching between open windows. We recommend to either:
  • Extend from a computer to a 2nd screen - please refer to this link for guidance on how to correctly setup a second monitor https://support.microsoft.com/en-us/help/4340331/windows-10-set-up-dual-monitors or
  • Use 2 computers, or
  • Use a tablet for the online classroom and a computer for the Excel
• Please note that our courses are based on Excel for Windows and it is recommended that you are using at least Excel 2010 or a later version. MAC users are welcome but please note that there may be some tools and functionality that will differ from what is being presented. The presenter will accommodate for MAC versions on the course.

Questions?

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