Data Analysis With Excel - Intermediate

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