Excel 2010:

Topic-Level Outline

Days:  1

Prerequisites:  Excel 2010: Intermediate or equivalent experience

Unit 1:  Logical and statistical functions

Topic A:  Logical functions

A-1:                               Using the IF function

A-2:                               Using a formula to apply conditional formatting

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

A-4:                               Using nested IF functions

A-5:                               Using the IFERROR function

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

Unit 2:  Financial and date functions

Topic A:  Financial functions

A-1:                               Using the PMT function

Topic B:  Date and time functions

B-1:                               Using date functions

B-2:                               Using time functions

Topic C:  Array formulas

C-1:                               Using an array formula

C-2:                               Applying arrays to functions

C-3:                               Modifying the array formula

Topic D:  Displaying and printing formulas

D-1:                         Showing, printing, and hiding formulas

D-2:                         Setting calculation options

Unit 3:  Lookups and data tables

Topic A:  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 to find exact matches

A-5:                               Using HLOOKUP to find approximate matches

Topic B:  Using MATCH and INDEX

B-1:                               Using the MATCH function

B-2:                               Using the INDEX function

Topic C:  Creating data tables

C-1:                         Creating a one-variable data table

C-2:                         Creating a two-variable data table

Topic A:  Validating cell entries

A-1:                               Observing data validation

A-2:                               Creating a data validation rule

A-3:                               Setting date and list validation rules

Topic B:  Exploring database functions

B-1:                         Examining the structure of database functions

B-2:                         Using the DSUM function

Unit 5:  Exporting and importing

Topic A:  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:  Exporting and importing XML data

B-1:                               Using the XML Source 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:  Getting external data

C-1:                         Getting external data from Microsoft Query

C-2:                         Using a Web query to get data from the Web

C-3:                         Discussing cube functions

Unit 6:  Analytical tools

Topic A:  Goal Seek and Solver

A-1:                               Using Goal Seek to solve for a single variable

A-2:                               Activating Solver and the Analysis ToolPak

A-3:                               Using Solver to solve for multiple variables

Topic B:  The Analysis ToolPak

B-1:                               Using the Sampling analysis tool

Topic C:  Scenarios

C-1:                         Creating scenarios

C-2:                         Switching among scenarios

C-3:                         Merging scenarios from another worksheet

Unit 7:  Macros and custom functions

Topic A:  Running and recording a macro

A-1:                               Running a macro

A-2:                               Recording a macro

A-3:                               Assigning a macro to a command button

A-4:                               Inserting a macro button

A-5:                               Creating an Auto_Open macro

Topic B:  Working with VBA code

B-1:                               Observing a VBA code module

B-2:                               Editing VBA code

Topic C:  Creating functions

C-1:  Creating a custom function