Microsoft Excel logo

Course duration

(we will customise to your learning requirements and timeframe)

Target audience

This Advanced Excel training course is designed for those who need to know how to use the more advanced formulas and functions in Microsoft Excel such as IF and VLOOKUP. Upon completion of the course, participants will be able to sort and filter data, create Pivot Tables and create and edit Macros.

Download the PDF Course Outline.

Course Objectives

At the end of the course participants will be able to:

  • Use Advanced Excel functions such as IF and VLOOKUP to reduce the chance of data error
  • Sort and Filter large amounts of data quickly
  • Use PivotTables to summarise large amounts of data in lists or tables to spot comparisons or trends such as monthly sales figures
  • Use Goal Seek to analyse data efficiently
  • Record and edit macros to automate repetitive tasks such as formatting, sorting lists or adding subtotals
  • Assign macros to toolbar buttons and menus to make access more convenient

Rules for Creating a Database

  • Elements of a Database
  • Create an Excel Database
  • Do’s
  • Don’t’s
  • Modifying the Database Structure

Database Tools

  • Sorting
  • Filtering in Excel
  • Calculations for Filtered Lists

Calculating your Data

  • Subtotals
  • Pivot Tables
  • Other Features of PivotTables

Advanced Functions

  • What is a function
  • Function Syntax
  • Paste Function Dialog Box
  • Date and Time Functions
  • Text Functions
  • Math & Trig
  • Financial Functions
  • IF Functions
  • IS Functions
  • VLookup Functions
  • HLookup Functions

Goal Seek

  • Identify uses for goal seek
  • How to Use Goal Seek

Macros

  • Identify uses for Macros
  • Record a Macro
  • Run a Macro
  • View the Macro in the Visual Basic Editor