Microsoft Office Excel 2002 - Advanced Course Outline

Microsoft Office Excel 2002 - Advanced

SECTION 1: Getting the Most From Your Data

Lesson 1.1: Outlining and Grouping your Data

Using Automatic Outlining

Displaying and Collapsing Levels

Grouping Data Manually

Creating Subtotals

Practice Exercise

Lesson 1.2: Pivot Tables

What is a Pivot Table?

Creating a Pivot Table Frame

Specifying Data in a Pivot Table

Rearranging Pivot Table Data

Practice Exercise

Lesson 1.3: More on Pivot Tables

Modifying Pivot Table Calculations

Formatting a Pivot Table

Refreshing a Pivot Table

Charting a Pivot Table

Creating a Pivot Table Based On Data from an External Database

Practice Exercise

Lesson 1.4: Exploring Scenarios

What Is A Scenario?

Creating a Scenario

Creating a Scenario Summary Report

Saving Multiple Scenarios

Practice Exercise

Lesson 1.5: What if Analysis

Using a One and Two Input Data Table

Using Goal Seek

Using Solver

Practice Exercise

Section 1: Review Questions

SECTION 2: Excel and the Internet

Lesson 2.1 Excel and Hyperlinks

Hyperlinks and The Internet

Inserting Hyperlinks

Modifying Hyperlinks

Using the Web Toolbar

Browsing Hyperlinks in Excel

Practice Exercise

Lesson 2.2 Excel Workbooks as Web Pages

Interactive and Non-Interactive Web Pages

Saving a Workbook as a Web Page

Saving a Worksheet as a Web Page

Saving a Chart as a Web Page

Saving a Range as a Web Page

Practice Exercise

Lesson 2.3 Getting Data from the Internet

Importing From an External Data Source

Creating a Web Query

Saving a Web Query

Refreshing a Web Query

Practice Exercise

Section 2: Review Questions

SECTION 3: Advanced Excel Tasks

Lesson 3.1: Creating and Using Shared Workbooks

Sharing a Workbook

Requesting Reviews

Reviewing a Workbook

Tracking Changes

Merging and Revising a Shared Workbook

Practice Exercise

Lesson 3.2: Visual Basic and Macros

Adding Code to Your Macro

Adding Comments to VBA Code

Declaring Variables

Prompting For User Input

Iteration over a Range

Using If Then Else Statements

Practice Exercise

Lesson 3.3: Advanced File Management

Identifying File Properties

Searching For Files

Protecting Personal Information

Creating Back Up Files

Changing Auto-Recovery Settings

Practice Exercise

Lesson 3.4: Excel Security

Digital Signatures

Macro Security

Protecting Your Workbook with A Password

Practice Exercise

Lesson 3.5: Custom and Advanced Features in Excel

Customizing Excel by Changing Options

Using Custom Auto-fill Lists

Using Detect and Repair

Practice Exercise

Section 3: Review Questions