Excel XP:
Advanced

Topic-Level Outline

Days: 1

Prerequisites: Excel XP: 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 Decision-making functions

B-1: Using IF

B-2: Using SUMIF

Topic C: 0 Creating nested functions

C-1: Nesting with IF

C-2: Using ROUND

Topic D: 0 Using financial functions

D-1: Nesting with IF

Unit 2 : Lookups and data tables

Topic A: 0 Working with lookup functions

A-1: Observing 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 MATCH

B-2: Using INDEX

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 DSUM

Topic D: 0 Working with data forms

D-1: Entering data using a form

Unit 4 : Working with PivotTables

Topic A: 0 PivotTables

A-1: Using the PivotTable and PivotChart Wizard

A-2: Adding fields to a PivotTable

Topic B: 0 Rearranging PivotTables

B-1: Moving fields in a PivotTable

B-2: Hiding and showing detail in a PivotTable

B-3: Refreshing data of an Advanced PivotTable

Topic C: 0 Formatting PivotTables

C-1: Changing field settings in a PivotTable

C-2: Using the Format Report button

Topic D: 0 Working with PivotCharts

D-1: Creating a PivotChart

Unit 5 : Exporting and importing

Topic A: 0 Exporting and importing data

A-1: Exporting Excel data into a text file

A-2: Importing data from a text file into an Excel workbook

Topic B: 0 Querying external databases

B-1: Using Microsoft Query to get data from an external database

B-2: Discussing Web query

B-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 scenarios

B-1: Creating scenarios

B-2: Switching among scenarios

Topic C: 0 Working with views

C-1: Creating views

C-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

Appendix A : MOUS exam objectives map

Topic A: 0 Core exam objectives

Topic B: 0 Expert exam objectives