Course outline for
Microsoft Office Excel 2010 - Advanced

Introduction

Prerequisites

Section 1: Getting the Most from Your Data

Lesson 1.1: Outlining and Grouping Data

Using Automatic Outlining

Displaying and Collapsing Levels

Grouping Data Manually

Creating Subtotals

Step-By-Step

Skill Sharpener

Lesson 1.2: Exploring Scenarios

What is a Scenario?

Creating a Scenario

Saving Multiple Scenarios

Merging Scenarios

Creating a Scenario Summary Report

Step-By-Step

Skill Sharpener

Lesson 1.3: Using Data Analysis Tools

Enabling Data Analysis Tools

An Overview of Excel's Analysis Tools

An Overview of the Available Add-In Tools

Using a One or Two Input Data Table

Using Goal Seek

Step-By-Step

Skill Sharpener

Lesson 1.4: Using Solver

Understanding Solver

Generating Reports and Scenarios with Solver

Changing Solver Values

Managing Solver Constraints

Choosing a Solving Method

Using Solver as a Goal Seek Tool

Step-By-Step

Skill Sharpener

Lesson 1.5: Excel and Hyperlinks

What is a Hyperlink?

Inserting Hyperlinks

Editing Hyperlinks

Formatting Hyperlinks

Using Hyperlinks in Excel

Step-By-Step

Skill Sharpener

Section 1: Case Study

Section 1: Review Questions

Section 2: Pivoting Data

Lesson 2.1: Getting Started with PivotTables

What is a PivotTable?

Creating a PivotTable

Using the PivotTable Tools Tabs

Adding and Removing Data with the Field List

Changing the Field List Layout

Pivoting Data

Step-By-Step

Skill Sharpener

Lesson 2.2: Working with PivotTable Data

Expanding and Collapsing Data

Filtering Data

Sorting Data

Grouping Data

Refreshing Data

Editing the Data Source

Step-By-Step

Skill Sharpener

Lesson 2.3: Formatting a PivotTable

Modifying Fields and Labels

Modifying Values

Using the Layout Group on the Design Tab

Applying a Style to a PivotTable

Changing PivotTable Style Options

Manually Formatting a PivotTable

Using the PivotTable Options Dialog

Step-By-Step

Skill Sharpener

Lesson 2.4: Using the Classic PivotTable Layout

Creating an Empty (Classic) PivotTable Frame

Switching an Existing PivotTable to a Classic Layout

Adding Data

Pivoting Data

Step-By-Step

Skill Sharpener

Lesson 2.5: Advanced PivotTable Tasks

Creating a PivotTable Based on External Data

Refreshing External Data

Creating a Slicer

Using the Slicer Tools Tab

Step-By-Step

Skill Sharpener

Lesson 2.6: Using PowerPivot

System Requirements

Downloading and Installing PowerPivot

Importing Access Data

Importing Excel Data

Integrating Data with Relationships

Creating a PivotTable with PowerPivot Data

Step-By-Step

Skill Sharpener

Section 2: Case Study

Section 2: Review Questions

Section 3: Charting Pivoted Data

Lesson 3.1: Getting Started with PivotCharts

Creating a PivotChart from Scratch

Creating a PivotChart from Existing Data

Adding Data to your Chart

Pivoting Data

Step-By-Step

Skill Sharpener

Lesson 3.2: Using the PivotChart Tools Tabs

Using the Design Tab

Using the Layout Tab

Using the Format Tab

Using the Analyze Tab

Step-By-Step

Skill Sharpener

Lesson 3.3: Formatting a PivotChart

Renaming Fields

Changing the Chart Type

Applying a Chart Style

Manually Formatting Chart Elements

Changing the Layout of Chart Elements

Step-By-Step

Skill Sharpener

Lesson 3.4: Advanced PivotChart Tasks

Creating a PivotChart Based on External Data

Creating a Slicer

Creating a PivotTable and PivotChart from a Scenario

Creating PivotCharts with PowerPivot Data

Step-By-Step

Skill Sharpener

Section 3: Case Study

Section 3: Review Questions

Section 4: Advanced Excel Tasks

Lesson 4.1: Using Advanced Functions

Using the PMT Function

Using the FV Function

Understanding Logical Functions

Using Logical Functions

Using IFERROR with Array Formulas

Step-By-Step

Skill Sharpener

Lesson 4.2: Using the VLOOKUP Function

Understanding VLOOKUP and HLOOKUP

Using VLOOKUP to Find Data

How to Find an Exact Match with VLOOKUP

Finding an Approximate Match with VLOOKUP

Using VLOOKUP as an Array Formula

Step-By-Step

Skill Sharpener

Lesson 4.3: Using Custom AutoFill Lists

What is an AutoFill List?

Creating a Custom AutoFill List

Using a Custom AutoFill List

Modifying a Custom AutoFill List

Deleting a Custom AutoFill List

Step-By-Step

Skill Sharpener

Lesson 4.4: Linking, Consolidating, and Combining Data

Linking Workbooks

Consolidating Workbooks

Combining Worksheets

Pivoting Consolidated Data

Step-By-Step

Skill Sharpener

Section 4: Case Study

Section 4: Review Questions

Section 5: Macros, Visual Basic, and Excel Programming

Lesson 5.1: Creating a Basic Macro

Recording a Macro

Editing a Macro

Running a Macro

Understanding Macro Security

Step-By-Step

Skill Sharpener

Lesson 5.2: Visual Basic and Macros

Opening the Visual Basic Editor

Understanding the Visual Basic Editor

Adding Code to your Macro

Adding Comments to Visual Basic Code

Step-By-Step

Skill Sharpener

Lesson 5.3: More Macro Tasks

What are Relative References?

Recording a Relative Reference Macro

Running a Relative Reference Macro

Assigning a Keyboard Shortcut to a Macro

Copying a Macro from a Workbook or Template

Step-By-Step

Skill Sharpener

Lesson 5.4: Advanced Visual Basic Tasks

Declaring Variables

Iteration over a Range

Prompting for User Input

Using If, Then, and Else Statements

Step-By-Step

Skill Sharpener

Section 5: Case Study

Section 5: Review Questions

Index