Advanced Microsoft Excel Training

Course Overview

Duration: 1 Day
Pre-requisites: Delegates should have attended an Excel Intermediate and been using Excel for a reasonable length of time. Some typing skill would be an advantage.
Aimed at: Advanced users of Excel who require to consolidate existing knowledge and extend their knowledge to the more useful high end functions in Excel.
Objectives: On successful completion of the course delegates will be able to access advanced Excel features such as lookups and data analysis.

Course Content:

Lesson 1: Pivot Tables

  1. Creating a pivot table
  2. Custom calculations for pivot table and pivot chart data fields

Lesson 2: Auditing

  1. Concept
  2. Finding cells that provide data to a formula
  3. Finding cells that contain a formula related to the cell
  4. Auditing a whole worksheet
  5. Tracing values that create errors in a worksheet
  6. Tracing formulae that create errors in a worksheet
  7. The ISERROR function
  8. Concept

Lesson 3: Validation

  1. Concept
  2. Specify the valid entries for cells
  3. Creating a list of valid entries
  4. Specifying valid entries
  5. Prompt for correct entry
  6. Displaying a warning for an incorrect entry
  7. Types of data validation messages
  8. Check for incorrect entries
  9. Copy data restrictions and messages to other cells
  10. Removing data restrictions and messages from a cell
  11. Change validation definitions for a cell
  12. Exercise

Lesson 4: The Vlookup Function

  1. The Vlookup syntax
  2. Remarks
  3. Examples
  4. The Hlookup function

Lesson 5: Sumif

  1. Exercise

Lesson 6: Countif

  1. Exercise

LESSON 7: CountA

  1. Exercise

Lesson 8: Text Functions

  1. Mid
  2. Examples
  3. Left
  4. Examples
  5. Right
  6. Find
  7. Examples

Lesson 9: Mathematical Functions

  1. Concept
  2. Round
  3. Examples
  4. Roundup
  5. Examples
  6. Rounddown
  7. Examples
  8. Int
  9. Examples

Lesson 10: Goal Seek

  1. Concept
  2. Using goal seek
  3. Goal seek options

Lesson 11: Solver

  1. Concept
  2. Example
  3. The solver parameters dialogue box

Lesson 12: Scenarios

  1. Concept
  2. Defining different scenarios within solver
  3. Using the scenario manager
  4. Creating scenarios using the scenario manager

Lesson 13: Simple Macros

  1. Recording a macro
  2. Recording a macro
  3. Assigning a macro to a button
  4. Creating a new macro for multiple copies.

Lesson 14: nested if

  1. If using and, or, not
  2. Starting with or
  3. If functions using “not”

Further Training:

Excel for Windows Workshops

The above is an outline intended as a guide only; topics may not be covered in this order. Actual course content will be tailored to suit client's needs.

Contact John Cowie on 0141 563 8200 to book your course, or use our enquiry form to send us a message.