An overview of PowerPivot for Excel 2010
Part one of a six-part series of blogs

PowerPivot is a superb addition to Excel: it allows you to create pivot tables based on multiple tables taken from a variety of data sources, and its DAX language lets you report any statistic you want. This overview summarises how to get started with PowerPivot, and what it does.

  1. Getting started with PowerPivot for Excel 2010 (this blog)
  2. Stage 1 - Importing the tables we need
  3. Stage 2 - Creating a data model
  4. Stage 3 - Creating a Pivot Table
  5. Stage 4 - creating measures using DAX
  6. Stage 5 - KPIs in PowerPivot for Excel

Posted by Andy Brown on 31 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Getting started with PowerPivot for Excel 2010

Having recently taught my first course in PowerPivot, I thought I'd use this blog to share my enthusiasm for this fantastic product!  The aim of this blog is to give an overview of what PowerPivot is and does, and to get you started using it.

Our mission

Assume that you have two tables of data: one in Word and one in SQL Server.  You want to link them together and use them as a basis for a pivot table, and make the whole thing as easy to use as possible.

PowerPivot allows you to get data from almost any data source seamlessly, including flat files, Access, SQL Server, Azure and your clipboard.  I've chosen two random formats to show how easily PowerPivot will link them together.

Here are the two tables:

Word table directors SQL Server table films
Word table of directors SQL Server table of films

You can download the Word document and script to generate the SQL Server table here.  Given these tables, we want to end up with a simple data model:

Pivot table field list

The pivot table field list hides the fact that there are two tables, and renames all of the tables and fields.


We can then use this to create a simple pivot table:

Pivot table: films by director and decade

A pivot table showing the number of films by director and decade.


Version Notes

This blog is written for PowerPivot for Excel 2010, which available as a free download from Microsoft.  In Excel 2013 some of the features of PowerPivot (such as the ability to connect to multiple tables) are integrated, but others (such as the ability to create measures and show KPIs) aren't.

Starting PowerPivot

Once you've installed PowerPivot for Excel 2010, you can get into it as follows:

PowerPivot icon in Excel

Click on the PowerPivot tab which appears in Excel, then click on the left-hand icon (PowerPivot Window).


Time to begin by connecting to the tables we need!

This blog has 0 threads Add post