Create Your First PowerPivot Workbook (Tutorial)

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.

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.
 Create PivotTables and PivotCharts.
 Add Slicers.
 Save the resulting Excel spreadsheet.

Prerequisites

Make sure that the following are installed:

 Microsoft
 Microsoft Excel 2010

For more information, see Install PowerPivot for Excel. Additionally, to follow along with
the tutorial, you will 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 WindowsXP

In 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 In this lesson, you will learn how to import data from a variety
PowerPivot Workbook of data sources, including Access databases and Excel
(Tutorial) worksheets.
Save Your PowerPivot In this lesson, you will learn the difference between saving your
Workbook (Tutorial) Excel workbook and saving your PowerPivot workbook.
In this lesson, you will learn how to view and create
relationships between the data that you have imported. By
Create Relationships
creating relationships between data from different sources, you
between Tables (Tutorial)
will be able to perform analysis on your whole set of imported
data.
In this lesson, you will create a new column that is based on
Create a Calculated Column
data in the table. This calculated column will later be used in
(Tutorial)
PivotTables and PivotCharts.
Create a PivotTable from In this lesson, you will use PivotTables to represent your data in
PowerPivot Data (Tutorial) a way that enables you to analyze the data in detail.
Create a PivotChart from In this lesson, you will use PivotCharts to graphically represent
PowerPivot Data (Tutorial) your data. PivotCharts also help in analyzing your data in detail.
Slicers are one-click filtering controls that narrow the portion of
Add Slicers to PivotTables
a data set shown in PivotTables and PivotCharts. In these
(Tutorial), and Add Slicers
lessons you will learn how to add Slicers to your PivotTables
to PivotCharts (Tutorial)
and PivotCharts to interactively filter and analyze data.

Add Data to Your PowerPivot Workbook (Tutorial)

As with any Excel workbook, in PowerPivot for Excel you can add data from a wide variety
of data sources, including: a relational database, a multidimensional database, data feeds, an
Excel table, or a Reporting Services report. You can also add data from files on your local
computer, and use data that you download from the Web.

However, unlike Excel, you can then create relationships between these data to form a single
data set, and then perform analyses against this data. You are not limited to one million rows
either -- PowerPivot enables you to add and work with millions of rows of data locally,
depending on the actual physical memory available on your desktop computer.

What You Will Learn

PowerPivot supports importing data from a variety of sources, including: SQL Server
databases, Analysis Services cubes, Access databases, Excel worksheets, text files, data
feeds, and more. For a full list of supported data sources, see Data Sources Supported in
PowerPivot Workbooks.
In most cases, you will use the Table Import Wizard to import data. The wizard guides you
through the process of setting up a connection to a data source and choosing what data to
import, including selecting a subset of the rows and columns in the data source. When
importing data from a database, the wizard also enables you to specify a custom query to
import exactly the data that you want.

You can also import data by pasting it from an Excel worksheet or by linking directly to an
Excel worksheet. In the following topics, you will see how to use the Table Import Wizard
and these other methods.

1. Add Data by using the Table Import Wizard (Tutorial)

2. Add Data by using a Custom Query (Tutorial)
3. Add Data by using Copy and Paste (Tutorial)
4. Add Data by using an Excel Linked Table (Tutorial)

Next Step

To continue this tutorial, go to the next topic: Add Data by using the Table Import Wizard
(Tutorial).

Add Data by using the Table Import Wizard (Tutorial)

In this lesson, you will use the Table Import Wizard to connect to a database, select the data
to load, and import that data into your PowerPivot workbook.

PowerPivot can import data from a variety of relational sources: Access, Oracle, Sybase,
Informix, DB2, Teradata, and more. The process for importing data from each of these
relational sources is very similar to what is described below.

Additionally, data can be loaded using a custom query or stored procedure. You will do this
in the next lesson.

Keep in mind that in order to use this data in your analysis, you must create relationships
between the data from the relational database and the other data in your PowerPivot
workbook. Instructions for creating relationships are provided in a later lesson. For more
information, see Create Relationships between Tables (Tutorial).

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

Create a Connection to an Access Database

