Excel Power Pivot is an incredibly useful tool for working with large complex sets of data.. Power Pivot is an Excel Add-on from Microsoft that compliments Power Query, and helps you import millions of rows of data, create relationships between tables, and blend data together for analysis.
While Microsoft Power Pivot has been around since Excel 2010, it’s not turned on by default in newer versions of Excel that come with Office or Microsoft 365.
We’ll Explain what Power Pivot is, why you should use it, and how to enable it in recent versions of Excel.
Let’s take a look!
Table of Contents
Power Pivot Explaind and What it Does
Power Pivot is an Excel add-in created by Microsoft to help users analyze large volumes of data. It works in a very similar way to Power BI, Microsoft’s modern business intelligence and reporting application. In fact, it shares the same processing engine. Power Pivot greatly extends the usefulness of Excel in a world where datasets continue to get larger and large, while taking longer and longer to process and analyze in a standard spreadsheet.
Benefits of using Power Pivot
- Import millions of rows from multiple data sources like SQL Server and Oracle into a data model
- Compress data into a highly optimized vertipaq engine to enhance performance
- Create relationships between different imported tables for unified analysis
- Build pivot tables and charts that update outputs as you slice data
- Refresh connected datasets easily to keep analysis up-to-date
Power Pivot is perfect for data analysts and people working with datasets that would otherwise be impossible to process efficiently in the classic Excel interface.
How to Enable Power Pivot for Excel
To enable Power Pivot for Excel, navigate to File, Options, Add-ins, Manage: COM Add-ins, then select Microsoft Power Pivot for Excel. Once enabled a new section will appear in the Microsoft Excel Ribbon called “Power Pivot” with options to modify data models and create DAX measures.
Let’s break it down into more detail.
Step 1.) Open Microsoft Excel for Desktop and go to File, Options
Selection Options from the bottom left.
Step 2.) Navigate to the Add-Ins section and select Manage “COM Add-ins”
By default you will see the option called “Manage Excel Add-Ins” at the bottom of the page. Use the Excel add-ins drop down menu to adjust this to COM add-ins.
Then press OK.
Step 3.) Enable Microsoft Power Pivot for Excel
Check the box to the left of Microsoft Power Pivot for Excel, and click OK. This will enable the COM add-ins. You can alternatively navigate to this section and uncheck any of the enabled COM add-ins.
Note: Excel COM (Communication Object Model) add-ins are designed to assist in process automation and offer Excel developers more advanced functionality than is typically available with a standard add-in.
Step 4.) Launch Power Pivot from the Excel Ribbon
After enabling Power Pivot, you can create a new workbook or go to the workbook you currently have active and should see a new tab at the top in the Excel Ribbon called “Power Pivot”.
This tab gives you the ability to launch the data model editor, create formulas or measures using DAX (Data Analysis Expressions) and work with other aspects of your dataset.
How to Identify Excel Version
To identify which version of Microsoft Excel for Desktop you are currently using, go to File, Account. On the right side of the page there is an “About Excel” section that lists the version and build number currently in use. Click the “About Excel” button to view your license key and other important detailed information.
If you have an older version of Excel, options might look slightly different. If you’re running a version of Excel prior to 2010, Power Pivot and other recent features may not be available and you will need to upgrade to a more recent version.
Power Pivot is Not Available in Excel Online
The Power Pivot add-in is only available for the desktop version of Excel running on Windows. It requires the Vertipaq analytics engine to compress data which is not supported in the online versions. Excel Online also only supports the ability to refresh Power Query models that must be created in Excel for Desktop at this time.
Power Pivot is a great addition to Microsoft Excel and significantly extends the capabilities of the world’s most popular business intelligence tool. If you find yourself running into row limits or need the ability to dynamically join or merge multiple tables together, we highly recommend giving it a try.
Another benefit of Power Pivot that’s often overlooked is that the skillsets learned in Excel are very similar to those used in Microsoft Power BI. Being familiar with one platform can be a great help if you or your organization choose to migrate to a more modern reporting solution at some point in the future.