How to Correctly Install Power Pivot for Excel 2010 - Excelerator BI

How to Correctly Install Power Pivot for Excel 2010

As most subscribers of my blog would already know, I am a professional trainer of Power Pivot here in Australia. I do a lot of work for Corporations at their own premises with their own PC equipment. Not every company has made the move to Excel 2013, and in fact I sometimes find companies have to move from Excel 2007 to 2010 just to prepare for this training. There are a couple of traps when installing the Power Pivot for Excel 2010 Plug-in (and the Power Query Plug-in for that matter) and hence I am blogging these instructions to help people and companies get it right the first time. The main trap is getting the wrong version. But first a comment about 32 bit vs 64 bit.

32 bit vs 64 bit Power Pivot Versions

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.

Let me explain. 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).

4 layers

Let’s discuss each layer

Most hardware is 64 bit these days. I think I can safely say your hardware will be 64 bit. 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.

  1. 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 PanelAll Control Panel ItemsSystem and take a look at the system type
    windows
  2. 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.
  3. 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 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 (called x86 by Microsoft) will work fine up to a data limit of about 500,000 rows. It can be more or less depending on the structure of your data. You may think that 500,000 sounds like a lot, 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.

Power BI Online Training

Now Back to 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.

Note:  When you complete the download, the 32 bit version is called x86 and the 64 bit version is called amd64.

How to Check my 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 PanelProgramsPrograms 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 FileOptionsAddins. Then right down the bottom select “Manage Com Add-ins” from the list and press go. You will then see this dialog.

addins

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.

ribbon

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.

diagramview

Power Query Versions (applies to 2010 and 2013)

Power Query is still in development and at the time of this post it is in a monthly update cycle. This means it is very easy to have an old version installed. To check which version you have, click on the Power Query ribbon, then select About to get the following dialog.

pq

The latest version of Power Query can be downloaded from the Microsoft Website here. Note that it is the same download for Excel 2010 and 2013.

Power Query Online Training

You now need to check if Power Query works

Power Query is a quirky thing. It requires a number of additional pieces of software to work, and one of them is a recent version of Internet Explorer. In fact it doesn’t matter if you use Internet Explorer or not – you must have a recent version of Internet Explorer installed or Power Query will not work.

I have found the best way to test if Power Query is installed properly is to do the following.

  1. Make sure you have installed the Plug-in. Refer to “How to Check my Power Pivot for Excel 2010 Version” above to find out how to do this.
  2. Turn on the Plug-In, open a new workbook and then navigate to the Power Query Ribbon.
  3. Click on the “from Web” button and type in any web page URL. I use abc.net.au because it is short and sweet.pq web
  4. Now one of 2 things will happen. If the Navigator pane appears with a folder referencing your web URL and some other items like documents or tables, then you are all set to go. If you get an error message, then read the error message and install the missing software. You may need to repeat this process with more software before it finally works – depending on your system.
    abc

If you have had any version management issues with Power Pivot for Excel 2010 that are not covered here, I would love to hear from you in the comments.

If you want a comprehensive lesson on how to use Power Query, checkout my training course here

avatar
12 Comment threads
14 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
  Subscribe  
Notify of
Sérgio Silva
Guest
Sérgio Silva

I have EXCEL 2010 and, according to above instructions, alreay install PowerPivot and Power Query. I try to open dinamic Excel files but always receive this error message: “Initialization of the data source failed. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database”.
If I use EXCEL 2013 ou 2016, I have no problem at all.
Any suggestions?

Mark Jackson
Guest
Mark Jackson

Hi Matt, I have a x64 PC with 32bit Excel 2010. I need to work with multiple data sets (largest being just over 500k rows). I have downloaded the x86 (32 bit) PowerPivot plug-in and get the error message (not enough memory available on system) when trying to import the largest data set. Therefore, I believe I need the 64 bit version of excel 2010. In your experience working with companies and IT departments, is it easy to convince IT to have just one PC at least installed with 64 bit Office or is it generally a company wide initiative… Read more »

Ray Butner
Guest

