Tailored Courses for IT and Business Professionals

Microsoft Excel 2003 Power User

Objectives

This course explores the most sophisticated and powerful features of Excel and shows delegates how to produce large complex spreadsheets, and manage that complexity.

Duration

One Day

Delegates will learn ...

  • Different ways of customising the excel working environment
  • How to create advanced formulas - for example using IF and VLOOKUP
  • How to create and make use of pivot tables
  • How to create data links between files and between worksheets
  • How to use the outlining and consolidation features
  • How to use the built-in analysis tools
  • How to use the worksheet protection and display options
  • An introduction to VBA macro programming in Excel

Pre-Requisites:

It is recommend that delegate(s) attending this course have a good practical understanding of using Excel including basic formula construction, formatting and editing spreadsheets, equivalent to the Entry level 1 (Guided Tour) course prior to attending this course. This course is suitable for users who have either attended either of the MS Excel Entry level courses or users who are self taught and have been using MS Excel for at least four months and want to explore the more advanced capabilities of MS Excel.

Suggested Next courses

Either: Excel vba Macros Guided Tour

Or: Excel vba Macros Professional

Topics Covered

Customising the work area

  • Working with existing toolbars
  • Creating a new toolbar
  • Creating and using styles
  • Creating templates

Advanced formula construction

  • Nesting functions in cell formulas
  • How to avoid nesting functions
  • The "IF" function
  • The "VLOOKUP" function
  • Error handling and workbook auditing

Pivot tables

  • Creating pivot tables
  • Working with pivot tables
  • Pivot charts

Linking Multiple files

  • Consolidating information
  • Consolidating worksheets
  • Working with workbooks
  • Linking individual cells
  • Workbooks versus links and Workspaces
  • Using 3-D references in Cell formulas

Data and List manipulation

  • Sorting data
  • Using the Auto filter
  • Creating and using outlines

Protect and display options

  • Adding cell comments
  • Protecting worksheet cells
  • Hiding information
  • Creating and displaying custom views

Introducing Excel Analysis Tools

  • Goal Seek and Solver
  • Database functions

Introducing Excel VBA Macros

  • Recording Macros
  • Executing Macros
  • Examples of what you can do with Macros