Excel – advanced

A one-day workshop

Most people only use a fraction of Excel’s capabilities. This workshop shows what you’ve been missing!

Objectives

Learning objectives

This course will help participants:

  • Nest formulas
  • Get the most from pivot tables
  • Use conditional formatting
  • Write array formulas
  • Explore the lookup functions
  • Calculate by criteria
  • Use ‘goal seek’ and ‘scenario manager’ for what-if analysis
  • Record macros

Pre-requisites

Pre-requisites

  • Participants are required to be familiar with the ‘Excel – Intermediate’ content before attending this course.
  • Designed for Office 365 versions: 2016, 2013 and 2010

Format

Format

A very practical, interactive one-day session for a maximum group size of 12. Comprehensive materials provided.

Meet the trainer

Expert trainer

Alan is a highly experienced and very popular IT skills trainer. After eighteen years as an employed IT trainer and training manager he went independent in 2013, specialising in Microsoft Office courses, primarily Excel, PowerPoint, Word, Outlook and Access, although he also delivers training in other programs when required. His clients come from all sectors and include such organisations as The Economist, Hyundai, Marston Group, MediaCom, London Borough of Harrow, Paragon Customer Communications, ICP Global Creative, Bywaters, etc, etc.

 

 

See what some of the participants have said about his workshops:

‘Fantastic. Well-paced and easy to follow.’

‘Great experience.’

‘Very personable, relatable, patient and able to present complicated processes in an understandable way.’

‘Friendly and good at checking people are up to speed.’

‘Very good and explained and answered all questions.’

‘Excellent teacher and very intelligent.’

‘Alan explains things very clearly and addresses issues on questions before they are visible. Thank you very much!’

1 Nesting formulas

  • Principles of nesting formulas together
  • Using IF with AND or OR to answer questions
  • Nesting an AND function in an IF
  • Nesting an OR function in an IF

2 Advanced pivot table reports

  • Grouping dates, numerical and text items
  • Running percentage analyse
  • Running analyses to compare data
  • Inserting Field calculations
  • Finishing off with a user-friendly dashboard

3 Advanced conditional formatting

  • Colour table rows based on criteria in it
  • Applying colour to approaching dates
  • Exploring the different rule types

4 Lookup functions

  • Going beyond the VLOOKUP function
  • Lookups that retrieve data from left or right
  • The versatile INDEX and MATCH functions
  • Retrieving data from columns with duplicates

5 Calculate by criteria

  • Using SUMIFS to sum by criteria
  • Finding an average by criteria with AVERAGEIFS
  • Use SUMPRODUCT to multiply then add different values

6 What-if analysis

  • Use Goal Seek to meet targets
  • Forecast reports with the Scenario Manager

7 Recording Macros

  • Macro security
  • Understanding a Relative References macro
  • Recording, running and editing macros
  • Saving files as Macro Enabled Workbooks
  • Introduction to VBA code
  • Making macros available across workbooks
  • Add a macro button to the Quick Access toolbar

Any questions? Please just give us a call on 01582 463463 – we’re here to help!