One very common question people ask is “which versions of Excel come with Power Pivot”? Microsoft doesn’t make it easy to find out the answer. Well here is a table that shows you exactly which SKU you need to purchase if you want to get Power Pivot (and some of the other key Power Products I love).
Now note that Power Pivot for Excel 2010 is a free download and will work with any installed version of Excel. If you have Excel 2013 or above, then the following table will tell you which SKU you have to purchase to get Power Pivot.
In Excel 2013, you need Office Professional Plus to author Power Pivot reports, but you can interact with a report produced by someone else if you have Office Professional. In Excel 2016, you only need Office Professional to author reports, and you can interact with a report produced by someone else in Home and Office Edition 2016.
Do I Need 32 bit or 64 bit?
64 bit computers have been around for many years. The 32 bit architecture is quite old now and 64 bit is a lot more powerful. There are actually 4 technical layers that can be configured with either 32 bit or 64 bit, but each layer depends on the previous layer.
Starting at the bottom and working up, if you ever install 32 bit, then you can’t install 64 bit at a higher layer. But the reverse is not true. You can normally install 32 bit on top of a 64 bit layer (except for – the plug-in itself).
Let’s discuss each layer
- Most hardware is 64 bit these days (If your hardware is 32 bit, then time to get a new PC). If you have 32 bit hardware, then that’s it – you have no choice but to install 32 bit software on all other layers. But if you have 64 bit hardware you do have a choice – so read on.
- On 64 bit hardware, there are 2 choices for the operating system layer. You can install 32 bit Windows or 64 bit Windows (it has to be Windows if you want to use Power Pivot). If you have 32 bit Windows, you cannot install 64 bit Office and hence you can’t install the 64 bit Plug-in for Power Pivot. If you are not sure which version of Windows you have, then go into Control Panel\All Control Panel Items\System and take a look at the system type.
Assuming you have 64 bit Windows, you then have a choice of 32 bit Office or 64 bit Office – both will work. Many (dare I say most) IT departments will install 32 bit Office by default even if you have 64 bit Windows. The main reason is that it is an “all or nothing” decision. You either have the entire MS Office suite as 64 bit, or you have it all as 32 bit. So that means if you want 64 bit Excel, you have to take 64 bit Outlook, 64 bit PowerPoint, Word etc. Now none of these other programs “NEED” 64 bit to operate. It is like buying a Ferrari to go shopping each week. In addition there is another problem. If you install 64 bit MS Office, then you cannot use ANY 32 bit Plug-in for ANY MS Office application. Lots of companies use MS Office Plug-ins for Outlook, Excel and other software, and this can be a problem. The more main stream Plug-Ins have 64 bit versions available but there is some work to do to find these and get them installed, and without a good reason – why would you do it? Well Power Pivot IS a good reason to do it – possibly the ONLY reason to do it!! So that’s why many companies will have 32 bit as their default MS Office install.
If you have MS Office 64 bit installed, then you must install the 64 bit plug-in for Power Pivot. If you have 32 bit Office, you have to use the 32 bit plug-in.
Do I really need the 64 bit version of Power Pivot?
The short answer is it depends on how much data you want to use. The 32 bit version will work fine up to a size of between 0.5 million and 2 million rows. It can be more or less depending on the structure and shape of your data. You may think that this sounds like a lot of data, but keep in mind that we have never been able to process large volumes of data in Excel in the past, and that drives our perception of what is “a lot of data”. If you needed to use data in these quantities in the past, you had to move to a database tool, or for most Excel users, you needed to get IT to do it for you. That is not the case any more with Power Pivot for Excel 64 bit. I regularly use workbooks with 50 million rows of data on my PC in Excel and it works a charm – as long as you have 64 bit Office.
In fact I would go as far to say that Power Pivot for Excel is the ONLY Office application that needs (and can benefit from) 64 bit. If you have a large workbook and are using 32 bit, it simply won’t work – it will throw an error.
What will happen in many cases is people will start using 32 bit. As soon as they realise how super powerful Power Pivot is to them and their job, they will start inventing new ways of doing fabulous things they never could do before. Before too long, you will get the error message telling you that the 32 bit version can’t handle your request.
So if you have a choice, you should work with your IT Department to get the 64 bit version installed. This will save time and rework for everyone down the track. And note that this applies to everyone that wants to open the workbook on their PC. So if you create a 2 million row Power Pivot workbook on your 64 bit version of MS Office and then send the workbook to a user that has the 32 bit version of Office, they will be able to open the workbook but they will not be able to interact with the Pivot Table – it simply won’t work.
And then there is the NT Core Patch/Hack
I recently discovered (thanks to powerpivotpro.com) that it is possible to patch/hack Excel 32 bit so that it is capable of using 4GB of memory instead of the standard 2GB. I have never tried it, but I understand that it first creates a backup of Excel.exe and then creates another copy of excel.exe that will work with 4GB of memory. If you are out of options and game, then give it a go. Please post a comment below how it works out for you.
Power Pivot for Excel 2010 – there are 2 Versions
Power Pivot 2010 was written as a plugin as part of the development of SQL Server 2012. It was built in 2 distinct versions (V1 and V2). V1 lacks a lot of features that were later released in V2, so you should never install V1 today. The latest build of V2 is available for download at the Microsoft website. The full version name is “Microsoft® SQL Server® 2012 SP2 PowerPivot for Microsoft Excel® 2010”. Don’t be spooked by the reference to SQL Server. This is the correct version and it works on Excel 2010 – you don’t need SQL Server to make it work. Depending on how “locked down” your PC is, you may be able to install it yourself without IT support. I suggest giving it a try. It will either install or it wont – either way you will know.
How to Check if you have Power Pivot
How to Check your Power Pivot for Excel 2010 Version
Now you may have got to this point and are now wondering which version you have installed. Well unfortunately you can’t simply go to Control Panel\Programs\Programs and Features to find out which version is installed. But there is an easy way to check. You first need to make sure the Plugin is enabled. In Excel, got to File\Options\Addins. Then right down the bottom select “Manage Com Add-ins” from the list and press go. You will then see this dialogue.
If you can see the Power Pivot Add-in, then you know it is installed. Make sure the check box is selected so you can use it. Same applies for Power Query.
Then go back to Excel and go to the Power Pivot Ribbon and click on the “Power Pivot Window” button.
One of the main differences between Power Pivot V1 and V2 is the Diagram View. So navigate to the home tab (which is the default) and check to see if you can see a button for “diagram view” (see image below). If you have this button, then you have V2 – which is good. If you don’t have this button, then you have V1 and it needs to be replaced with the latest version.
How to check if you have Power Pivot for Excel 2013
With Excel 2013, you either have Power Pivot or you don’t. There is no AddIn available. If you don’t have it then you will need to purchase a different version of Excel that does have it. To check if your version of Excel 2013 has Power Pivot, you need to do the following:
- Navigate to File\Options\Addins
- Locate the option to “Manage” and switch to “COM Add-ins”, then click Go.
Next you will see a list of available Add-ins. If you have Power Pivot, it will be visible in this list. Place a check mark next to the plugin and click OK.