1. Navigate to the location on your computer where you downloaded the samples and
double-click Stores. You will use the stores data in a later lesson.
2. In the Excel window, on the PowerPivot tab, click PowerPivot Window.
 3. In the PowerPivot window, on the Home tab, click From Database and select From
Access. This launches the Table Import Wizard which guides you through setting up a
connection to a data source.
4. In the Friendly connection name field, type ContosoDB from Access.
5. In the Database name field, click Browse. Navigate to the location where you
downloaded the sample files, select ContosoSales, and click Open.
6. Click Next and verify that Select from a list of tables and views to choose the data to
import is selected.
7. You want to select from a list of tables and views, so click Next to display a list of all
the source tables within the database.
8. Select the check box for the following tables: DimChannel, DimDate, DimProduct,
DimProductSubcategory, and FactSales.

Now that you have connected to the database and selected the tables to import, go to the next
section headed Filter the Table Data prior to Importing.

Filter the Table Data prior to Importing

The FactSales and DimProduct tables that you are importing from the Access database
contain a subset of the data from the original SQL Server Contoso database: sales and
products from two categories (games and home appliances) are not included. You will apply
a filter to one of the other tables before importing it, so that you eliminate the same
categories. You will also filter out some of the columns from the DimProduct table.

Filter the Table Data prior to Importing

1. Select the row for the DimProduct table and click Preview & Filter.
2. The Preview Selected Table window opens with all the columns in the DimProduct
table displayed.
3. Clear the checkboxes at the top of the columns for all the columns from ClassID
through StockTypeName (a total of 15 columns) and then click OK.
4. Notice that the words Applied filters are now displayed in the Filter Details column in
the DimProduct row; if you click on that link you’ll see a textual description of the
filters that you just applied.
5. Now select the row for DimProductSubcategory and click Preview & Filter.
6. Since you’re interested in only some of the products, you will apply a filter so that
you import only the data for these categories.

At the top of the ProductCategoryKey column, click the arrow and deselect 7 and 8.
Categories 7 and 8 include games and home appliances, and you don't want to include
those in your analysis.

7. At the top of the ProductSubcategoryDescription column, clear the checkbox. Since

the descriptions are almost identical to the names, there is no need to import both
columns, and eliminating unnecessary columns will make your workbook smaller.
Click OK.

Import the Selected Table and Column Data

Finally, import the selected data. The wizard imports the table relationships along with the
table data. For more information about relationships, see Creating Relationships Between
Tables.

Import the Selected Table and Column Data

1. Review your selections. If everything looks OK, click Finish.

While importing the data, the wizard displays how many rows have been fetched.
When all the data has been imported, a message indicating success is displayed.
Notice that you imported more than 2 million rows from the FactSales table alone.

2. Click Close.

The wizard closes and the PowerPivot window is visible. Each table has been added
as a new tab in the PowerPivot window. If the data changes at the source, you can
keep the data imported into the PowerPivot window up-to-date by using data refresh.
For more information, see Refreshing or Changing Imported Data.

Next Step

To continue this tutorial, go to the next topic: Add Data by using a Custom Query (Tutorial).

Add Data by using a Custom Query (Tutorial)

In this task, you will connect to a Microsoft Access database and use a custom query to
import data into your PowerPivot workbook. An Access database is a relational data source.
PowerPivot can import data from a variety of relational sources: SQL Server, Oracle, Sybase,
Informix, DB2, Teradata, and more.

Importing data from the Access database involves connecting to the database, locating the
query file, and running the query to import the data into your PowerPivot workbook.

Keep in mind that in order to use this data in your analysis, you will have to create
relationships between the data from the Access database and the other data in your
PowerPivot workbook. Instructions for creating relationships are provided in a later lesson.
For more information, see Create Relationships between Tables (Tutorial).

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

To Create a Connection to an Access Database

 1. In the PowerPivot window, on the Home tab, click From Database and select From
Access. This starts the Table Import Wizard which guides you through setting up a
connection to a data source.
2. In the Friendly Name box, type Product Category Access DB.
3. In the Database name field, click Browse. Navigate to the location where you
downloaded the sample files, select ProductCategories, and then click Open.
4. Click Next.

