Excel 2007

Topic-Level Outline

                         Days:  1

          Prerequisites:  Excel 2007: Intermediate or equivalent experience

                         Unit 1 :  Advanced functions

                               Topic A: 0  Logical functions

                               A-1:                               Using the IF function

                               A-2:                               Using OR, AND, and NOT as nested functions

                               A-3:                               Using nested IF functions

                               A-4:                               Using the IFERROR function

                               Topic B: 0  Math and statistical functions

                               B-1:                               Using SUMIF

                               B-2:                               Using COUNTIF

                               B-3:                               Using AVERAGEIF

                               B-4:                               Using SUMIFS, COUNTIFS, and AVERAGEIFS

                               B-5:                               Using ROUND

                               Topic C: 0  Financial functions

                               C-1:                               Using the PMT function

                               Topic D: 0  Displaying and printing formulas

                         D-1:                         Showing, printing, and hiding formulas

                         Unit 2 :  Lookups and data tables

                               Topic A: 0  Using lookup functions

                               A-1:                               Examining VLOOKUP

                               A-2:                               Using VLOOKUP to find an exact match

                               A-3:                               Using VLOOKUP to find an approximate match

                               A-4:                               Using HLOOKUP for exact matches

                               A-5:                               Using HLOOKUP for approximate matches

                               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  Validating cell entries

                               A-1:                               Observing data validation

                               A-2:                               Setting up data validation

                               Topic B: 0  Exploring database functions

                         B-1:                         Examining the structure of database functions

                         B-2:                         Using the DSUM function

                         Unit 4 :  PivotTables and PivotCharts

                               Topic A: 0  Working with PivotTables

                               A-1:                               Creating a PivotTable

                               A-2:                               Adding fields to a PivotTable

                               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:                               Formatting by using a Pivot style

                               C-2:                               Changing field settings

                               Topic D: 0  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

                               A-3:                               Converting text to columns

                               A-4:                               Removing duplicate records

                               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 a 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:                               Installing Solver and the Analysis ToolPak

                               A-3:                               Using Solver to solve for multiple variables

                               Topic B: 0  The Analysis ToolPak

                               B-1:                               Using the Sampling analysis tool

                               Topic C: 0  Scenarios

                               C-1:                               Creating scenarios

                               C-2:                               Switching among scenarios

                               C-3:                               Merging scenarios from another worksheet

                               Topic D: 0  Views

                         D-1:                         Creating views

                         D-2:                         Switching among views

                         Unit 7 :  Macros and custom functions

                               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  Creating functions

                         C-1:                         Creating a custom function

                         Unit 8 :  Conditional formatting and SmartArt

                               Topic A: 0  Conditional formatting with graphics

                               A-1:                               Creating data bars

                               A-2:                               Using color scales

                               A-3:                               Creating icon sets

                               Topic B: 0  SmartArt graphics

  B-1:  Inserting a SmartArt graphic

  B-2:  Modifying a SmartArt graphic