LearnPro: Mastering Excel 2000

Course description

Overview: Students will learn how to solidify their basic Excel skills and extend their knowledge of Excel by using advanced software features.

Prerequisites: Students should understand the basics of Microsoft Windows 95, 98 or 2000 and Excel 2000. To acquire the Excel basics, students should complete the LearnPro Applying Excel 2000 course or have equivalent knowledge.

Skills List

Students are introduced to the following essential and extended skills in this course:

Adding sheets to a workbook

Annotating a worksheet using Drawing tools

Applying conditional formatting

Attaching a macro to a button on a worksheet

Changing page breaks

Constructing custom filter conditions

Converting workbook data to HTML format

Creating a basic Web page using an Excel file

Creating and using PivotTables

Creating and using styles (for formatting data)

Creating formulas containing 3-D cell references

Creating formulas containing absolute or mixed cell references

Creating formulas involving dates and times

Creating formulas taking into consideration Excel's order of operations

Creating linking formulas

Creating natural-language formulas

Creating nested formulas containing more than one function

Creating simple formulas containing a single function

Entering data into more than one sheet at a time

Entering dates into a worksheet

Entering times into a worksheet

Freezing titles and headings

Generating answers from worksheets

Naming cells

Protecting worksheet data

Saving a file with a password

Setting up criterion ranges

Using Criteria to filter records

Using Data Forms

Using Data Validation

Using Database functions, including DSUM, DCOUNT, and DMAX

Using Excel's auditing features

Using Excel's macro recorder

Using logical operators

Using lookup tables and the Vlookup function

Using Page Break Preview

Using Paste Special

Using the Advanced Filtering feature

Using the AutoFormat feature

Using the Format Painter

Using the future value (FV) function

Using the Goal Seek feature

Using the If function

Using the Outlining feature

Using the payment (PMT) function

Using the Round function

Using the Subtotal feature

Viewing and editing macro code

LearnPro: Mastering Excel 2000 (Continued)

Course content

Project 1: Understanding and Constructing Formulas

Project 2: Absolute References and Rounding

Project 3: Security Considerations

Project 4: Working with Multiple Sheets

Project 5: Using Excel's If Function

Project 6: Using Lookup Tables

Project 7: Working with Dates and Times

Module A: Tools for Managing Large Worksheets

Project A1: Freezing Titles and Headings

Project A2: Outlining

Project A3: PivotTables and Subtotals

Module B: Using Excel as a Design and Presentation Tool

Project B1: Enhancing Data Appearance

Project B2: Converting Excel Data to HTML Format

Module C: Exploring Financial Functions

Project C1: Calculating Repayment on a Loan

Project C2: Calculating Future Value of an Investment

Module D: Using Paste Special and Linking Data Between Files

Project D1: Using Paste Special

Project D2: Linking Files with Formulas

Module E: Using Excel to Design and Manage Databases

Project E1: Working with Database Functions

Project E2: Using Data Forms

Project E3: Filtering Records

Project E4: Data Validation

Module F: Sensitivity Analysis

Project F1: Getting Answers from Worksheets

Project F2: Using the Goal Seek Feature

Module G: Exploring the Basics of Macros

Project G1: Using Macros to Automate Tasks

Project G2: Attaching Macros to Worksheet and Toolbar Buttons