Use a Query to Select the Data to Import

The procedure for using the Table Import Wizard to import by selecting from a list of tables
was described in the previous lesson in this tutorial. So instead of duplicating those steps, you
will use a custom query to import this set of Access data.

The Table Import wizard guides you through the steps. You can either import a query, copy
and paste the text of an existing query, or write a new query by using the PowerPivot
graphical query builder. In this lesson, you will import a query that you received from the IT
department.

To Use the Query Designer Tool to Select the Data to Import

1. Select the Write a query that will specify the data to import option, and then click
Next.
2. In the Friendly Query Name box, type Product Category Query.
3. Click Design to open the Query Builder window.
4. Click Import and navigate to the location on your computer where you saved the
samples.
5. If you do not see the file listed, click the file down arrow and select All files (*.*).
6. Select SQLQuery and then click Open.
7. The SQL Statement appears in the window. This query selects all data from the
ProductCategory table except GAMES and TOYS and HOME APPLIANCES.
8. Click OK, Validate, and then Finish. A summary of the columns you are importing is
displayed.
9. When the import is finished, click Close.

The data is displayed as a new table named Product Category Query in your
PowerPivot workbook. You can keep this data current by refreshing. If the contents of
the Access database change, refreshing keeps your PowerPivot data up-to-date. For
more information, see Refreshing or Changing Imported Data.

10. Rename the new table by right-clicking the Product Category Query tab and selecting
Rename. Type ProductCategory and then click Enter.

Next Step

To continue this tutorial, go to the next topic: Add Data by using Copy and Paste (Tutorial).
Add Data by using Copy and Paste (Tutorial)

In this task, you will add data to your PowerPivot workbook by copying it from a Microsoft
Excel worksheet and pasting it into the PowerPivot window.

Keep in mind that in order to use this data in your analysis, you will have to create
relationships between the data from the Access database and the other data in your
PowerPivot workbook. Instructions for creating relatioships are provided in a later lesson. For
more information, see Create Relationships between Tables (Tutorial).

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

Copy and Paste from an External Excel Worksheet

The Sales department has an Excel spreadsheet that contains data about the location of areas
where Contoso is currently selling products. You will copy the data that you need from this
worksheet, and paste it into your PowerPivot workbook.

To Copy and Paste from an External Excel Worksheet

1. Navigate to the location on your computer where you downloaded the samples and
double-click Geography. This opens a new Excel worksheet and you are no longer in
the PowerPivot window.
2. Highlight and copy cells A1 through J675.

Note
Be sure to select only this range of cells and not whole rows and columns. Selecting
whole rows and columns imports empty cells, which might affect your ability to
create relationships with this data.

3. Back in your PowerPivot window, on the Home tab, click Paste.

4. The Paste Preview dialog displays the new table that will be created. Make sure that
the table data is correct, select Use first row as column headers, and then click OK.
5. The new table is created in the PowerPivot window. To rename the table, right-click
the tab, click Rename, and then type Geography.

Next Step

To continue this tutorial, go to the next topic: Add Data by using an Excel Linked Table
(Tutorial).

Add Data by using an Excel Linked Table (Tutorial)

A Linked Table is a table that has been created in a worksheet in the Excel window, but is
linked to a table in the PowerPivot window. The advantage of creating and maintaining the
data in Excel, instead of importing it or pasting it in, is that you can continue to modify the
values in the Excel worksheet, while you are using the data for analysis in PowerPivot.

In this task, you will create a link from the Stores data in the worksheet in your Excel
window to the table in the PowerPivot window. This means that any change made to the
source data will be automatically updated in your linked table in the PowerPivot window.

Keep in mind that in order to use this data in your analysis, you must create relationships
between the data in the linked table and the other data in your PowerPivot window. For more
information, see Create Relationships between Tables (Tutorial).

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

Create a Linked Table

In the previous task (Add Data by using Copy and Paste (Tutorial)) the Geography table is
static because after you pasted the data, it is not automatically updated. Linked tables, on the
other hand, automatically reflect changes that were made to the source. Linked tables must
reference Excel data found in a separate Excel window within the same PowerPivot
workbook.

