Microsoft Excel Advanced

Microsoft Excel Advanced

1 Day

This course is aimed at regular users of Excel who need to broaden their knowledge and develop a structured approach to building more complex formulas. The course also covers many tools and features to help them develop effective worksheet models.


On completion of this course, attendees will be able to: create a variety of advanced statistical, logical and lookup functions; further analyse data with PivotTables; create multiple conditional formats; use data tools to 'clean up' data lists; apply data entry rules and restrictions; apply worksheet/workbook protection; record a basic macro (note: VBA programming is not covered on this course).


Before attending this course, attendees should have either participated in our Excel Intermediate course or have equivalent knowledge. Attendees of this Advanced course must already be confident with: writing formulas; the basics of IF, SUMIF, COUNTIF, LOOKUP functions; creating a basic PivotTable; the basics of the Conditional Formatting feature.


Courses are delivered in 2007, 2010, 2013, 2016.

What will I learn?
Multi-Criteria Statistical Functions
Logical Functions
  • - Recap on Single IF Statement
  • - Nested IF statements
  • - Combine IF statements with AND, OR, NOT functions
Further VLOOKUP Function
  • - Nest VLOOKUPs with IF function
Formula Auditing
  • - Use worksheet auditing tools
  • - Trace dependent and precedent cells
  • - Check for errors
More on Conditional formatting
  • - Manage multiple conditional formats
  • - Logical expressions in Conditional Formatting
Use Data Tools to manipulate data
  • - Split text to columns
  • - Use Text Functions to change display of data
  • - Remove duplicates
Further PivotTables
  • - Manually group items
  • - Automatically Group Date items
  • - Create calculated fields
  • - Create calculated items
  • - Reference cells within a PivotTable
  • - Set PivotTable options
PivotTable - Data Model
  • - Create a PivotTable from multiple Excel tables
Data Validation
  • - Types of validation
  • - Setup rules for data entry
  • - Highlight invalid data
  • - Create drop down lists
  • - Display help and error messages
  • - Apply cell, worksheet, and workbook
  • - Password protect a file
Introduction to the Macro recorder
  • - Macro security
  • - Record a Macro
  • - Assign a Macro to a button, shortcut key and to the Quick Access Bar
  • - Note: This course does not cover VBA programming
Related courses
Run this
course at
your offices
Request a quote
which course
Which level do you need?
Take our online skills assessment
Find out more
Speak to a friendly advisor


0333 344 6630

or visit our

Online Chat