Excel 2003: Advanced

Second Edition

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 :  Microsoft Office Specialist exam objectives maps

                               Topic A: 0  Specialist exam objectives

                               Topic B: 0  Expert exam objectives