Second Edition
Topic-Level Outline
Days: 1
Prerequisites: Excel
2003: Intermediate or equivalent experience
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: Microsoft Office Specialist exam objectives maps
Topic A: Specialist exam objectives
Topic B: Expert exam objectives