Excel 2002: Advanced

« back to previous page

Description:	Students will learn how to work with Excel databases using advanced filtering options. They will learn to share data between Microsoft Access, Word and Excel and create PivotTables and PivotCharts using data lists. Worksheets will be customized using conditional and custom formats, templates and macros. Changes to worksheets will be tracked and shared between users.
Prerequisites:	Intermediate Excel 2002 or equivalent experience
Length:	One day

Working with Databases
• Working with an Excel Data List
• Adding and Deleting Records
• Finding and Replacing Data
• Sorting a List
• Using Data Validation
• Creating a Custom Error Message
• Creating Subtotals

Working with the Data Form
• Understanding Data Form Components
• Adding and Editing Records in a Form
• Finding and Deleting Records in a Form

Using AutoFilter
• Filtering Your Data
• Clearing AutoFilter Criteria
• Using AutoFilter Options
• Setting Custom Criteria
• Disabling AutoFilter

Working with Advanced Filters
• Using the Advanced Filter
• Entering the Criteria
• Specifying AND and OR conditions
• Using Comparison Operators
• Copying Filtered Records
• Using Database Functions

Importing and Exporting Data
• Sharing Information Between Programs
• Exporting and Importing a File
• Using Microsoft Access Data
• Exporting Data to Microsoft Access
• Copying a Microsoft Word Table into Excel
• Inserting Excel Data into Microsoft Word

PivotTables
• Creating a Simple PivotTable
• Using the PivotTable Toolbar
• Using PivotTable Options
• Formatting a PivotTable
• Performing Custom Calculations
• Merging Labels
• Viewing Page Data
• Sorting a PivotTable
• Grouping and Ungrouping Data
• Creating and Formatting a PivotChart
• Sorting PivotChart Items

Using Conditional and Custom Formats
• Applying a Conditional Format
• Changing a Conditional Format
• Adding and Deleting a Conditional Format
• Creating a Custom Format
• Using Zeroes in Formatting
• Using Color Codes

Customizing Excel Preferences
• Working with Adaptive Menus and Toolbars
• Understanding Templates
• Creating Custom and Default Excel Worksheet Templates
• Creating Custom and Default Excel Workbook Templates
• Restoring Original Settings for New Worksheets and Workbooks

Sharing Workbooks
• Setting Up a Shared Workbook
• Tracking the History of a Workbook
• Viewing Conflicting Changes Between Users
• Limiting User Access
• Viewing the History of Changes

Working with Macros
• Recording and Running a Macro
• Viewing and Editing a Macro
• Deleting a Macro
• Creating a Macro Custom Button
• Assigning a Macro to a Drawing Object
• Understanding Macro Viruses

Summary and Review