Microsoft Excel (Advanced)

Course Aims: This course is aimed at people 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: Candidates 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.

Duration: 1 Day

Learning Method: Online class with dedicated tutor

Course Objectives: 

On completion of this course candidates 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 dependants
• Removing arrows
• Evaluating a formula
• Setting manual & automatic calculation

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
• Creating a Pivot Chart
• Formatting a Pivot Chart
• Moving and deleting a Pivot Chart

Using Range Names

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

Protecting Excel Data

• Protecting entire worksheet
• Protecting and unprotecting certain cells
• Protecting worksheets from being inserted, moved, deleted or hidden/unhidden

Using Data Validation

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

Introduction to Basic Macros

• What is a macro?
• How to record a simple macro
• How to run a macro using different methods
• Deleting a macro

Action Planning

Review of Programme