An Introduction to Power Pivot in MS Excel 2013

An Introduction to Power Pivot in MS Excel 2013
Page content

Getting Started

Power Pivot for Excel was introduced in Excel 2010 as a free add-on product. Power Pivot for Excel 2013 is still a free add-on, but you must have Office 2013 Professional Plus or Office 365 Professional Plus in order to use it.

To enable Power Pivot in Excel 2013, you need to open a new spreadsheet and browse to the File -> Options menu. In the Options Window click Add-Ins. From there, next to the “Manage” field, click COM Add-Ins and then click the GO button. Check the box next to “Microsoft Office PowerPivot for Excel 2013” and click OK (Figure 1).

You should be taken back to your blank spreadsheet and you should now see the PowerPivot menu tab (Figure 2).

Figure 2 PowerPivot Tab

In order to show off the power of PowerPivot I’m going to be using a set of sample data available from Microsoft CodePlex site.

Setting up your Data

We’ll load up the data from the sample ‘Stores’ Excel file so we can manipulate it.

1. Click on the PowerPivot menu and then click on Manage.

2. Navigate to Get External Data -> From Other Sources -> Excel File. Click Next.

3. Browse for the sample data file and be sure to check the ‘use first row as header’ checkbox.

4. Click Test Connection and verify Excel can connect. Click Next.

5. Excel will show you the table it will be pulling data from. You can further filter the data by clicking the Filter button (Figure 3).

Figure 3 Table Preview

6. Click Finish and the Close. Your data should now show up in the Manage Data window (Figure 4).

Figure 5 Calculated Column

For our scenario we want to investigate the relationship between the number of employees a store has and the number of acres of land served by the store. To do this we need to create a calculated column dividing the number of acres by the number of employees.

1. To do this, click Insert Function.

2. Select the Divide function under All Functions.

3. You will see a new column added to the data model. Select the SellingAreaSize column, add a comma and then select the EmployeeCount column. Hit enter and the column will calculate the values (Figure 5).

Figure5 CalculatedColumn

4. Double click on the heading for the new column and give it a proper title.

Creating a Pivot Chart

Once you have data in the Excel Data Model you can choose from a number of designs to create Pivot Charts or Tables. In the Excel Data model, click the drop down arrow on the Pivot Table button on the Home menu tab. Select the option to create your Pivot Chart in a new worksheet.

You should now see a blank Pivot chart on the left side of the screen and a list of fields you can add in the PivotTable fields section.

From here select the StoreName and our new ‘Employee to Acre ratio’ field. As you can see in Figure 6 we have a few outliers – both are online stores and thus serve a very large geographical area.

Figure 6 PivotChart

Let’s exclude these form our chart. To do so, click and drag the ‘Employee to Acre ratio’ field to the filters area. You should notice a new drop down arrow appear on the chart. Click the drop down and then click the ‘Select multiple items’ checkbox. Deselect the two highest values. We can now see which stores may be understaffed (Figure 7).

Figure 7 Final

Although this was a very brief outline of using Power Pivot, keep in mind the tool is extremely powerful and can easily handle huge amounts of data. If you downloaded the sample data you can follow a full tutorial on Microsoft’s site to further learn about Power Pivot’s capabilities.