BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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.
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 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:
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:
A pivot table showing the number of films by director and decade.
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.
Once you've installed PowerPivot for Excel 2010, you can get into it as follows:
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!