Excel 2013 – Advanced

Duration: 1 day

Course Aims and Objectives:

The Advanced Excel 2013 training course covers the key topics required by users of this application, including the brand new features of this version, with lots of in-depth explanations, step by step instructions, key questions and group and individual activities. 

This course is designed to help you to extend your knowledge into some of the more specialised and advanced capabilities of Excel. You will learn how to outline and group data, work with Scenarios and Solver, work with the Analysis ToolPak, use PivotTable features, PowerPivot, and develop macros.

Who should attend:

This course is designed for Microsoft Excel users who wish to develop their skills so they can use the advanced techniques to manipulate Excel data.

Assumed Knowledge

It is also recommended that proposed delegates have attended a Microsoft Excel 2013 Intermediate course or have equivalent knowledge.

Course Outline Summary

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
  • Editing and Merging Scenarios
  • Creating a Scenario Summary Report

 

Using Solver

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

 

Analysing 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 and Filtering Pivoted Data

 

Using PowerPivot

  • Enabling PowerPivot
  • Importing Access Data
  • Importing Excel Data
  • 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

 

Working with Charts

  • Modify Charts (Core Essentials)
  • Add Trendlines to a Chart
  • Analyse Data using Sparklines (Core Essentials)