| Who should attend?
| This course is intended for participants who have previous experience with spreadsheet programs. |
| Prerequisites:
| Excel 2000 Fundamentals |
| Performance objectives:
| Upon successful completion of this course, students will be able to accomplish the following: - 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 Outline
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
Applying Your Skills
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
Applying Your Skills
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
Applying Your Skills
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
Applying Your Skills
