Sign in or start a free trial to avail of this feature.
1. Overview and Installation
Power Pivot is only available in certain Excel versions. Learn how to get Power Pivot in this lesson.
To explore more Kubicle data literacy subjects, please refer to our full library.
What is Power Pivot? (00:04)
Power Pivot is a structured data model, like a database, contained within Excel. In this model, data is stored in a structured, tabular format. You can import tables of data from multiple different sources into Power Pivot, then link these tables together to form a data model. You can then analyze the data model using Pivot Tables and Pivot Charts.
Power Pivot Advantages (01:20)
There are several advantages of using Power Pivot over standard Excel tools:
- With Power Pivot, you can create Pivot Tables and Pivot Charts using multiple tables of data. You don’t need to use VLOOKUP or other tools to get all your data into one table.
- Power Pivot handles larger data sets (about 2 billion rows) than standard Excel (just over 1 million rows).
- Power Pivot lets you perform more advanced calculations on your data than the simple options (like sum, average etc.) that are available through regular Pivot Tables.
Power Pivot Availability (02:44)
Power Pivot is not available in every version of Excel. Currently, Power Pivot is available with the following Excel versions:
- Office 365: All versions of Office 365 include Power Pivot
- Excel 2016: Office 2016 Professional Plus, Excel 2016 standalone
To check if Power Pivot is available for your version of Excel, check this page of the Microsoft website.
Enabling the Add-In (03:20)
You may need to enable the Power Pivot add-in before you start using Power Pivot. To do this, we select the File tab from the ribbon, then Options, and Add-ins. We select COM Add-ins and Go. At this point, we check the box to enable the Power Pivot Add-In.
In this course, we'll introduce you to the functionality available in Power Pivot. Power Pivot is a data modeling tool introduced in Excel 2010. This tool creates a structured data model in Excel similar to a database, turning Excel into a self-service business intelligence platform. In this first course, we'll create a Power Pivot data model and then analyze it using Pivot Tables and Pivot Charts. First, let's describe Power Pivot in more detail. Power Pivot introduces a data model into Excel, storing data in a structured tabular format. Using Power Pivot, you can view multiple tables of data from various sources. You can then create relationships that link these tables into a single model.
You can also add to your data model by creating calculated columns or fields offering new insights. Once you've created a data model, you can then output your data to the Pivot Tables or other reporting tools, such as Power Maps or Power View. In this course, we'll focus on outputting our data to Pivot Tables. If you have no experience with Pivot Tables we recommend that you watch the Kubicle course on Pivot Tables before continuing with this one. So what are the advantages of using Power Pivot over normal Excel? First, Power Pivot has the ability to handle multiple tables of data. When you build Pivot Tables in Excel, you generally need all the fields of interest to be in a single sheet. Instead of using V lookup functions or other methods to combine data from multiple sheets, Power Pivot allows you to analyze data from multiple sources in a single Pivot Table. Second, Power Pivot can handle much larger data than regular Excel. Excel spreadsheets are limited to just over one million rows. Although this may sound large, one million rows run out quickly when you're working with databases. In Excel, you also may run into performance issues before reaching one million rows, especially when using functions like V lookup. Standard Excel is simply not built to analyze very large datasets. Power Pivot, however, can handle up to two billion rows and is designed to perform well with large datasets. Finally, Power Pivot is useful when your analysis requires more advanced calculations. When you add a field to a Pivot Table, you can perform simple calculations, such as sum, average, and so on. If you want to conduct more advanced calculations, such as percentage changes, you'll need to use Power Pivot. Let's look at how to access Power Pivot. Power Pivot is only available for certain versions of Excel 2016.
Specifically, Office 365 Pro Plus, Office 265 Enterprise E3, Office 365 Enterprise E5, Office Professional 2016, Office 2016 Professional Plus, and Excel 2016 standalone.
If you have another version of Excel 2016, unfortunately you do not have access to Power Pivot. If you have access to Power Pivot, you will find that it's built into Excel. You simply need to enable the Power Pivot add-in. To do this, we'll navigate to file, then options, then add-ins.
In the manage box, we'll select com add-ins and go.
We'll then check the box to enable Power Pivot. It's also a good idea to check the box for Power View as well, as we'll use it later on. Select okay, and we can see that there's now a Power Pivot tab in the ribbon indicating the add-in has been successfully enabled. Now that we have access to Power Pivot, we'll start by loading data in the next lesson.