PowerPivot for Excel Tutorial Introduction

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

Welcome to the Microsoft PowerPivot for Excel tutorial for creating your first PowerPivot workbook.

PowerPivot is an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance. To complete this tutorial, you need the PowerPivot for Excel Tutorial Sample Data.

Start the tutorial now: Add Data to Your PowerPivot Workbook (Tutorial).

Tutorial Scenario

In this tutorial, you will complete a scenario for analyzing international electronic sales. The fictitious company, Contoso, is used for all examples.

Imagine you are an analyst for an electronics company called Contoso Electronics. You want to examine sales over time and compare sales by product type, year, and country. During the course of this tutorial, you will use PowerPivot to:

  • Import data from multiple sources.

  • Create linked data.

  • Create relationships between data from different sources.

  • Rename columns, and create calculated columns.

  • Create hierarchies.

  • Create PivotTables and PivotCharts.

  • Add Slicers.

  • Create a measure and KPI.

  • Create perspectives.

  • Save the resulting Excel spreadsheet.

Prerequisites

Make sure that the following are installed:

  • Microsoft PowerPivot for Excel

  • Microsoft Excel 2010

For more information, see Install PowerPivot for Excel. Additionally, to follow along with the tutorial, you need the PowerPivot for Excel Tutorial Sample Data – Version 2. The sample data that is used in this tutorial is from the fictional company Contoso and is stored in Access databases and Excel worksheets. If you prefer to import the same data from a SQL Server database instead, download and restore the full Contoso database to a server. Depending on the Contoso version, there could be minor differences in the data, but you should be able to complete the tutorial successfully.

For Windows XP

In Windows Server and Windows 7, features in the PowerPivot window are available on a ribbon. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

What You Will Learn

This tutorial is divided into the following lessons:

Add Data to Your PowerPivot Workbook (Tutorial)

In these lessons, you will learn how to import data from a variety of data sources, including Access databases and Excel worksheets.

Save Your PowerPivot Workbook (Tutorial)

In this lesson, you will learn the difference between saving your Excel workbook and saving your PowerPivot workbook.

Create Relationships Between Tables (Tutorial)

In this lesson, you will learn how to view and create relationships between the data that you have imported. By creating relationships between data from different sources, you will be able to perform an analysis on your whole set of imported data.

Create a Calculated Column (Tutorial)

In this lesson, you will create a new column that is based on data in the table. This calculated column will later be used in PivotTables and PivotCharts.

Create a Hierarchy in a Table (Tutorial)

In this lesson, you will learn how to view and create hierarchies from table columns in Diagram View.

Create a PivotTable from PowerPivot Data (Tutorial)

In this lesson, you will use PivotTables to represent your data in a way that enables you to analyze the data in detail.

Create a PivotChart from PowerPivot Data (Tutorial)

In this lesson, you will use PivotCharts to graphically represent your data. PivotCharts also help in analyzing your data in detail.

Add Slicers to PivotTables (Tutorial), and Add Slicers to PivotCharts (Tutorial)

Slicers are one-click filtering controls that narrow the portion of a data set shown in PivotTables and PivotCharts. In these lessons you will learn how to add Slicers to your PivotTables and PivotCharts to interactively filter and analyze data.

Create a Measure and KPI (Tutorial)

A measure is a formula that is created specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. A Key Performance Indicator (KPI) is designed to help evaluate the current value, status, and trend of a metric.

Create a Perspective (Tutorial)

A perspective is a subset of items from the underlying model that help you create individualized data sets for particular groups of users. In this tutorial, you create a perspective that contains store, geographic, and business metric objects.

Next Step

To continue this tutorial, go to the next topic: Add Data to Your PowerPivot Workbook (Tutorial).

See Also

Concepts

Introducing PowerPivot for Excel

Learn About PowerPivot Capabilities

Take a Tour of the PowerPivot UI

Watch Videos About PowerPivot Tasks

Get Sample Data for PowerPivot

What's New in PowerPivot