Excel – advanced

Excel – advanced – overview

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

Full details below or download course outline.

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
  • Use text formulas
Pre-requisites
  • Participants need to be familiar with the ‘Excel – intermediate’ content before attending this course
  • Designed for Office 365 versions: 2016, 2013 and 2010
Course format

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

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.’
‘Explains everything well.’
‘Excellent teacher and very intelligent.’
‘Very knowledgeable would love to have him again if there is another course – thank you.’
‘Alan explains things very clearly and addresses issues on questions before they are visible. Thank you very much!’

Excel – advanced – course outline

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 tables

  • Grouping dates, numerical and text items
  • Running percentage analyse
  • Running analyses to compare data
  • Inserting field calculations
  • Creating a user-friendly dashboard
  • Creating a pivot table from multiple tables

3 Advanced conditional formatting

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

4 Array formulas

  • Advantages of writing an array formula
  • Protecting formulas with an array

5 Lookup functions

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

6 Calculate by criteria

  • Calculate by criteria through multiple ranges
  • Writing SUMIFS to sum numbers by criteria
  • Finding an average by criteria with AVERAGEIFS
  • Counting values by criteria with COUNTIFS

7 What-if analysis

  • Meeting targets with goal seek
  • Forecasting with the scenario manager

8 Recording macros

  • Macro security
  • Understanding a relative references macro
  • Recording, running and editing macros
  • Saving files as macro enabled workbooks
  • Saving macros in the personal workbook
  • Managing the personal workbook
  • Introduction to editing VBA code

9 Customising the ribbon

  • Adding groups and buttons to the ribbon
  • Creating a new custom ribbon
  • Creating a new custom group

10 Text formulas

  • Using CONCATENATE to join cells
  • Change case with UPPER, LOWER and PROPER
  • The MID, LEFT, REPT and FIND functions
  • Using TRIM to clean imported data