Matt: Thank you so much for your clear and complete explanations. You are a lifesaver! I recently migrated to a new PC. PowerPivot would not work correctly. This was a critical problem because I rely on PowerPivot for many of my operations. I ultimately identified the problem as a Version 1 vs. Version 2 issue. For one reason or another the current Microsoft download links generally point to Version 1 copies of PowerPivot which do not work with 32 bit Excel 2010 running on Windows 10 or any models created with Version 2. The correct version, Version 2, has a… Read more »

Rachel
Guest
Rachel

Hi Matt,

Thank you for this very helpful post! As you have described, my company has installed Windows 2010 x64, but Office 2010 x32.

There is one thing I might add to your post for us relative noobs, the version of Microsoft Powerpivot for Excel 2010 x32 is called \x86\PowerPivot_for_Excel_x86.msi

The x86 really threw me for a loop and I just spent over an hour (and a phone call to Microsoft… fun times) to figure that fact out.

Ryan
Guest
Ryan

Matt, What is the latest version of PowerPivot for Excel 2010 & where can it be downloaded? Microsoft has disabled the links in this article: http://www.microsoft.com/en-US/download/details.aspx?id=43348 Our IT department cannot keep PowerPivot running on our machines. My control panel says that we have Microsoft SQL Server 2012 PowerPivot for Excel version 11.1.3000.0, which is V2 but I know there must be a newer build because I have version 11.1.3129.0 (installed in March 2013) on my home computer. A newer build might help get things working again, but if not, I’m about to give up on PowerPivot entirely… Microsoft has made… Read more »

Sherlyne
Guest
Sherlyne

Hi there,

I have a question. Apologies if this question has been asked and already addressed.

I have developed a Power Pivot in Excel 2013 for my client. However, my client only has Excel 2010 and nothing else (i.e. no Office 365, no Excel 2013).

What’s the workaround for this?

Kindly help!

Thanks!

Anderson Terngu
Guest
Anderson Terngu

Can you install power-pivot and power-query on windows 10 operating system?

Jose Quijada
Guest
Jose Quijada

Hey Matt, I need help for a course I’m currently taking. I’m studying business economics and right now I’m taking an Excel course for data analysis and visualization. The problem that I have is the following: There are two requested add-ins for the course: – Power Query add-in – Power Pivot add-in I have Excel 2010 installed and my MS Office version is 32 bits. My OS version is 64 bits though. I downloaded the two add-ins successfully but the problem arose when I tried to install the add-ins. First when trying to install the Power Query I got a… Read more »

Mike H
Guest
Mike H

Matt… Great post! On V1 vs V2. I agree that V2 is vastly superior and I’ve spent a lot of time developing dashboards in V2 tied back to our ERP. With some groveling I’ve got our corporate IT team to turn on Powerpivot in a development instance of Sharepoint 2010….BUT…they are telling me I must use V1 because our Sharepoint 2010 implementation is currently supported by SQL server 2008. Sound plausible? I do know that our ERP implementation backs onto 2008 though I could likely influence an upgrade. My thought is in the mean time to keep my V2 instance… Read more »

Dominik Petri
Guest
Dominik Petri

Hi Matt,

very good summary. I’ve learn’t a few things I didn’t know so far. Thanks for that!

You state that the current version is “Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010″.
But there is already SP2 available (released June 10, 2014):
http://www.microsoft.com/en-US/download/details.aspx?id=43348

Best regards from Germany
Dominik.

Anthony
Guest
Anthony

Hi Matt Hit the spot again, great information. A lot of Govt departments still run XP and Excel 2003 with a few PC’s running 32 bit Excel 2010. Can you tell me if how an Excel 2013 PowerPivot table ( less than 500,000 rows) will run on a 32 bit Excel 2010 PC that does not have PowerPivot loaded. Would it make a difference for the better if I created the data solution in Excel 2010 64 bit or just stick with 2013. Soon we will have Windows 10 and Excel 2016, is it true it may not backward compatible… Read more »

Doug
Guest

Excellent summary! I had this very problem today.

Thank you for explaining.

I love Power Pivot.

Question – do these problems all disappear by using Excel 2013? Can I use Excel 2013 32-bit or do I need the 64-bit version to avoid memory problems?

Scroll to Top