Excel 2000:
Advanced

Topic-Level Outline

Days: 1

Prerequisites: Excel 2000: Intermediate or equivalent experience

Unit 1 : 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

B-1: Using IF

B-2: Using SUMIF

Topic C: 0 Nesting

C-1: Nesting with IF

C-2: Using ROUND

Topic D: 0 Financial functions

D-1: Using PMT

D-2: Using FV

Unit 2 : Lookups and data tables

Topic A: 0 Lookup functions

A-1: Observing VLOOKUP

A-2: Using VLOOKUP

Topic B: 0 MATCH and INDEX

B-1: Using MATCH

B-2: Using INDEX

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

A-1: Creating subtotals in a list

A-2: Using multiple subtotal functions

Topic B: 0 Data validation

B-1: Observing data validation

B-2: Setting up data validation

Topic C: 0 Database functions

C-1: Examining the structure of database functions

C-2: Using DSUM

Topic D: 0 Data forms

D-1: Entering data using a form

Unit 4 : PivotTables

Topic A: 0 Creating a PivotTable

A-1: Using the PivotTable and PivotChart Wizard

A-2: Adding fields to a PivotTable

A-3: ActivityTitle

Topic B: 0 Rearranging PivotTables

B-1: Moving fields in a PivotTable

B-2: Showing and hiding detail in a PivotTable

B-3: Refreshing data

Topic C: 0 Formatting PivotTables

C-1: Changing field settings (including number formats)

C-2: Using the Format Table button

Topic D: 0 PivotCharts

D-1: Creating a PivotChart

Unit 5 : Exporting and Importing

Topic A: 0 Exporting data

A-1: Exporting Excel data to a text file

Topic B: 0 Importing data

B-1: Importing data from a text file into an Excel workbook

B-2: Parsing text data

Topic C: 0 Querying external databases

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

C-2: Using Web Query to get data from the web

Unit 6 : Analytical options

Topic A: 0 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 Scenarios

B-1: Creating scenarios

B-2: Switching among scenarios

Topic C: 0 Views

C-1: Creating Views

C-2: Switching among views

Topic D: 0 Reports

D-1: Using the Report Manager add-in to create a report

Unit 7 : Macros

Topic A: 0 Recording and running a macro

A-1: Running a macro

A-2: Recording a macro

A-3: Assigning a macro to a button

Topic B: 0 Editing 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

C-2: ActivityTitle

C-3: ActivityTitle

Unit 8 : Advanced web topics

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 PivotTables on the web

B-1: Using a PivotTable on a web page