Data analysis with Excel - intermediate

Event type: Mastercourse

Harness the power of Excel - discover how to:

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

Booking information

CIMA members and students should log in to their MY CIMA account before placing their booking in order to receive the appropriate discounts.

Date Location Price  
12 May 2015 - 09:30 Manchester Price: GBP 599.00
Member price: £539. CDS member price: £415. Plus VAT on all prices.
12 June 2015 - 09:30 London Price: GBP 599.00
Member price: £539. CDS member price: £415. Plus VAT on all prices.
18 September 2015 - 09:30 London Price: GBP 599.00
Member price: £539. CDS member price: £415. Plus VAT on all prices.
20 October 2015 - 09:30 Birmingham Price: GBP 599.00
Member price: £539. CDS member price: £415. Plus VAT on all prices.
2 December 2015 - 09:30 London Price: GBP 599.00
Member price: £539. CDS member price: £415. Plus VAT on all prices.

Who will benefit

  • All who use Excel on a regular basis wishing to learn more about performing various kinds of analysis and exploring more complex functions and calculations.
  • Support staff, finance professionals and analysts wanting to increase their functional knowledge of Excel.

It is recommended that you attend this course before Data analysis with Excel - advanced, as it will familiarise you with many of the core functions and disciplines which are studied in more detail on the advanced course.

What you can gain

  • Apply more complex calculations to your spreadsheets.
  • 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.

These quick tips and tricks will help you to utilise the power of Excel more effectively, enhancing your expertise in:

  •  analysing and extracting data quickly from data sets
  •  a variety of reporting tools, array formulae and CSE keystrokes, nesting functions and data manipulation
  •  the basics of macro automation
  •  some of the more complex and advanced areas and functions within Excel, including complex nesting techniques, performing unconventional and flexible lookups, summarising and analysing trends, and controlling charts via pivot tables
  • writing formulae to develop bespoke conditional formats
  • dealing with spreadsheet errors and automating repetitive tasks.

Outline

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.

See also

Data analysis with Excel - introduction offers an introduction to the topic, while Data analysis with Excel - advanced and  Excel VBA for finance professionals will extend your knowledge. 

General information

CIMA members and students should log in to their MY CIMA account in order to receive the appropriate discounts.

The course starts at 9.30am. Lunch and refreshments are included.

6 CPD hours (where applicable)

CIMA Mastercourses held in partnership with BPP 

Find out more
If you have any queries please email or phone us on +44 (0)845 026 4722.

CIMA on demand
CIMA on demand is a high quality online professional development solution, specifically designed for busy finance and business professionals. Find out more