Course Length: 1 day
Prerequisites: Basic knowledge of Excel 2000
Certification: MOUS 2000
Product code: EXCL2KUP3LG

This one day instructor-led course presents the database management and analysis concepts of Excel 2000. You will be introduced to Excel features and the steps required to use the features correctly. The Learning Guide includes an exercise CD containing the exercise files used in class.

What’s in Excel 2000:Database Management and Analysis?

This course teaches you how to create and sort databases; use database functions, filters, and queries; create and modify PivotTables; audit worksheets; and create scenarios and custom reports.

Why do I need Excel 2000:Database Management and Analysis?

• Provides skills necessary to become MOUS certified.
• Provides students with an overview of Excel 2000 features.
• Provides hands-on experience using Excel 2000.

 

Lesson 1: Working with Databases

Defining Database Components
Adding Database Components
Modifying Database Records

Lesson 2: Sorting Database Data

Sorting Records from Top to Bottom
Sorting Field Data from Left to Right
Sorting by Four or More Columns
Specifying Custom Sort Orders

Lesson 3: Using Database Functions

Using the DAVERAGE Function
Using the DCOUNT and DSUM Functions
Using the DMIN and DMAX Functions
Using the VLOOKUP Function

Lesson 4: Using Filters and Queries

Using the AutoFilter Feature
Using the Advanced Filter Feature
Extracting Data
Working with Extracted Data
Querying an External Database
Working with Queried Data

Lesson 5: Using Automated Features

Using the Automatic Subtotals Feature
Using Conditional Formatting
Using the AutoFormat Feature

 

 

Lesson 6: Using PivotTables

Creating PivotTables
Editing PivotTables
Using PivotTable AutoFormat
Changing PivotTable Field Names and Functions
Formatting Numbers in PivotTable Fields

Lesson 7: Modifying PivotTable Data

Deleting PivotTable Data
Displaying and Hiding PivotTable Data
Refreshing PivotTable Data
Sorting PivotTable Data
Using the GETPIVOTDATA Function

Lesson 8: Using Advanced Data Analysis to Audit Worksheets

Tracing Precedents, Dependents, and Errors
Removing Arrows
Finding Cells with Specific Characteristics or Entries
Using the Goal Seek Feature
Using the Solver Feature

Lesson 9: Working with Scenarios

Creating Scenarios
Displaying Scenarios
Editing and Deleting Scenarios
Protecting Scenarios
Creating  Scenario Summary Reports

Lesson 10: Creating Reports

Creating Custom Reports
Printing Custom Reports
Creating PivotChart Reports