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.
Advantages of using Power Pivot
- Model and analyse data from multiple sources (workbooks, databases etc.)
- Handle datasets larger than the 1 million row limit of standard Excel
- Create advanced measures for use in Pivot Tables
- Office 365: Office 365 ProPlus, Office 365 Enterprise E3, Office 365 Enterprise E5
- Excel 2016: Office Professional 2016, Office 2016 Professional Plus, Excel 2016 Standalone
Installing Power Pivot
- Office 365/Excel 2016: No download required, must enable add-in in Excel
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.