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).

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
   
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.

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.

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.

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.

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.

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.
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.
   

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