Microsoft Office Excel 2010 - Advanced Course Outline

Microsoft Office Excel 2010 - Advanced



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


Skill Sharpener

Lesson 1.2: Exploring Scenarios

What is a Scenario?

Creating a Scenario

Saving Multiple Scenarios

Merging Scenarios

Creating a Scenario Summary Report


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


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


Skill Sharpener

Lesson 1.5: Excel and Hyperlinks

What is a Hyperlink?

Inserting Hyperlinks

Editing Hyperlinks

Formatting Hyperlinks

Using Hyperlinks in Excel


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


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


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


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


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


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


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


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


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


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


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


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


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


Skill Sharpener

Lesson 4.4: Linking, Consolidating, and Combining Data

Linking Workbooks

Consolidating Workbooks

Combining Worksheets

Pivoting Consolidated Data


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


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


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


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


Skill Sharpener

Section 5: Case Study

Section 5: Review Questions


Download this course as a demo

Complete the form below and we'll email you an evaluation copy of this course:

What You Get with Velsoft Courseware

More Course Outlines in this Series