To Create a Linked Table

1. In the Excel window, put your pointer in any of the cells of the Stores worksheet and
format it as a table (CTRL+T). Be sure to select My table has headers.
2. The new table that will appear in the PowerPivot window always has the same name
as the table in Excel. Therefore, you should give the Excel table a meaningful name
before you create the linked table in PowerPivot. By default, Excel automatically
generates names for tables (Table1, Table2, etc) but you can easily rename tables by
using the Excel interface.
1. Click the Design tab.
2. In the Properties area, under Table Name: type Stores.
3. On the PowerPivot tab in Excel, click Create Linked Table. This opens the
PowerPivot window and you can see that a new table has been created – notice the
Link icon on the tab.
4. In the PowerPivot window, on the Linked Table tab, click Go to Excel Table to return
to the source table in the Excel window. Change the value in cell C2 from 35 to 37.
5. Return to the PowerPivot window; and you'll see that the corresponding row has now
been updated to the new value as well.

Next Step

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

Save your PowerPivot workbook for use in future tutorial lessons.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

Save your PowerPivot Workbook

1. In the Excel window, click the File tab.

2. Click Save As. The Save As dialog box opens.

You will save the workbook as the default file type of Excel Workbook (*.xlsx). For a
list of supported file types, see Learn About PowerPivot Capabilities.

3. In the File name text box, type PowerPivotTutorialSample and then click Save.

Next Step

Congratulations, you have created your first PowerPivot workbook! In the next lesson, Create
Relationships between Tables (Tutorial), you'll learn how to create relationships between the
tables of data you've imported.

Create Relationships between Tables (Tutorial)

In this lesson you will use PowerPivot to view and create relationships between data from
different data sources. A relationship is a connection between two tables of data that
establishes how the data in the two tables should be correlated. For example, the DimProduct
table and the DimProductSubcategory table have a relationship based on the fact that each
product belongs to a subcategory. For more information about relationships, see
Understanding Relationships.

Relationships in PowerPivot are created either by manually joining tables in the

PowerPivot window, or automatically if PowerPivot for Excel detects existing relationships
when importing data into a PowerPivot workbook. A relationship is created between two
tables by joining columns that contain similar or identical data. For example, the DimProduct
and DimProductSubcategory tables are related by the ProductSubcategoryKey columns that
occur in both tables. The columns do not have to have the same name, but they often do.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).
Why Create Relationships?

In order to perform any meaningful analysis, your data sources must have relationships
between them. More specifically, relationships enable you to:

 Filter data in one table by columns of data from related tables.

 Integrate columns from multiple tables into a PivotTable or PivotChart.
 Easily look up values in related tables using Data Analysis Expressions (DAX)
formulas.

Review Existing Relationships

You already have data from three different sources in your PowerPivot workbook:

 Sales and product data imported from an Access database. Existing relationships were
automatically imported for you together with the data.
 Product category data imported from an Access database.
 Data copied from, and linked to, an Excel spreadsheet that contains store information.

To Review Existing Relationships

1. In the PowerPivot window, on the Design tab, in the Relationships group, click
Manage Relationships.
2. In the Manage Relationships dialog box, you should see the following relationships,
which were created when the first Access database was imported:

Table Related Lookup Table

DimProduct DimProductSubcategory
[ProductSubcategoryKey] [ProductSubcategoryKey]
FactSales [channelKey] DimChannel [ChannelKey]
FactSales [DateKey] DimDate [Datekey]
FactSales [ProductKey] DimProduct [ProductKey]

3. Click Close.

Create New Relationships between Data from Separate Sources

Now that you have reviewed the relationships that were created automatically, you will create
additional relationships.

To Create Your First Relationship

1. Click the Stores tab.

2. Right-click the GeographyKey column header and select Create Relationship.

The Table field and the Column field are auto-populated.

3. In the Related Lookup Table field, select Geography.

 4. In the Related Lookup Column field, select GeographyKey.
