Microsoft Excel 2013 Advanced Essentials

This course has been superseded

We suggest the following instead:
Microsoft Office Excel Advanced

Course Description

Microsoft Excel 2013 has lots of time-saving tools. Do you know how to make the most of them? The modules in the course will show you how! This advanced course covers a wide range of topics, including advanced formula tasks, working with named ranges, using advanced functions, resolving formula errors, managing data, outlining and grouping data, working with scenarios, using Solver, the Analysis ToolPak, PivotTable features, using PowerPivot, and developing macros.
1 day
Contact us for pricing
 

Prerequisites

To ensure the successful completion of this Advanced course, the completion of relevant Core or Intermediate courses - or equivalent knowledge - is recommended.

Advanced Formula Tasks

Understanding Relative and Absolute Cell References
Using Multiple Cell References
Using 3D References
Using Array Formulas

Working with Named Ranges

Understanding Named Ranges
Defining Named Ranges
Editing Named Ranges
Deleting Named Ranges
Using Named Ranges in Formulas

Using Advanced Functions

Using the PMT Function
Using the FV Function
Using VLOOKUP and HLOOKUP Functions
Using IF, AND, OR Functions

Resolving Formula Errors

Tracing Formula Precedents and Dependents
Showing Formulas
Evaluating Formulas
Setting Error Checking Options
Using Error Option Buttons
Running an Error Check

Managing Data

Consolidating Information
Removing Duplicates
Configuring Data Validation
Transposing Data
Converting Text to Columns

Outlining and Grouping Data

Outlining Data
Showing and Hiding Outline Details
Grouping Data
Creating Subtotals
Removing Outlining and Grouping

Working with Scenarios

Creating Scenarios
Loading Scenarios
Merging Scenarios
Editing Scenarios
Creating a Scenario Summary Report
Deleting Scenarios

Using Solver

Setting up the Worksheet
Running Solver
Generating Reports and Scenarios with Solver
Modifying Constraints
Setting Solver Options

Analyzing Data

Enabling the Analysis ToolPak
Using Data Analysis Tools
Using Goal Seek
Using Data Tables

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

Using PowerPivot

Enabling PowerPivot
Importing Access Data
Importing Excel Data
Integrating Data with Relationships
Creating a PivotTable with PowerPivot Data

Using Macros

Recording a Macro
Writing a Macro using the Visual Basic Editor
Editing a Macro
Running a Macro
Advanced ExcelExcel 2013Microsoft ExcelMIcrosoft Excel 2013MS Excel