Power Pivot is a data modeling technology in Excel that lets you create data models, establish relationships, and create calculations. With Power Pivot, you can work with large data sets, build relationships between tables, and create complex (or simple) calculations in a high-performance environment—all within Excel.
Today, Power Pivot is part of Excel’s modern data modeling experience, which also includes tools like Power Query and the Excel Data Model. The same underlying technology is also used in Power BI. For most scenarios, especially in Microsoft 365, you can create and use data models directly in Excel without needing to open the Power Pivot window.
Most users working with data models in Excel today use Power Query to load and shape data, and the Data Model to relate it, with Power Pivot used for advanced modeling and calculations.
Power Pivot resources
How do I get Power Pivot?
Power Pivot is available as an add-in for Excel and is built into the Excel Data Model experience in modern versions of Excel. You can enable Power Pivot in older Excel versions by following a few simple steps. The underlying modeling technology found in Power Pivot is also found in the Power BI Designer, which is part of the Power BI service offered from Microsoft.
Getting started with Power Pivot
When the Power Pivot add-on is enabled, the Power Pivot tab in the ribbon is available, as shown in the following image.
From the Power Pivot ribbon tab, select Manage from the Data Model section.
When you select Manage, the Power Pivot window appears, which is where you can view and manage the data model, add calculations, establish relationships, and see elements of your Power Pivot data model. A data model is a collection of tables or other data, often with established relationships among them. The following image shows the Power Pivot window with a table displayed.
The Power Pivot window can also establish, and graphically represent, relationships between the data included in the model. By selecting the Diagram view icon from the bottom right side of the Power Pivot window, you can see the existing relationships in the Power Pivot data model. The following image shows the Power Pivot window in Diagram view.
For a short tutorial about how to use Power Pivot, take a look at the following resource:
In addition to that tutorial, the following link provides an extensive set of links, resources, and additional information on Power Pivot:
In the following sections, you'll find additional resources and tutorials that go into detail about how to use Power Pivot, and how it can be combined with Power Query and Power View to perform powerful, intuitive self-service business intelligence tasks in Excel.
Power Pivot tutorials
Seeing Power Pivot in action can help you learn how to use it, and provide helpful use cases that demonstrate the power of Power Pivot. The following tutorials can help you get started:
-
Create a Data Model in Excel (starts with a basic data model, refines with Power Pivot)
-
Import Data into Excel, and Create a Data Model (first in a six-part end-to-end tutorial series)
Additional information on Power Pivot
Power Pivot can be simple to use and fast to execute. It also can create powerful and complex calculations, indicators, and formulas. Take a look at the following links to get oriented to the many things you can do with Power Pivot.
-
Create a memory-efficient Data Model using Excel and the Power Pivot add-in
-
Power Pivot: Powerful data analysis and data modeling in Excel (data model comparison)
-
Workbook Size Optimizer (download)
For an extensive set of links and helpful information, visit Power Pivot Help.
Forums and related links
Take a look at the following resources to get involved with others in the Power Query community.
