Course Overview
This course builds on the skills and concepts taught in Excel 2010: Intermediate. Students will work
with advanced formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In
addition, students will learn about data validation and database functions such as DSUM. They will
learn how to work with PivotTables and PivotCharts, how to import and export data, and how to
query external databases. Finally, students will learn about the analytical features of Excel (such as
Goal Seek and Solver), running and recording macros, SmartArt graphics, and conditional formatting
with graphics.
Course Outline
Course Introduction 2m
Unit 01 – Logical & Statistical Functions 37m
Topic A: Logical Functions
The IF Function
Demo – A-1: Using the IF Function
Demo – A-2: Using a Formula to Apply Conditional Formatting
OR, AND, and NOT Functions
Demo – A-3: Using OR, AND, and NOT as Nested Functions
Nested IF Functions
Demo – A-4: Using Nested IF Functions
The IFERROR Function
Demo – A-5: Using the IFERROR Function
Topic B: Math and Statistical Functions
The SUMIF Function
Demo – B-1: Using SUMIF
The COUNTIF Function
Demo – B-2: Using COUNTIF
The AVERAGEIF Function
Demo – B-3: Using AVERAGEIF
SUMIFS, COUNTIFS, AVERAGEIFS
Demo – B-4: Using SUMIFS, COUNTIFS, and AVERAGEIFS
The ROUND Function
Evaluate Formula Dialog Box
Demo – B-5: Using ROUND
Unit 01 Review
Unit 02 – Financial & Date Functions 32m
Topic A: Financial Functions
The PMT Function
Demo – A-1: Using the PMT Function
Topic B: Date and Time Functions
Date Functions
Demo – B-1: Using Date Functions
Calculating Time
Demo – B-2: Using Time Functions
Topic C: Array Formulas
Array Formulas
Creating an Array Formula
Demo – C-1: Using an Array Formula
Applying Arrays to Functions
Demo – C-2: Applying Arrays to Functions
Modify an Array Formula
Demo – C-3: Modifying the Array Formula
Topic D: Displaying and Printing Formulas
Displaying Formulas in Cells
Hide Formulas from Users
Show Hidden Formulas
Demo – D-1: Showing, Printing, and Hiding Formulas
Automatic Recalculation
Edit Iteration Calculation Options
Demo – D-2: Setting Calculation Options
Unit 02 Review
Unit 03 – Lookups and Data Tables 28m
Topic A: Using Lookup Functions
The HLOOKUP Function
The VLOOKUP Function
Demo – A-1: Examining VLOOKUP
VLOOKUP for Exact Matches
Demo – A-2: Using VLOOKUP to Find an Exact Match
VLOOKUP for Approximate Matches
Demo – A-3: Using VLOOKUP to Find an Approximate Match
HLOOKUP for Exact Matches
Demo – A-4: Using HLOOKUP to Find Exact Matches
HLOOKUP for Approximate Matches
Demo – A-5: Using HLOOKUP to Find Approximate Matches
Topic B: Using MATCH and INDEX
The MATCH Function
Demo – B-1: Using the MATCH Function
The INDEX Function
Demo – B-2: Using the INDEX Function
Topic C: Creating Data Tables
One-Variable Data Tables
Demo – C-1: Creating a One-variable Data Table
Two-Variable Data Tables
Demo – C-2: Creating a Two-variable Data Table
Unit 03 Review
Unit 04 – Advanced Data Management 16m
Topic A: Validating Cell Entries
Data Validation
Demo – A-1: Observing Data Validation
Setting Data Validation Rules
Demo – A-2: Setting Up Data Validation
Using Date Criteria
Demo – A-3: Setting Date and List Validations
Topic B: Exploring Database Functions
Structure of Database Functions
Demo – B-1: Examining the Structure of Database Functions
DSUM and DAVERAGE
Demo – B-2: Using the DSUM Function
Unit 04 Review
Unit 05 – Exporting and Importing 25m
Topic A: Exporting and Importing Text Files
Using the Save As Command
Demo – A-1: Exporting Excel Data to a Text File
Importing Data
Demo – A-2: Importing Data from a Text File into a Workbook
The Text Import Wizard
Converting Text to Columns
Demo – A-3: Converting Text to Columns
Removing Duplicates
Demo – A-4: Removing Duplicate Records
Topic B: Exporting and Importing XML Data
The XML Maps Dialog Box
The XML Source Pane
Importing XML Data
Exporting Data to an XML File
Deleting XML Maps
Topic C: Querying External Databases
Using Microsoft Query
Demo – C-1: Getting External Data from Microsoft Query
Web Query
Retrieving Data from a Web Page
Demo – C-2: Using a Web Query to Get Data from the Web
Unit 05 Review
Unit 06 – Analytical Tools 28m
Topic A: Goal Seek and Solver
Using the Goal Seek Utility
Demo – A-1: Using Goal Seek to Solve for a Single Variable
Activating Add-Ins
The Add-Ins Dialog Box
The Solver Parameters Dialog Box
Demo – A-3: Using Solver to Solve for Multiple Variables
Topic B: The Analysis ToolPak
Analysis ToolPak
Using the Sampling Analysis Tool
Demo – B-1: Using the Sampling Analysis Tool
Topic C: Scenarios
Creating a Scenario
Demo – C-1: Creating Scenarios
Add a Scenario Manager Button
Merging Scenarios
A Sample Scenario Summary
Unit 06 Review
Unit 07 – Macros and Custom Functions 33m
Topic A: Running and Recording a Macro
Running Macros
Demo – A-1: Running a Macro
Recording Macros
Demo – A-2: Recording a Macro
Assigning Macros to Buttons
Demo – A-3: Assigning a Macro to a Button
Add Macro Buttons to the Ribbon
Insert a Button
Change Button Properties
Demo – A-4: Inserting a Macro Button
Create Auto_Open Macro
Demo – A-5: Creating an Auto_Open Macro
Topic B: Working with VBA Code
VBA Code
Observing VBA Code
Demo – B-1: Observing a VBA Code Module
Example of Editing VBA Code
Demo – B-2: Editing VBA Code
Topic C: Creating Functions
Function Procedures
Creating a Custom Function
Demo – C-1: Creating a Custom Function
Unit 07 Review
Total Duration: 3hrs 20m