All You Need to Know About

Excel Power Pivot Tutorials

Microsoft Power Pivot for Excel is a very powerful analytical tool which allows you to import data from various external sources. Learn more about Power Pivot here!

Here are the top things on what you can do with Power Pivot in Excel:

Linking Excel Tables in Power Pivot

When you have multiple tables, Power Pivot can help you link them together. After linking them together you can then create a Pivot Table that will give you a single view of data.

What we will focus on is a simple example of two Excel Tables: a Name Table and a Sales Table.

Linking Excel Tables 01

What we want to know is how much each Employee made in Total Sales. 

You can see that each employee is uniquely identified by the ID number, which is also used in the Sales table.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select your first table. Go to Insert > Table. Click OK.

Linking Excel Tables 02

STEP 2: Go to Design > Table Name  and give your new Table a descriptive name. In our example, we will name it Names

Linking Excel Tables 03

STEP 3: Select your second table. Go to Insert > Table. Click OK.

Linking Excel Tables 04

STEP 4: Go to Design > Table Name  and give your new Table a descriptive name. In our example, we will name it Sales

Linking Excel Tables 05

STEP 5: Select your first table. Go to Power Pivot > Add to Data Model. This will import your new Table into the Power Pivot Window.

Linking Excel Tables 06

STEP 6: Select your second table. Go to Power Pivot > Add to Data Model. This will import your new Table into the Power Pivot Window.

Linking Excel Tables 07

For Excel 2010, go to PowerPivot > Create Linked Table.

Excel 2010 - Create Linked Table

STEP 7: This will open Power Pivot Window. Your two Tables should already be loaded there.

Linking Excel Tables 08

Linking Excel Tables 09

STEP 8: Go to Design > Create Relationship.

Linking Excel Tables 10

STEP 9: Ensure for Table 1, you set Names = ID and for Table 2, you set it to Sales = ID.

This will set the relationship and your Sales table will be able to see the values in the Names table.

Linking Excel Tables 11

For Excel 2010, you can do the same using the Create Relationship Dialog Box:

Excel 2010 - Relationship Dialog Box

STEP 10: With this, our setup is complete.  Now it’s time to create a Pivot Table to do our analysis.

Within the PowerPivot Window, go to Home > PivotTable

Select New/Existing Worksheet and press OK

Linking Excel Tables 12

STEP 11: This will create a new Pivot Table within your Excel worksheet.

In the ROWS area put in the Name field from the Names Table,  in the VALUES area you need to put in the Sales Amount field from the Sales Table:

Linking Excel Tables 13

STEP 12: We now have the Names and the Total Sales Amount all in one Pivot Table.

We were able to link and consolidate two Excel Tables together with no need for VLOOKUP or helper columns…thanks to Power Pivot!

Linking Excel Tables 14

Enabling Power Pivot in Excel 2013

Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel.

So what is Power Pivot?

In a nutshell, Power Pivot allows you to use multiple data sources for analysis.

Power Pivot gives you the power to work with large sets of data that are over 1 millions rows!!!

You could import, merge and perform analysis on the resulting data. The beautiful thing with Power Pivot is it allows you to work on Big Data with no limitations.

Imagine getting data from multiple sources like SQL Server, Oracle, XML, Excel, Microsoft Access and analyzing these all into one awesome Pivot Table!

So how do I get my hands on this super awesome add-in…I hear you say?

The good thing with Power Pivot is it already comes with your Excel 2013, we need to enable it to start using Power Pivot.

You just need to make sure you have Office Professional Plus 2013 as either:

Office 365 ProPlus Subscription or

Excel 2013 Standalone Download

STEP 1: Go to File > Options 

enabling 2013 01

enabling 2013 02

STEP 2: Go to Add-Ins, for the Manage dropdown select COM Add-ins. Click Go once set.

enabling 2013 03

STEP 3: Check Microsoft Office PowerPivot for Excel 2013. Click OK once done.

enabling 2013 04

STEP 4: You should now have the Power Pivot Tab in your ribbon menu.

PP

Installing Power Pivot in Excel 2010

STEP 1: The first step is to check the Excel bit version that you have on your PC.

To check the bit version, you need to go and open any Excel workbook and go to File > Help and on the far right hand side you will see the Version and the bit number in brackets, either (32-bit) or (64-bit).  Once you know this, close out of Excel completely!

excel 2010 file help

STEP 2: You will need to click on the following link and download the Power Pivot add-in from Microsoft’s website:

Download Power Pivot for Excel 2010

installing 2010 02

STEP 3: Press the Download button and you will need to select the download bit version based on the bit system your Excel 2010 version is running (from Step 1).

If your Excel is 32-bit, pick the x86 installer.

If your Excel is 64-bit, pick the amd64 installer:

installing 2010 03

Press the NEXT button…

installing 2010 04

…and this will start the download of the add-in installer .msi file installer on your browser (if you do not see this, go to your browser’s Downloads folder).

STEP 4: Click on the .msi file to run the installer:

installing 2010 05

STEP 5:  This will bring up the Microsoft Power Pivot for Excel Setup:

installing 2010 06

Click the Next button and accept the terms of the Licence Agreement and press Next again:

installing 2010 07

Click Next and select the Install button, wait for the Install to finish:

installing 2010 08

You will get the following message once your install has been completed successfully.  Press Finish!

installing 2010 09

STEP 6: Open a blank Excel workbook and on the ribbon menu you should now see the Power Pivot tab:

installing 2010 10

*** IF YOU CANNOT SEE THE POWERPIVOT TAB, CLICK HERE AND GO THROUGH STEPS 1-6 TO ACTIVATE IT ***

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]