Excel 2003:
Advanced

Topic-Level Outline

Days: 1

Prerequisites: Excel 2003: Intermediate or equivalent experience

Unit 1 : Working with advanced formulas

Topic A: 0 Using names

A-1: Defining names

A-2: Using the Create Names command

A-3: Using the Apply Names command

Topic B: 0 Using decision-making functions

B-1: Using the IF function

B-2: Using the SUMIF function

Topic C: 0 Creating nested functions

C-1: Using nested IF functions

C-2: Using the ROUND function

Topic D: 0 Using financial functions

D-1: Using the PMT function

Unit 2 : Lookups and data tables

Topic A: 0 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: 0 Using MATCH and INDEX

B-1: Using the MATCH function

B-2: Using the INDEX function

Topic C: 0 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: 0 Creating subtotals

A-1: Creating subtotals in a list

A-2: Using multiple subtotal functions

Topic B: 0 Validating cell entries

B-1: Observing data validation

B-2: Setting up data validation

Topic C: 0 Exploring database functions

C-1: Examining the structure of database functions

C-2: Using the DSUM function

Topic D: 0 Working with data forms

D-1: Entering data by using a form

Unit 4 : Working with PivotTables and PivotCharts

Topic A: 0 Working with PivotTables

A-1: Creating a PivotTable

A-2: Adding fields

Topic B: 0 Rearranging PivotTables

B-1: Moving fields

B-2: Hiding and showing details

B-3: Refreshing the data in a PivotTable

Topic C: 0 Formatting PivotTables

C-1: Changing field settings

C-2: Using the AutoFormat dialog box

Topic D: 0 Working with PivotCharts

D-1: Creating a PivotChart

Unit 5 : Exporting and importing

Topic A: 0 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: 0 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: 0 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: 0 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: 0 Working with the Analysis ToolPak

B-1: Installing the Analysis ToolPak

B-2: Using the Sampling analysis tool

Topic C: 0 Working with scenarios

C-1: Creating scenarios

C-2: Switching among scenarios

C-3: Merging scenarios from another worksheet

Topic D: 0 Working with views

D-1: Creating views

D-2: Switching among views

Unit 7 : Working with macros

Topic A: 0 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: 0 Working with VBA code

B-1: Observing a VBA code module

B-2: Editing VBA code

Topic C: 0 Function procedures

C-1: Creating a custom function

Unit 8 : Interactive Web pages

Topic A: 0 Creating interactive Web spreadsheets

A-1: Publishing an interactive Web page

A-2: Maintaining an Excel-based Web page

Topic B: 0 Publishing PivotTables on the Web

B-1: Using a PivotTable on a Web page

Unit 9 : Using SharePoint services

Topic A: 0 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: 0 Discussing smart documents

Appendix B : MOS exam objectives maps

Topic A: 0 Core exam objectives

Topic B: 0 Expert exam objectives