Microsoft Excel 2002 (XP) - Expert

Overview

Analyse, customise and automate Excel spreadsheets to achieve optimum efficiency. Learn about advanced Lookup functions and Macro automation.

Target Audience

This course is aimed at regular users of Microsoft Excel who wish to analyse data and automate and customise aspects of the Excel environment.

Prerequisites

It is essential that course participants have successfully completed the Excel 2002 (XP) Proficient course, or have gained a similar level of knowledge within the work environment. Specifically participants need to be fully conversant in:

  • Formulas and Functions.
  • Absolute and Relative Referencing.
  • Sorting and Filtering.

Objectives

Upon completion users will be able to:

  • Utilise the features of Excel to create powerful and advanced workbooks.
  • Create and apply outlines and views to manage complex workbooks.
  • Manage list data using Database Functions and Pivot Tables to create and retrieve specific data.
  • Use ‘What if’ analysis tools to assist in finding required target values.
  • Customise Excel with user friendly automation tools such as macros and data validation.

Duration

1 day.

Course Content

Advanced Functions

  • Conditional Formula Review
  • Using Lookups Functions
  • Using Index and Match Functions
  • Using Text Functions

Advanced Database Features

  • Dbase Functions
  • Advanced Filter
  • Outlines and Subtotals

Analysis Utilities

  • Goal Seek
  • Data Tables
  • Creating and Editing Pivot Tables and Pivot Charts

Customising Excel

  • Applying Data Validation
  • Using Conditional Formatting
  • Applying Custom Number Formats

Importing and Exporting Data

  • Importing a Text File
  • Importing with Microsoft Query
  • Exporting Data to a File

Macros

  • Recording, Running and Editing Macros
  • Assigning Macros to Keyboard Shortcuts
  • Assigning Macros to Toolbar Buttons
  • Customising Toolbars
  • Creating Custom Buttons with the Forms Toolbar

Back



 
 
 

Download

Download this profile here.
 

What's Next?

Learn to create macros using VBA code to automate and customise Excel. Interact with your user using message and input boxes.
Microsoft Excel 2002 (XP) VBA
 

Also Available

Microsoft Excel 2002 (XP) Core
Microsoft Excel 2002 (XP) Proficient