Excel 97 Expert Specialist

Course Length/Prerequisites

This three-day course requires Windows 95 or Windows NT 4.0 Introduction as well as the Excel 97 User Specialist course. Familiarity with all features covered in the User Specialist course or equivalent experience with the product is a prerequisite. Profound knowledge of basic concepts such as referencing cells and ranges in formulas, formatting spreadsheets, creating charts and setting print options as well as moderate typing skills are required.

Course Objectives

This course is designed for students who wish to prepare for the Excel 97 Expert Specialist exam. The Expert Specialist exam of the Microsoft Office User certification program covers the following skills:
Outline worksheets
Creating and summarizing lists
If functions
Data validation and conditional formatting
Macros
Styles and templates
Importing and exporting data
Sharing and merging workbooks

Upon completion of this hands-on workshop, students will be able to perform the required tasks listed above. Students who wish to achieve certification in the MS Office User program will be prepared to challenge the Expert Specialist exam.

Course Content

Outlining

Definition and Use

Creating and Using an Outline

Printing and Charting Summary Information

Copying Summary Information

Working with a List

Definitions and Use

Creating a List

Editing a List Using the Data Form

Filtering Records

AutoFilter — Top Ten

Custom Filtering Criteria

Guidelines for Using Lists

Sorting

Retrieving External Data

Summarizing Lists

Definition and Use

Subtotals

Creating a Pivot Table

Modifying Pivot Tables

Inserting a Calculated Field in a Pivot Table

Grouping Column/Row Headings

Creating a Pivot Table from an External Database

Working with Multiple Worksheets

Multiple Worksheet Formulas

Group Editing (Selecting Multisheet Ranges)

Consolidating Worksheets

Saving a Workspace File

Advanced Functions

Introduction and Definitions

The Paste Function Button

IF Function

AND and OR Functions

Rounding Errors

ISERROR Function

VLOOKUP Function

COUNTIF and SUMIF Functions

Advanced Functionality

Converting Formulas to Values

Auditing Cells

Data Validation

Conditional Formatting

Data Map

Macros

Recording a Macro

Running a Macro

Relative References in a Macro

Editing Macros

Using Step Into

Deleting Macros

Customizing Toolbars

Assigning Macros to Drawn Objects

Styles

Definitions and Use

Creating, Applying, Modifying and Deleting Styles

Copying a Style between Workbooks

Changing the Normal Style

Templates

Definitions and Use

Creating Templates

Using Templates

Editing Templates

Creating and Using Autotemplates

Other Autotemplates

Excel’s Built-in Templates

Importing and Exporting Data

Importing Data

Exporting Data

Workgroup Functions

Adding Comments to Cells

Sharing Workbooks

Merging Workbooks