Learning & Development

Home / Learning & Development / Microsoft Excel Advanced
Microsoft Excel Advanced

Microsoft Excel Advanced

This course is aimed at experienced Excel users who need to create and manipulate more complex models using the advanced features of Excel

Course Objectives:

To create complex models involving multiple linked spreadsheets and files, analysing data using what ifs and scenarios, validating and auditing data and automating common processes

Course Outline:

What’s new in Excel 2016

  • One click forecasting Smart lookup
  • Tell me what you want to do… Quick analysis tools

Review of Intermediate level

  • If Statement / Nested If Absolute cell referencing Named ranges

Lookup and Information Functions

  • Vertical Lookup (VLOOKUP) Horizontal Lookup (HLOOKUP) INDEX , MATCH & OFFSET

Summarising Data with Pivot Tables

  • Inserting calculated fields Changing value field settings Using report filter
  • Changing the scope of the data source Pivot table options
  • Using slicers for effective filtering Using timelines
  • Pivot charts

What If Analysis tools

  • Scenarios Custom views Goal seek Solver
  • Scenario manager Data tables

Advanced Filtering and sorting

  • Text filters
  • Date filters
  • Numeric filters
  • Advanced subtotals

Formulae Auditing Formula View

  • Tracing precedents
  • Tracing dependents
  • Using watch window
  • Go To Special

Protecting and Sharing

  • Sharing a file
  • Tracking changes
  • Applying data validation rules
  • Protecting cells, sheets, files
  • Password protecting a file

Working with Tables

  • Advantages of excel tables Techniques
  • Structured references

Advanced Charts

  • Saving custom charts as templates
  • Applying trend lines
  • Formatting and editing series, plot area, data points
  • New Charts in Excel 2016

Introduction to Macros

  • Displaying the Developer Tab
  • Overview and Purpose of Macros
  • Where to save macros
  • Absolute and relative recording
  • Running macros
  • Assigning to the Quick access toolbar
  • Assigning to shapes or pictures
  • Keyboard shortcuts for macros

Course Summary and Review

  • Recap of topics covered
  • Questions

Time and Date:

  • 23rd June  9.30-12.30pm via Zoom


  • €55 for Members of ISME
  • €65 for Non Members of ISME

Please note: 2 screens are preferable for this training as it leads to a more productive session.

Members please log in to avail of your discount


ISME Skillnet
Course Form