Advanced, MOS Edition
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
Unit 4 : Advanced data management
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