Excel 2000: Advanced

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