Excel Level 3 - Advanced

Overview

This intensive course will prepare you for your continued lifelong learning of Excel. It will concentrate on functions and formulas and how to manage, manipulate and extract data. It will show you how to create complex formulas. It will teach you how to design and create large databases with inbuilt controls, so that you and your colleagues get the most from Excel.

Participants who attend this Advanced Level course should have a very good knowledge of formulas and should be creating them on a regular basis. You should already understand and use of all the items covered on the Intermediate level course, including the IF and VLOOKUP functions. You should know the difference between Absolute and Relative cell referencing and a knowledge of basic Charts and formatting is advised also.

The course content will include the items listed below. Items not listed here are covered at another level. Some items may not be covered due to time constraints, but all will be covered in the notes.

Items not listed here (including Macros, VBA, Dashboards and Add-Ins) are not covered on this course and are covered on other courses at a higher level.

Syllabus
  • Learn how to nest functions within other functions.
  • Use advanced Lookup functions such as Match and Index
  • Use Data Validation to force compliance with database input standards.
  • Create dynamic dropdown lists.
  • Understand and create Array formulas.
  • Learn the various levels of security to protect a spreadsheet or specific cells within a worksheet.
  • Deal with error messages caused by formulas.
  • Create and use Pivot Tables.
  • Change Field Settings in a Pivot Table.
  • Create formulas in a Pivot Table based on the existing fields.
  • Learn how to Rearrange and Filter Pivot Tables.
  • Group data in Pivot Tables.
  • Use Slicers to create mini dashboards.
  • Learn about automatically updating an refreshing multiple Pivot Tables.
  • Use the following Excel functions at an advanced level:
  • If - Nesting within IF Functions - IF(AND) - IF(OR) - VLOOKUPS within VLOOKUPS - IFERROR - INDIRECT - MATCH - INDEX
  • Review of KEY Intermediate Excel - Level 2 Content.