Aims & Objectives

Our Advanced course is for regular users of Excel who want 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.

Before attending this course attendees should have completed our Excel Intermediate course or have equivalent knowledge. Attendees 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.

*Please note that VBA programming is not covered on this course. See our VBA Programming courses.

 

 

What will I learn?

Not sure what level your staff need?

Our Skills Assessment will help you assess the knowledge of your staff so that you can choose the most relevant course and the appropriate level of training. Or take a look at the course outline below.

Multi-Criteria Statistical Functions

  • Recap on SUMIF, COUNTIF, AVERAGEIF
  • Use SUMIFS, COUNTIFS, AVERAGEIFS

Logical Functions

  • Recap on Single IF Statement
  • Nested IF statements
  • Combine IF statements with AND, OR, NOT functions
  • IFERROR & ISNA

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

Protection

  • Apply cell, worksheet, and workbook protection
  • 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

How We Deliver

We currently deliver all training online to comply with the Government's guidelines.

Online training works well for businesses whose staff are mainly working from home.

It allows us to train all of your staff with the same training needs at the same time. Your people simply log on from home, using details provided in advance by our team.

Choose from Watch and Learn sessions for larger groups or interactive hands on sessions for smaller groups.

When permitted we also offer face to face training at your offices.

All sessions can be customised to a half day or 90 minute sprint session and tailored to fit your needs, covering the topic(s) within a course that are most important to your business.

Stay up to date

Subscribe to our mailing lists:

I would like to receive the following emails:
Mailing lists

We are collecting your email address to send you the emails you select above and we require your consent. By clicking the Subscribe button, we are treating this as your consent. To withdraw your consent, please use the unsubscribe option at the bottom of the received email(s)