Course Description

Overview: Students will learn various advanced techniques for analyzing and manipulating data in Excel.

Prerequisites: To ensure students’ success with this course, we recommend that they first take the following
Element K Content courses or have equivalent knowledge:

• Excel 2003: Level 1
• Excel 2003: Level 2

 

See Dates for this class

Performance-Based Objectives

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.

 

 

Course Content

Lesson 1: Customizing the work area
Working with built-in toolbars
Using custom toolbars
Creating and using styles
Using templates
Changing the color scheme of Office

Lesson 2: Advanced formula construction
Using names
Using the IF function
Using the VLOOKUP function
Using IS functions and the Auditing features
Locating invalid data and formulas
Applying conditional formatting

Lesson 3: Using PivotTable Reports
Creating PivotTable Reports
Modifying PivotTable reports
Grouping and summarizing data in a PivotTable report

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

Appendix A: New Features in Office 2003
New Features

Appendix B: Working with interactive Excel Web documents
Saving Excel worksheets as Web documents
Spreadsheet Web Components

Appendix C: Importing and exporting data
Export to Microsoft Word
Import a Word table
Import text files