Microsoft Excel 2000 Advanced:
 

This course is intended for participants who have previous experience with spreadsheet programs. This is one of two PBTSystem books covering problem-solving skills for Microsoft Excel 2000. This full-day performance-based training course covers skills using Excel 2000, including using range names, using macros, checking data, and creating forms.

Course Prerequisites: Excel 2000 Fundamentals Operating System Windows 95 or 98

Course Length: 1 day

Performance Objectives:  Use range names to easily identify a group of cells. Use functions and array formulas to perform complex analysis of data.  Display subtotals to automatically summarize data. Use AutoFilters and macros to automate filtering of data.  Check data to ensure that it is error free.  Perform what-if analyses to evaluate and forecast changes in worksheet data.  Create a form and link it to a database so that data can be collected automatically.  Protect a form to prevent unwanted changes to the form design.

Course Content:

Module 1:
Working with Formulas and Functions; Building Your Skills; Using Range Names; Creating Range Names Using Range Names in Formulas; Using Conditional Functions; Using the IF Function; Using the SUMIF Function; Using the Conditional Sum Wizard; Creating Formulas with Nested Functions; Entering Array Formulas.

Module 2:
Organizing and Displaying Data; Building Your Skills; Displaying Subtotals; Filtering Data Using AutoFilters; Creating Custom AutoFilters; Creating Advanced Filters; Copying and Exporting Filtered Data; Using Macros to Filter Data; Creating Macro Buttons and Editing Macros; Creating Macro Buttons Editing Macros on the Macro Sheet.

Module 3:
Reviewing and Testing Data; Building Your Skills; Checking Data; Setting Data Validation Criteria; Setting an Error Alert; Tracing Errors in Formulas; Using Goal Seek; Using Scenarios; Creating Scenarios; Editing and Merging Scenarios; Creating a Scenario Summary Report; Using Data Tables.

Module 4:
Creating Forms; Building Your Skills; Designing a Form; Adding Form Controls; Adding Combo Boxes; Adding Check Boxes; Adding Option Buttons; Linking a Form to a Database; Protecting and Using a Form.