Excel 2007
Advanced

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