Aims

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.

Objectives

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).

Prerequisites

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.

Versions

Courses are delivered in 2010, 2013, 2016, 2019 & O365

What will I learn?

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

Training can be delivered at your offices in either of the following ways:

  • Online

    Online training is ideal if you have staff in different geographical locations who have the same Microsoft or Adobe training requirements.

    Our expert trainers can deliver Watch and Learn sessions to up to 100 people. Alternatively, they can produce an interactive session that allows delegates to complete practical exercises within the Microsoft or Adobe apps of your choosing.

  • Face to face

    Choose from one or half day training sessions or our popular 90-minute Sprint sessions, which can be delivered in your offices or at another location of your choice.

    All we need is a room with a table and chairs and we look after the rest. We can even provide laptops and a projector should you not have spare IT equipment to use.

Sprint learning

Short on time, but still want to boost staff performance?
Book a Sprint Session!

Each Sprint Session follows a 90-minute format for ultra-focused team learning.

Pick a topic, tell us what you want to achieve from your session, and we’ll put together a short, fun, entirely bespoke programme that meets the needs of your team.

Designed to cater for 10-15 delegates at a time

Developed by our team to cover the subject matter of your choice

Delivered online or face-to-face

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)