5. Click Create.
6. When the relationship is created, an icon displays at the top of the column. Pause the
pointer over the icon to display the relationship details.

To Create More Relationships between the data from Access and Excel

1. Click the Stores tab.

2. Select the StoreKey column.
3. On the Design tab, click Create Relationship.

The Table field and the Column field are auto-populated.

4. In the Related Lookup Table field, select FactSales.

5. In the Related Lookup Column field, select StoreKey.

Notice the icon next to the Related Lookup Column field. This tells you that this
relationship is being created in the wrong order. When you create a relationship, you
must select a column with unique values for the Related Lookup Column.

6. Reverse the order. Move FactSales to the Table field and select StoreKey as the
Column. Select Stores as the Related Lookup Table and select StoreKey as the
Column.
7. Click Create.
8. Repeat the steps for DimProductSubcategory (as the table) and ProductCategory (as
the lookup table), using the ProductCategoryKey column in both tables.
9. Check that all relationships have been successfully created by clicking Manage
Relationships and reviewing the list.

Next Step

To continue this tutorial, go to the next topic: Create a Calculated Column (Tutorial).

Create a Calculated Column (Tutorial)

In this lesson you'll create new data in your PowerPivot workbook by adding a calculated
column. A calculated column is based on data that already exists in your PowerPivot
workbook. For more information, see Create a Calculated Column.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

Create a calculated column

1. In the PowerPivot window, select the FactSales table.

 2. On the Design tab, in the Columns group, click Add.
3. In the formula bar above the table, type the following formula. AutoComplete helps
you type the fully qualified names of columns and tables, and lists the functions that
are available. For tips on how to use AutoComplete, see Building Formulas for
Calculated Columns and Measures.

=[SalesAmount] - [TotalCost] - [ReturnAmount].

4. When you have finished building the formula, press ENTER to accept the formula.

Values are populated for all the rows in the calculated column. If you scroll down
through the table, you will see that rows can have different values for this column,
based on the data that is in each row.

5. Rename the column by right-clicking CalculatedColumn1 and selecting Rename

Column. Type TotalProfit, and then press ENTER.

Next Step

To continue this tutorial, go to the next topic: Create a PivotTable from PowerPivot Data
(Tutorial).

Create a PivotTable from PowerPivot Data (Tutorial)

Once you've added data to your PowerPivot workbook, PivotTables help you efficiently
analyze your data in detail. You can make comparisons, detect patterns and relationships, and
discover trends.

This tutorial assumes that you are already familiar with using PivotTables and PivotCharts. If
not, see the following topics on Microsoft Office Online for an introduction:

PivotTable and PivotChart reports

Overview of PivotTable and PivotChart reports

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

Add a PivotTable to Your Analysis

You'll continue using the PowerPivot workbook you created in the previous tasks. It already
has the data imported and relationships created. Now you will add a PivotTable.

Important
Always create PivotTables from the PowerPivot window or the PowerPivot tab in the Excel
window. There is also a PivotTable button on the Insert tab in the Excel window, but standard
Excel PivotTables cannot access your PowerPivot data.

To Add a PivotTable to Your Analysis

1. In the PowerPivot window, on the PowerPivot Home tab, click PivotTable.

2. Select New Worksheet.

Excel adds an empty PivotTable to the location you specified and displays the
PowerPivot Field List. The Field List displays two sections: a field section at the top
for adding and removing fields, and a section at the bottom for rearranging and
repositioning fields.

3. Select the empty PivotTable.

If you get an error message telling you the data list is no longer valid, right-click the
table and select Refresh Data.

4. In the PowerPivot Field List, scroll down and locate the FactSales table.
5. Select the SalesAmount field. Ensure this field displays in the Values window of the
Field List.
6. In the DimChannel table, select the ChannelName field. Ensure this field displays in
the Row Labels window of the Field List.
7. In the DimDate table, select the CalendarQuarter field. In the PivotTable Field List,
move this field from the Values box into the Column Labels box.
8. Rename the PivotTable by double-clicking Sum of SalesAmount in the first cell,
erasing the current text, and typing Sales by Channel.
9. Format the display of the data to make it easier to read and compare. Highlight the
data cells, right-click and select Format Cells. In the Format Cells window, select
Currency and make sure that 2 is selected in the Decimal places dropdown. Select $
for Symbol. Click OK. Change column widths if numbers aren’t readable.

