Ian Fraser Consulting banner

ianfraserconsulting - Learning and Development Consultants


Excel Advanced

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

Course Topics

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
  • Logical Functions
  • Is Functions
  • Lookup 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

Pre-requisites

Intermediate Microsoft Excel or have a good understanding of Microsoft Excel.

Course Duration

1 day

Download the course outline in PDF format PDF

 
 
 
   
Copyright © 2008 Ian Fraser Consulting
Last modified: 5 June, 2008