What is it?
Microsoft came up with a series of tools all have names starting with “Power”. PowerQuery and PowerBI are just two examples. PowerPivot is one of the oldest in this series and although not many people may know about it, it’s actually one of the most powerful features of Microsoft Excel.
With Power Pivot, you can crunch millions of rows of data from multiple data sources, perform information analysis rapidly by creating relationships between heterogeneous data, creating calculated columns and measures using formulas. Interested? Let’s get started.
Most operations PowerPivot handle can be performed by Excel itself, however this will cost you time and will not work in all cases. PowerPivot gets help from SQL Server Analysis Services (SSAS) to run calculations effectively, and gives you access to an array of data analysis tools such as Power View, Power Map, and Excel’s conventional tools. PowerPivot also happens to accept DAX (Data Analysis Expressions) and manipulate data almost instantaneously regardless of your file size.
Getting Started
PowerPivot is available as a free add-in for Excel 2010 and is included natively in Excel 2013 and later. Excel 2010 users will need to download and install the free add-in from Microsoft’s website. Once installed, the PowerPivot ribbon will appear in the top menu. Power-Pivot add-in download
Excel 2013 and Excel 2016 users simply need to enable it from add-in options. To do this,
- Go to File > Options > Add-Ins.
- Under the Manage box, click COM Add-ins and press Go.
- Make sure the Microsoft Office Power Pivot box is checked and click OK. If you have other versions of PowerPivot installed, those versions will also be listed in the COM Add-ins list. Make sure you select the PowerPivot add-in for Excel.
Creating a Data Model
PowerPivot offers various options to import data into a data model. Let’s see how this works with an example. Here, we have two tables, one table contains a breakdown of finances by state, and the other matches state names with their abbreviations. You can download these workbooks here and here.
Let’s begin with starting the add-in. You can do this by clicking the Manage button under the PowerPivot menu.
PowerPivot for Excel window is where we can manage data connections and relationships between tables. You can find options for connecting to various sources such as Microsoft SQL Server, Microsoft Access, Oracle, Data Feeds, and even Excel or text files. In this example, we’re going to be connecting to an Excel file. When you’re ready, click From Other Sources button to go to the window where you can see all connection options.
Next, select the data source you’d like to connect to. We’re going to pull data from an Excel file, so select Excel File to proceed.
Click Browse to locate your source file. If your data includes column headers in the first row of your data file, make sure the Use first row as column headers is checked.
Click the check boxes next to the table (sheet) names to add them to the data model. We have two tables here, first for financial numbers which has the state abbreviations, and second one with full state names and abbreviations. If you connect to a relational database and your tables have relationships with other tables, remember to press the Select Related Tables button and this way the system will catch all necessary tables.
When you’re done, press Finish and wait for Excel to do its magic. Click Close to end the process and import the data into your spreadsheet.
Our data model is ready. You will see that the PowerPivot for Excel window is now filled with imported data. These tabs represent data tables and they behave differently than workbook sheets. At this point, you can manipulate the tables, refresh data or even create Pivot Tables or Pivot Charts.
Calculated Fields
PowerPivot also allows you to add calculated fields into your table. This is a pretty useful way to manipulate big sets of data. While calculated fields compute values row by row, measures aggregate values. To add calculated fields, you need to use Data Analysis Expressions (DAX). DAX is very similar to Excel formula, but are more suited for data analysis tasks. To create a calculated column follow these steps,
- Press Design in the toolbar and click Add under the Column
- Type in your formula. For example,
=IF([End Of Year Assets]>=[Average Assets],"Good","Bad")
- Rename the column by right clicking, and selecting Rename Column. Here, we changed the column name to “Status”.
- The new column will be added to the right of your table.
Relationships
Now let’s see how we can create relationships between tables. As our example goes, the main table contains the financial data and state abbreviations, and other one has full names. To match the two files and have our main table display state names instead of the abbreviations, we can establish relationships between the two.
Note: You might be asking yourself, why not just put everything together in a simpler way, like using formulas. However; if you have 10 tables, or billions of rows (or even worse both), every column will have a serious performance impact on calculations. This concept is called Data redundancy. To learn more, https://en.wikipedia.org/wiki/Data_redundancy
Begin by clicking Diagram View icon in the toolbar.
Here, you can manage relationship between tables, hierarchies and KPIs.
Next, we need to drag and drop the related fields into corresponding sections. Please note that the relationship direction should be either from one occurrence to one occurrence, or from many occurrences to many occurrences. In other words, many to one relations won’t work. If there are more than one instance for a value, you might get errors.
For example, in the Breakdown table some state names are used more than once, while in the States table we see a state name only once. This is in accordance with the relationship direction as it needs to be from State in Breakdown table to Abbreviation in the States table.
Next, let’s see how we can create visualizations from this table. Begin by clicking Pivot Table in the toolbar and create a Pivot Table, or click the arrow to see more options. In our example, we selected Chart and Table (Horizontal), but feel free to experiment.
We start from the Pivot Table to show how relationship works. From PivotTable Fields on the right hand side, move the State field from the States table, into the ROWS section. Then move the End of Year Assets in the Breakdown table, to VALUES section. Now we should have the full state names and our data on a table. To learn more about pivot tables and how awesome they are, see our How to Organize and Analyze Your Data Quickly with Excel’s PivotTables guide to learn more.
Pivot Charts work very similar to Pivot Tables. Simply drag and drop the data fields into AXIS (CATEGORY) sections and statistical fields into VALUES section.
PowerPivot is a hidden gem that is not known to all Excel users. It’s a very powerful tool that can handle a vast amount of data, while allowing you to manage connections and manipulate the data. It sure can make life much easier when dealing with data tables and their analysis.
