Preloader spinner

Microsoft Excel Advanced

Image of laptop displaying an image containing the words Microsoft Excel

COURSE AIMS

This course is for attendees who want to expand their knowledge into some of the more advanced functions, analyse / summarise tables of data and automate common tasks.

COURSE PRE-REQUISITES

Attendees who wish to attend this course must have a good knowledge of Excel and be competent at working with formulas using absolute cell references, use basic functions like sum, average, max, min, count and be able to write formulas across multiple sheets and multiple workbooks.

COURSE OBJECTIVES

On completion of this course, delegates will be able to:

  • Use logical, lookup, statistical and error functions
  • Use auditing tools
  • Create range names and use range names in formulas
  • Set restrictions on data entry using data validation
  • Protect an Excel cell, worksheet, and workbook
  • Automate common task by recording a basic macro
  • Create, edit, format  Pivot Tables and Pivot Charts

COURSE CONTENT

Introduction and Objectives

 

Using Logical, Lookup and Statistical Functions

  • Using the VLOOKUP / HLOOKUP functions
  • Using IF, AND & OR function
  • Using the IFERROR function
  • Using COUNTIF & SUMIF

 

Using Auditing Tools

  • Displaying precedents and dependents
  • Removing arrows
  • Evaluating a formula
  • Setting manual & automatic calculation

 

Using Range Names

  • Assigning names to ranges
  • Using range names in formulas
  • Editing and deleting named ranges

 

Using Data Validation

  • Validating data using a data validation list
  • Creating a custom input and error message
  • Editing a validation list
  • Removing data validation

 

Protecting Excel Data

  • Protecting entire worksheet
  • Protecting and unprotecting certain cells within a worksheet
  • Protecting workbook structure

Introduction to Basic Macros

  • What is a macro?
  • How to record macros
  • How to run a macro using different methods
  • Editing and deleting macros

 

Creating / Revising Pivot Tables and Pivot Charts

  • Creating a Pivot Table
  • Adding, moving,removing Pivot Table fields
  • Formatting and structuring a Pivot Table
  • Filtering Pivot Table items
  • Changing the summary function
  • Summarising data as percentages
  • Refreshing a Pivot Table
  • Changing the data source for a Pivot Table
  • Adding a Pivot Table slicer
  • Moving & deleting a Pivot Table
  • Creating more than one pivot table on the same worksheet
  • Creating a Pivot Chart
  • Formatting a Pivot Chart

 

 Hints and Tips

Related Courses

Download PDF Outline

Public Schedule

RRP:  
£225 per delegate
Our price:  
£175 per delegate

Virtual Private Training

Contact us for pricing

Onsite Training

Contact us for pricing

Note

All prices exclude VAT at 20%.

VAT registration number: 450 4347 14

You may like...

Microsoft SharePoint Super User (Site Owner)

This course is aimed at people required to effectively manage SharePoint sites and pages as a Super User (Site Owner).

An icon of a person

N/A

An icon of a clock

2 Days

Microsoft Power Automate Introduction

This 1-day course explores the basic building blocks and allows you to create you own flows and understand how to develop your skills to the next level.

An icon of a person

N/A

An icon of a clock

1 Day

Microsoft Power Apps Introduction

Power Apps is a suite of apps, services, connectors, and a data platform that provides you with an opportunity to build custom apps for your business needs.

An icon of a person

N/A

An icon of a clock

1 Day

Enquire about this course

Microsoft Excel Advanced
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Join our mailing list

Receive details on our new courses and special offers

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.