The Sales by Channel PivotTable lists the sum of sales for Contoso and for each Sales
Channel, by quarters from first quarter 2007 through fourth quarter 2009.

To Add Another PivotTable to Your Analysis

1. In the Excel window, on the PowerPivot tab, click PivotTable.

2. Select New Worksheet.

Excel adds an empty PivotTable to the location you specified and displays the
PowerPivot Field List.

3. Select the empty PivotTable.

If you get an error message telling you the data list is no longer valid, right-click the
table and select Refresh Data.
 4. In the PowerPivot Field List, scroll down and locate the FactSales table.
5. Select the TotalProfit field. Ensure this field displays in the Values window of the
Field List.
6. In the PowerPivot Field List, locate the ProductCategory table.
7. Select the ProductCategoryName field. Ensure this field displays in the Row Labels
window of the Field List.
8. In the PowerPivot Field List, locate the DimDate table.
9. Select the CalendarYear field. In the PivotTable Field List, drag this field from the
Values window into the Column Labels window.
10. Rename the PivotTable by double-clicking Sum of TotalProfit in the first cell, erasing
the current text, and typing Profit by Category.
11. Format the display of the data to make it easier to read and compare. Highlight the
data cells, right-click and select Format Cells. In the Format Cells window, select
Currency and make sure that 2 is selected in the Decimal places dropdown. Select $
for Symbol. Click OK. Change column widths if numbers are not readable.

The Profit by Category PivotTable lists the sum of profits, by year, for each Contoso product
category.

These are simple analyses of your data. To dig deeper, you will add a PivotChart and Slicers.

Delete a PivotTable

Keep the PivotTables in the workbook in order to complete the tutorial, but if you want to
delete a table at some point, follow these steps.

To Delete a PivotTable

1. Click inside the PivotTable.

2. In the PivotTable Tools tab, select Options.
3. In the Actions group, click Select.
4. Select Entire PivotTable. On the Home ribbon select Delete and then click Delete
Sheet.

Next Step

To continue this tutorial, go to the next topic: Create a PivotChart from PowerPivot Data
(Tutorial).

Create a PivotChart from PowerPivot Data (Tutorial)

Once you've added data to your PowerPivot workbook, PivotCharts help you efficiently
summarize, analyze, explore, and present your data. PivotCharts provide an interactive
graphical representation of your data and help you see comparisons, patterns, and trends.

This tutorial assumes that you are already familiar with using PivotTables and PivotCharts. If
not, see the following topics on Microsoft Office Online for an introduction:
PivotTable and PivotChart reports

Overview of PivotTable and PivotChart reports

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

Add a PivotChart to Your Analysis

You'll continue using the PowerPivot workbook you created in the previous tasks. It already
has the data imported, relationships created, and PivotTables added.

Important
Always create PivotCharts from the PowerPivot window or the PowerPivot tab in the Excel
window. There is also a PivotChart button on the Insert tab in the Excel window, but standard
Excel PivotCharts cannot access your PowerPivot data.

To Add a PivotChart to Your Analysis

1. Start on the worksheet that contains your Sales by Channel PivotTable.

2. On the PowerPivot tab in Excel, click the arrow below PivotTable and select
PivotChart.
3. Select Existing Worksheet and click OK.

Excel adds an empty PivotChart to the same worksheet that contains your Sales by
Channel PivotTable.

4. Select the empty PivotChart and in the PivotChart Tools tab, select Design and then
Change Chart Type.
5. Select the first chart in the Line group and then click OK.
6. In the FactSales table, select the SalesAmount field. Ensure this field displays in the
Values window of the Field List.
7. In the DimChannel table, select the ChannelName field. In the PivotTable Field List,
move this field from the Axis Fields box into the Legend Fields box.
8. In the DimDate table, select CalendarQuarter. In the PivotTable Field List, move this
field from the Values box into the Axis Fields box.

