About this course

Course type Performance Plus
Course code QAEX13PP1
Duration 1 Day

In QA's Microsoft Excel 2013 Intermediate/Advanced Level courses, we show you how to create and manage PivotTables, Slicers and PivotCharts: some of Excel's most powerful features for analysing data. This course takes PivotTables to the next level, teaching you how to use PowerPivot: a free add-in available for Microsoft Excel 2013. PowerPivot links in with PivotTables, however the data used, can be manipulated and managed as though you were using a Relational Database (such as SQL or Access).

This course is designed for intermediate/advanced Microsoft Excel professionals who may work or be interested in the domains of finance, statistics, project analysis, market analysis or general data manipulation. They will have a need to create PivotTables on a regular basis and to produce reports with multiple PivotTables/PivotCharts to produce 'Business Intelligent' type dashboard reports.

IMPORTANT NOTE
PowerPivot will not work on Microsoft Excel 2003 or 2007, it is designed for Microsoft Excel 2010 or 2013 users only.

Prerequisites

Before attending this course, students need to be able to:

  • Demonstrate a good working knowledge of Microsoft Windows
  • Demonstrate a good working knowledge of Microsoft Excel 2013
  • Demonstrate a basic knowledge of Database Relationships (if relevant to your work)
  • Understand formula/function writing to manipulate data
  • Understand basic features of PivotTables, such as the areas designed for fields

To ensure your success, we recommend the following courses have been undertaken, or equivalent knowledge gained:

  • Microsoft Excel 2013 Advanced

Please Note: If you attend a course and do not meet the prerequisites, or your organisation does not use Microsoft Excel 2013 (or 2010), you may be asked to leave.

Delegates will learn how to

  • Use a wide variety of data sources within PowerPivot
  • Create calculations within PowerPivot
  • Manage various PivotTables creates by PowerPivot

Outline

Module 1 - PivotTable Review

  • PivotTable Hints and Tips

Module 2 - Introduction to PowerPivot

  • PowerPivot Overview

Module 3 - Connecting to Single Table Data Sources

  • Pasting data into PowerPivot
  • Checking PowerPivot can Produce Results
  • Excel (flat-file database) as a Data Source
  • Importing Data from Data Feeds into PowerPivot (Internet connection required)
  • Excel Linked Tables as a Data Source

Module 4 - Working with Multiple Table Data Sources

  • Access Tables (relational database) as a Data Source
  • Creating a Perspective to make Field Lists Manageable
  • Filtering Data during Importing External Data

Module 5 - The Diagram View

  • The Diagram View in PowerPivot
  • Managing Relationships and Tables in the Diagram View
  • Working with Hierarchies

Module 6 - Calculations and KPIs

  • Create a Calculated Column
  • Summary Calculations
  • AutoSum Calculations
  • Working with Dates in a PowerPivot Environment
  • Creating a KPI
  • Adding a KPI to a PivotTable

Module 7 - Working with Slicers

  • Layout Styles and using Slicers
  • Additional Slicer Settings
  • Locking a Slicer to PivotTables/Charts
  • Adding a Timeline to PivotTables/Charts
Performance Plus

1 Day

Duration
Microsoft Office
Delivery Method

Delivery method

Classroom / Attend from Anywhere

Receive classroom training at one of our nationwide training centres, or attend remotely via web access from anywhere.

Trusted, awarded and accredited

Fully accredited to ensure we provide the highest possible standards in learning

All third party trademark rights acknowledged.