Introduction to PowerPivot in Excel 2013

This blog post was brought to you by Diego Oppenheimer a Program Manager on the Excel team.

Some weeks ago I introduced an important, new part of the Excel 2013 Data Model and how you could create relationships between different data sources right in your Excel workbook (http://approjects.co.za/?big=b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx). In today’s post I am going to show you how you can expand on this Data Model and add all sorts of analysis “goodies” using the PowerPivot add-in that’s built into Excel 2013.

NOTE: If you are on the consumer preview of Office 2013 then the add-in is available. Once you upgrade to the full version you will need Office 2013 Pro Plus for this functionality to be available.

Activating the add-in

The simplest way to activate the PowerPivot add-in is to just insert a PowerView sheet from the ribbon:

This will automatically create an empty Data Model, insert a blank PowerView canvas (http://approjects.co.za/?big=b/microsoft-excel/archive/2012/10/04/intro-to-power-view-for-excel-2013.aspx) and activate the PowerPivot ribbon.

If this all worked all right, your ribbon should have a Powerpivot tab at the end:

Another route is to go to File-> Options -> Add-Ins and then on the bottom dropdown select COM Add-ins:

Once inside you can enable Microsoft Office PowerPivot for Excel 2013.

PowerPivot vs. Data Model: I am confused

Let’s take a step back to explain a little bit further the differences in functionality (especially for those of you who have used PowerPivot in the past).

In previous versions of the PowerPivot add-in (Office 2010), the add-in was a standalone application that was user downloaded and installed. The add-in came with two components: an in-memory data analysis engine and a modelling user interface.

In Excel 2013, the engine component has been directly integrated, and we refer to it as the Data Model engine (the technology is actually called xVelocity). All the basic components of the Data Model like import data and create relationships are available for ever every version of Excel, but the extra modelling component is reserved for those with Office 2013 Pro Plus.

The extra modelling component has a ton of features that anybody who is serious about data analysis will want to have (don’t take my word for it try it yourself!), including:

· Pre-filtering data when importing

· DAX expression language for intuitive calculations

· Rename tables and columns as you import data in PowerPivot

· Use the Diagram View to manage your model and create relationships

· Define calculated fields and key performance indicators

· more

Adding some more data analysis to my housing search

As my starting point, I am going to use the file I used for my last blog post. I open the PowerPivot window by simply clicking on the Manage button in the PowerPivot ribbon. As soon as you open it, you will notice a very similar interface to Excel. This is reading directly the Data Model I created last time.

To get a better view, check out Diagram View on the top right. You should see something like this:

Diagram View is a great way to understand your entire Data Model, see what fields are related to what others and even create new relationships by dragging from one field on a table to another field in another table.

Switching back to Data View, I am going to add a calculation that’s going to show me any price changes since the last time the house was sold. To do that I simply right click, insert new column and then start building my expression. Because I do not have last sale prices for all my properties I need to make sure I am not dividing by zero. My expression looks something like this:

=IF ([LAST SALE PRICE] <>0, [LIST PRICE]/ [LAST SALE PRICE], 0)

In plain English, if the value in the column [LAST SALE PRICE] is NOT 0 then return [LIST PRICE] divided by [LAST SALE PRICE], else return 0.

If I go back to my PivotTable report, and add it, and change its aggregation to average, I now get an idea of what the price movement has been for the different zip codes.

By applying some quick formatting like I showed the in the previous post, I see that I have 2 zip codes for which my average house has appreciated over 200% — not something to base my entire decisions on but yet another good data point to have when making the big decision. Another interesting thing to look at is that the properties I am interested in Seattle have lost about 6% of their value since they were last sold.

Note: I want to point out that none of the “conclusions” or observations I am making can be taken just as is. My data set is only looking at properties I personally picked, houses that have been “flipped” increase their price dramatically, etc. Doing data analysis is not just about performing the analysis but also understanding what we are looking at –just a little disclaimerJ.

Adding some calculated measures to my model

One of the great things about the PowerPivot add-in is that it allows for really easily creating measures, which are based on the aggregates of any given column. What if I want to know what the Maximum and Minimum List Prices are for each zip code?

By going into the PowerPivot add-in, and selecting one of the cells at the bottom of the grid (this is called the calculation area because this is where we define calculations!) I can input a simple formula like:

MaxPrice:=MAX([LIST PRICE])

and another

MinPrice:=MIN([LIST PRICE])

Go back to the PivotTable, add those two fields, and there you go: Max and Min for every zip code.

So much Powerpivot, so little time

In this blog post I covered a very simple introduction as to how the Powerpivot add-in has changed since 2010, and how it canbe used, but I really haven’t even scratched the surface. Before I leave you, I would like to share some links where you can get all sorts of goodies and learn more about PowerPivot than you ever imagined. Some of these are Microsoft, some are from our good friends and hardcore PowerPivot enthusiasts who stretch the capabilities of our product in really amazing ways:

Microsoft Business Intelligence

What’s new in PowerPivot in Excel 2013

PowerPivotPro.com by Rob Collie

PowerPivotGeek.com by Dave Wickert

Kasper de Jonge’s PowerPivot blog

And as always feel free to ask me questions in the comments of the blog or on Twitter (@doppenhe).

–Diego