Course outline for
Excel 2013 Advanced Essentials

Module 1: Advanced Formula Tasks

Understanding Relative and Absolute Cell References

Using Multiple Cell References

Using 3D References

Using Array Formulas

Review Questions

Module 2: Working with Named Ranges

Understanding Named Ranges

Defining Named Ranges

Editing Named Ranges

Deleting Named Ranges

Using Named Ranges in Formulas

Review Questions

Module 3: Using Advanced Functions

Using the PMT Function

Using the FV Function

Using VLOOKUP and HLOOKUP Functions

Using IF, AND, OR Functions

Review Questions

Module 4: Resolving Formula Errors

Tracing Formula Precedents and Dependents

Showing Formulas

Evaluating Formulas

Setting Error Checking Options

Using Error Option Buttons

Running an Error Check

Review Questions

Module 5: Managing Data

Consolidating Information

Removing Duplicates

Configuring Data Validation

Transposing Data

Converting Text to Columns

Review Questions

Module 6: Outlining and Grouping Data

Outlining Data

Showing and Hiding Outline Details

Grouping Data

Creating Subtotals

Removing Outlining and Grouping

Review Questions

Module 7: Working with Scenarios

Creating Scenarios

Loading Scenarios

Merging Scenarios

Editing Scenarios

Creating a Scenario Summary Report

Deleting Scenarios

Review Questions

Module 8: Using Solver

Setting up the Worksheet

Running Solver

Generating Reports and Scenarios with Solver

Modifying Constraints

Setting Solver Options

Review Questions

Module 9: Analyzing Data

Enabling the Analysis ToolPak

Using Data Analysis Tools

Using Goal Seek

Using Data Tables

Review Questions

Module 10: Advanced PivotTable Features

Creating a Basic PivotTable

Creating a Basic PivotChart

Using the PivotTable Fields Pane

Adding Calculated Fields

Sorting Pivoted Data

Filtering Pivoted Data

Review Questions

Module 11: Using PowerPivot

Enabling PowerPivot

Importing Access Data

Importing Excel Data

Integrating Data with Relationships

Creating a PivotTable with PowerPivot Data

Review Questions

Module 12: Using Macros

Recording a Macro

Writing a Macro using the Visual Basic Editor

Editing a Macro

Running a Macro

Review Questions