16 Corporate Woods Boulevard | Albany, New York 12211 | Tel: 518.437.1600

Excel 2003: Level 3

Available Dates: Request Dates
Class Length: 1 day
Cost: $199

Class Outline:

This course covers advanced-level features and functions of Excel 2003. Students will learn how to create nested functions, export/import data, perform what-if analyses, use the Goal Seek and Solver utilities, record and run macros, and use SharePoint services. This course is approved courseware for Microsoft Office Specialist certification.

Table of Contents:

Unit 1 : Working with advanced formulas
Topic A: Using names
A-1: Defining names
A-2: Using the Create Names command
A-3: Using the Apply Names command
Topic B: Using decision-making functions
B-1: Using the IF function
B-2: Using the SUMIF function
Topic C: Creating nested functions
C-1: Using nested IF functions
C-2: Using the ROUND function
Topic D: Using financial functions
D-1: Using the PMT function

Unit 2 : Lookups and data tables
Topic A: Working with lookup functions
A-1: Examining VLOOKUP
A-2: Using VLOOKUP for an exact match
A-3: Using VLOOKUP for an approximate match
Topic B: Using MATCH and INDEX
B-1: Using the MATCH function
B-2: Using the INDEX function
Topic C: Creating data tables
C-1: Creating a one-variable data table
C-2: Creating a two-variable data table

Unit 3 : Advanced list management
Topic A: Creating subtotals
A-1: Creating subtotals in a list
A-2: Using multiple subtotal functions
Topic B: Validating cell entries
B-1: Observing data validation
B-2: Setting up data validation
Topic C: Exploring database functions
C-1: Examining the structure of database functions
C-2: Using the DSUM function
Topic D: Working with data forms
D-1: Entering data by using a form

Unit 4 : Working with PivotTables and PivotCharts
Topic A: Working with PivotTables
A-1: Creating a PivotTable
A-2: Adding fields
Topic B: Rearranging PivotTables
B-1: Moving fields
B-2: Hiding and showing details
B-3: Refreshing the data in a PivotTable
Topic C: Formatting PivotTables
C-1: Changing field settings
C-2: Using the AutoFormat dialog box
Topic D: Working with PivotCharts
D-1: Creating a PivotChart

Unit 5 : Exporting and importing
Topic A: Exporting and importing text files
A-1: Exporting Excel data to a text file
A-2: Importing data from a text file into a workbook
Topic B: Exporting and importing XML data
B-1: Using the XML Source task pane
B-2: Importing XML data into a workbook
B-3: Exporting data from a workbook to an XML data file
B-4: Deleting an XML map
Topic C: Querying external databases
C-1: Using Microsoft Query to get data from an external database
C-2: Discussing the Web query feature
C-3: Using Web query to get data from the Web

Unit 6 : Using analytical options
Topic A: Working with Goal Seek and Solver
A-1: Using Goal Seek to solve for a single variable
A-2: Using Solver to solve for multiple variables
Topic B: Working with the Analysis ToolPak
B-1: Installing the Analysis ToolPak
B-2: Using the Sampling analysis tool
Topic C: Working with scenarios
C-1: Creating scenarios
C-2: Switching among scenarios
C-3: Merging scenarios from another worksheet
Topic D: Working with views
D-1: Creating views
D-2: Switching among views

Unit 7 : Working with macros
Topic A: Running and recording a macro
A-1: Running a macro
A-2: Recording a macro
A-3: Assigning a macro to a button
Topic B: Working with VBA code
B-1: Observing a VBA code module
B-2: Editing VBA code
Topic C: Function procedures
C-1: Creating a custom function

Unit 8 : Interactive Web pages
Topic A: Creating interactive Web spreadsheets
A-1: Publishing an interactive Web page
A-2: Maintaining an Excel-based Web page
Topic B: Publishing PivotTables on the Web
B-1: Using a PivotTable on a Web page

Unit 9 : Using SharePoint services
Topic A: Document Workspaces
A-1: Creating a Document Workspace
A-2: Publishing and viewing a list on the SharePoint server
A-3: Synchronizing a list with data on the SharePoint server

Appendix A : Smart documents
Topic A: Discussing smart documents
Appendix B : MOS exam objectives maps
Topic A: Core exam objectives
Topic B: Expert exam objectives