One very common question people ask is “which versions of Excel come with Power Pivot”? Until recently not all Microsoft Office products included Power Pivot. But Microsoft listened to the community and made an announcement that Power Pivot will be made available in “all” Windows editions (“SKUs”) of Excel starting from April 2018! However there are still exceptions with some of the Excel 2013 versions and boxed versions purchased prior to Office 2019. See the link above for more details.
Power Pivot for Excel 2010 – there are 2 Versions
The Power Pivot add-in for Excel 2010 did not ship with Office, but is available as a free download: Power-Pivot add-in download.
This free download works only with Excel 2010, and not with newer versions of Excel.
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.
Do I Need 32-bit or 64-bit?
Another common question people ask is “Do I Need 32-bit Office or 64-bit Office to use Power Pivot?”.
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, 64-bit 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 rows 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.