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
Open course dates
Keep me informed of future dates and offers Keep me informed
Dates Venue Price  
22 May 2018 St Albans £245 + VAT Book
18 July 2018 Milton Keynes £245 + VAT Book
2 August 2018 St Albans £245 + VAT Book
29 November 2018 St Albans £245 + VAT Book

We offer with all our public scheduled courses:

  • Free pre-course skills assessment
  • Lunch and refreshments
  • Course reference guide
  • Attendance certificate
  • Lifetime post training support
  • Small groups of up to 8 people

Courses start at 9:30am and finish at 4:30pm

Related courses
Next Available Course Date

22nd May 2018


St Albans


£245 + VAT

Book now View more dates
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