Description

Target Audience

Prerequisites

• An intermediate user of Microsoft Office Excel 2002 or 2000 for at least one year

Objectives

• Sort in ascending/descending order
• Find data
• Replace data
• Find and replace cell formats
• Use the AutoFill feature
• Use data entry shortcuts
• Create a linear series
• Create a date series
• Use a stop value
• Create a growth trend series
• Create a database
• Sort records by multiple fields
• Validate data by using a list
• Create a custom error message
• Remove data validation
• Create subtotals in a list
• Remove subtotals from a list
• View data form records
• Edit data form records
• Add data form records
• Define criteria
• Clear data form criteria
• Delete data form records
• Enable AutoFilter
• Use AutoFilter to filter a list
• Clear AutoFilter criteria
• Create a custom AutoFilter
• Disable AutoFilter
• Create a criteria range
• Use a criteria range
• Show all records
• Use an advanced AND condition
• Use an advanced OR condition
• Copy filtered records
• Use database functions
• Export data to other applications
• Import data from text files
• Change external data range properties
• Import data from other applications
• Remove the query definition
• Create a PivotTable report
• Add PivotTable report fields
• Select a page field item
• Refresh a PivotTable report
• Change the Summary function
• Add new fields to a PivotTable report
• Move PivotTable report fields
• Hide/Unhide PivotTable report items
• Delete PivotTable report fields
• Create a Page Field report
• Format a PivotTable report
• Create a PivotChart report

Outline

• Module 1: CREATING AN EXCEL DATABASE
  • An Excel Database
  • Excel List Command – New in Excel 2003
  • Create a New Database
  • Freeze Panes
  • Lab 1
  • Review 1
  • Practice 1

• Module 2: MODIFYING A DATABASE
  • Add New Records
  • Insert a New Record
  • Add New Fields
  • Find and Replace Data
  • Find and Replace Formats
  • Lab 2
  • Review 2
  • Practice 2

• Module 3: USING DATA VALIDATION
  • Reduce Data Entry Errors by Using Data Validation
  • Validate Text Length
  • Create a Custom Error Message
  • Validate Data by Using a List
  • Set Maximum and Minimum Values
  • Remove Validation Rules
  • Lab 3
  • Review 3
  • Practice 3

• Module 4: USING DATA FORMS
  • View Data Form Records
  • Edit Data Form Records
  • Add Records by Using Data Form
  • Define Search Criteria
  • Clear Criteria
  • Delete Records by Using Data Form
  • Lab 4
  • Review 4
  • Practice 4

• Module 5: SORTING AND FILTERING DATA
  • Use Simple Sorting
  • Sort Records By Multiple Fields
  • Use Custom Sort Orders
  • Create Custom Sort Orders
  • Use AutoFilter to Filter Data
  • Remove a Filter Criteria
  • Display Top Ten Records
  • Create a Custom AutoFilter
  • Create a Custom Filter by Using Wildcard
  • Lab 5
  • Review 5
  • Practice 5

• Module 6: WORKING WITH ADVANCED FILTER & DATABASE FUNCTIONS
  • Create a Criteria Range
  • Use a Criteria Range
  • Use an Advanced AND Condition
  • Use an Advanced OR Condition
  • Copy Filtered Records
  • Use Database Functions
  • Lab 6
  • Review 6
  • Practice 6

• Module 7: SUMMARIZING DATA
  • Create a Total Row
  • Use Subtotals to Summarize Data
  • Convert Data List to Data Range
  • Use Subtotals
  • Apply Multiple Subtotals
  • Use Outlines in Subtotals
  • Lab 7
  • Review 7
  • Practice 7

• Module 8: EXPORTING AND IMPORTING DATA
  • Export Data to Other Applications
  • Export Excel Data as a Text File
  • Export Data to XML
  • Import Data from a Text File
  • Refresh Data
  • Change External Data Range Properties
  • Remove Query Definition
  • Import Data from Other Applications
  • Import Dynamic Data from the Web
  • Copy a Table from a Web Page
  • Lab 8
  • Review 8
  • Practice 8

• Module 9: PIVOTTABLES
  • Get Answers with PivotTables
  • Use the PivotTable Wizard
  • Create a PivotTable Layout
  • View Our Report Differently
  • Rearrange the Layout
  • Format a PivotTable Report
  • Create a PivotChart Report
  • Lab 9
  • Review 9
  • Practice 9