The PivotChart and PivotTable now display the same data – in very different layouts.

9. Format the display of the data to make it easier to read and compare. Right-click the
axis numbers and select Format Axis.
1. Click Number and in the Category list, select Currency.
2. Set Decimal places to 0, and then click Close.
3. Right-click the Sum of SalesAmount axis label and select Value Field
Settings.
4. Change Custom Name to Sales by Channel and then click OK.
To Add Another PivotChart to Your Analysis

1. Start on the worksheet that contains your Profit by Category PivotTable.

2. On the PowerPivot tab in Excel, click the arrow below PivotTable and select
PivotChart.
3. Select Existing Worksheet and click OK.

Excel adds an empty PivotChart to the same worksheet that contains your Profit by
Category PivotTable.

4. In the FactSales table, select the TotalProfit field. Ensure this field displays in the
Values window of the Field List.
5. In the ProductCategory table, select the ProductCategoryName field. Ensure this field
displays in the Axis Fields window of the Field List.
6. In the PivotChart Tools tab, select Design and click Change Chart Type.
7. Scroll down and select the first Pie chart type and click OK.
8. In the Chart Styles group, select the 3-dimensional style with a black background and
click OK.
9. On your pie chart, select the title (Total) and change it to Profit % by Category.
10. Next, you add and format data labels.
1. On your pie chart, right-click and select Add Data Labels.
2. Right-click again and select Format Data Labels.
3. Highlight Label Options, then select the Percentage checkbox and deselect
Value.
4. Click Close. Resize the chart to make sure that all product categories are
displayed.
5. Resize the chart to make sure that all product categories are displayed.
11. Save your PowerPivot workbook.

Note
This PivotChart displays percentage values for each product category. Because FactSales and
other tables are filtered, the percentages are percentages of total sales in six of the eight
categories of Contoso products.

These are simple analyses of your data. To dig deeper, you will add Slicers.

Delete a PivotChart

Keep the PivotCharts in the workbook in order to complete the tutorial, but if you want to
delete a chart at some point, follow these steps.

To Delete a PivotChart

1. To delete a PivotChart, click inside the PivotChart.

2. Right-click and select Cut.

Deleting the PivotChart does not delete any associated PivotTable reports or data.

Next Step
To continue this tutorial, go to the next topic: Add Slicers to PivotTables (Tutorial).

Add Slicers to PivotTables (Tutorial)

Slicers are one-click filtering controls that narrow the portion of a data set shown in
PivotTables and PivotCharts. Slicers can be used in both Microsoft Excel workbooks and
PowerPivot workbooks, to interactively filter and analyze data.

In this lesson you will use PowerPivot Slicers to control what data appears in your
PivotTable.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

Add Slicers to a PivotTable

To Add Slicers to the Profit by Category PivotTable

1. Click anywhere inside the Profit by Category PivotTable to display the

PowerPivot Field List.
2. In the PivotTable Tools area of the Excel ribbon, click Options.
3. Click Insert Slicer.
4. In the Insert Slicers window, locate the Geography table and select
RegionCountryName.
5. Under the DimChannel table, select ChannelName.
6. Under the ProductCategory table, select ProductCategoryName.
7. Under the DimProductSubcategory table, select ProductSubcategoryName.
8. Click OK.

Format Slicers

To Format Slicers

1. Arrange the slicers so that they can all be seen. To move the Slicers, click the gray
border and drag.
2. The title of the ProductSubcategoryName Slicer is truncated. To format this Slicer,
right-click the Slicer and select Slicer Settings.
1. In the Caption box, type Subcategory.
2. Verify that Display header is selected.
3. Click OK.
3. Sometimes Slicers must be resized in order to display their contents correctly. Resize
the RegionCountryName Slicer by adding columns.
1. Right-click the RegionCountryName Slicer and select Size and Properties.
2. Highlight Position and Layout.
3. In the Number of columns dropdown, select 4. Click Close.
 4. Drag the corners until all content is visible.
4. Continue to format your Slicers as needed.

Use Slicers to Analyze your PivotTable Data

To Use Slicers to Analyze your PivotTable Data

