Course description
Overview: Students will learn various advanced techniques for analyzing and manipulating data in Excel.
Prerequisites: Excel 2000: Worksheets and Excel 2000: Charting and Organizing Data or equivalent knowledge.
Performance-based objectives
Lesson objectives help students become comfortable with the course, and also provide a means to evaluate learning. Upon successful completion of this course, students will be able to:
* Customize toolbars and create styles and templates.
* Create decision-making functions.
* Analyze worksheet data by creating pivot tables.
* Compare and contrast workbook files and file links.
* Outline and consolidate worksheets and analyze worksheet data by using the Scenario Manager.
* Display and protect worksheet data by locking cells.
* Record and modify macros by using the Visual Basic Editor.
* Create and work with interactive Web documents.
Course Content
Lesson 1: Customizing the work area
Working with built-in toolbars
Using custom toolbars
Creating and using styles
Using templates
Lesson 2: Advanced formula construction
Using names
Using the IF function
Using the VLOOKUP function
Using IS functions and the Auditing features
Lesson 3: Using pivot tables
Creating pivot tables
Modifying pivot tables
Grouping and summarizing data in a pivot table
Lesson 4: Working with multiple worksheets
Working with workbooks
Linking cells in different workbooks
Workbook versus links and workspaces
Lesson 5: Consolidating and analyzing data
Consolidating data from more than one worksheet
Using the Goal Seek and Solver utilities
Using Scenario Manager to view a worksheet with different input values
Lesson 6: Using protection and display options
Using comments
Protecting workbooks
Using custom views
Lesson 7: Introduction to macros
Running macros
Recording a macro
Viewing and editing VBA code
Lesson 8: Working with interactive Excel Web documents
Saving Excel worksheets as Web documents
Spreadsheet Web Components