Unit 1: Advanced functions
Topic A: 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: 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: Financial functions
C-1: Using the PMT function
Topic D: Displaying and printing formula
Unit 2: 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 for exact matches
A-5: Using HLOOKUP for 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
Unit 3: Advanced list management
Topic A: Validating cell entries
A-1: Observing data validation
A-2: Setting up data validation
Topic B: Exploring database functions
Unit 4: PivotTables and PivotCharts
Topic A: Working with PivotTables
A-1: Creating a PivotTable
A-2: Adding fields to a PivotTable
Topic B: Rearranging PivotTables
B-1: Moving fields
B-2: Hiding and showing details
B-3: Refreshing the data in a PivotTable
Topic C: Formatting PivotTables
C-1: Formatting by using a Pivot style
C-2: Changing field settings
Topic D: PivotCharts
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 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: 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: 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: 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
Topic D: Views
D-1: Creating views
D-2: Switching among views
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 button
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
Unit 8: Conditional formatting and SmartArt
Topic A: Conditional formatting with graphics
A-1: Creating data bars
A-2: Using color scales
A-3: Creating icon sets
Topic B: SmartArt graphics
B-1: Inserting a SmartArt graphic
B-2: Modifying a SmartArt graphic
|