1. At Contoso we want to evaluate our sales profit trends by channel. Based on what we
discover, we may have to redistribute marketing budgets and/or close channels.
1. In the ChannelName Slicer, select Catalog. Catalog should now be the only
shaded item in the Slicer list.
2. By looking at the PivotTable you see that catalog sale profits are declining.
3. One at a time, click Online, Reseller, and Store to reveal those profit trends.
You see that online profits are increasing, store profits are decreasing and
reseller profits are decreasing slightly.
4. Clear the filters you have set by clicking the icon in the upper-right corner of
the Slicer.
2. Dig a bit further by slicing your profit data by subcategory and country. Here are
several interesting things that you might discover:
1. Profits have more than doubled for cellphone accessories, televisions, and
recording pens with the biggest percentage increase coming from online sales.
While most sales were made in stores, store profit percentage increase was the
lowest. Given that most sales come from stores, what can be done to maximize
profits from this channel?
2. Cellphone accessories profits took a significant jump in 2009 for the Reseller
and Store channels. Prior to that, profit increase was fairly flat in those 2
channels. To what can this jump be attributed?
3. Overall profits from the sales of televisions more than doubled from 2007-
2009. However, most of that increase came in 2008 with very little profit
increase seen in 2009. Why were profits flat in 2009 and how can they be
increased?
4. Sales of Desktops dropped significantly. With total profits of almost 260
million, only 21 million came from catalog sales. Catalog sales saw profits
drop from almost 10 million (2007) to 4 million (2009). Perhaps Contoso
should close this channel?
5. Profits in Asian countries are increasing whereas profits in European
countries, and North America are decreasing.

Contoso can use this information, and much more, to make intelligent business decisions.

Next Step

To continue this tutorial, go to the next topic: Add Slicers to PivotCharts (Tutorial).

Add Slicers to PivotCharts (Tutorial)

Slicers are one-click filtering controls that narrow the portion of a data set shown in
PivotTables and PivotCharts. Slicers can be used in both Microsoft Excel workbooks and
PowerPivot workbooks, to interactively filter and analyze data.

In this task you will use PowerPivot Slicers to control what data appears in your PivotChart.

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For
information about prerequisites and the first step in the tutorial, see Create Your First
PowerPivot Workbook (Tutorial).

To Add Slicers to a PivotChart

1. Click anywhere inside the Profit % by Category PivotChart to display the

PowerPivot Field List.
2. In the PivotChart Tools area of the Excel ribbon, click Analyze.
3. Click Insert Slicer.
4. In the Insert Slicers window, locate the DimDate table and select CalendarYear and
CalendarMonth.
5. Under the Geography table, select RegionCountryName.
6. Click OK.

To Format Slicers

1. Arrange the Slicers so that they can all be seen. To move the Slicers, click the gray
border and drag the Slicers.
2. Sometimes Slicers must be resized in order to display their contents correctly.
1. Select the CalendarYear Slicer and resize it so that only 2007, 2008, and 2009
are displayed.
2. Right-click RegionCountryName and select Size and Properties.
3. Highlight Position and Layout.
4. In the Number of columns dropdown, select 6. Click Close.
3. By default, Slicer items are displayed in alphabetical and numeric order, with items
with no data displayed last. To change this view:
1. Right-click the CalendarMonth Slicer, and select Slicer Settings.
2. Uncheck Show items with no data last. Click OK.
4. Continue to format your Slicers as needed.

To Use Slicers to Analyze your PivotChart Data

1. Use the CalendarYear Slicer to explore profit by year. The PivotChart clearly shows
the profit share increase for COMPUTERS and TV and VIDEO (at the expense of
CAMERAS and CAMCORDERS) from 2007 to 2009. Profit share for the other
categories shows almost no fluctuation.
2. To dig even deeper, use the CalendarMonth Slicer. You will discover that CAMERAS
and CAMCORDERS had the highest profit share in the latter months of 2007.
3. Explore your data more by using the RegionCountryName Slicer. Clicking through
the countries reveals many interesting facts.
Contoso can use this information to make intelligent business decisions.