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

  1. 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.
  2. 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
  3. 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.
  4. 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.

L2WD banner ad

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.

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

Share?

Comments

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

    • Well there is good news and bad news. The good news is yes – version control issues disappear with Excel 2013. But the bad news is there are a whole lot of new problems. There are quite some usability features that are actually better in 2010 than 2013. The 2 that annoy me the most are 1) there is no button in Excel to insert a pivot table that is connected to your data model. That was a subject of another of my posts here https://exceleratorbi.com.au/vba-to-insert-a-power-pivot-table/

      The second thing is that you can’t right click on a measure and edit it. You have to go to the “Custom Field” dialogue box and find the measure in the list. This is just a waste of time, particularly when the list gets long.

      Yes Excel 2013 32 bit works, and most people that come to my training have this installed. It works up until the point that it doesn’t – ie somewhere around 500k – 1m rows of data. Once you realise that you can bring in translational data and do great reporting that will refresh in a few seconds, you will never want to go back to aggregated/summarised data as your source. This is why I think you should get 64 bit if you can.

  2. 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 as MS jettisons all 32 bit software.

    • Excel 2013 Power Pivot Workbooks will open on a previous version of Excel (eg 2010) but you cannot interact with the Pivot Table. So you can distribute it with a Pivot Table already refreshed (effectively a static report) but that’s it. There is no way to backwards convert a 2013 data model to a 2010 data model. It is irrelevant if the Excel 2010 version has Power Pivot installed or not – you will get the same behaviour (static report).

      If you have anyone that needs to interact with the data model in 2010, then you should produce the workbook in 2010. They are easy to convert to 2013, so you can update the 2010 version and then convert a copy of the workbook to 2013 for 2013 users. The only “issue” is if you have cube formulae in your workbook. These require specific additional conversion, but this can be done with a simple Ctrl-H to replace the text “Power Pivot Data” with “ThisWorkbookDataModel” or something like that anyway.

      BTW, the workbook file formats are independent of 32 bit or 64 bit. The file format is the same, just the computer capacity to process it is different.

      Microsoft has a long history of maintaining backwards compatibility with old software, particularly software that is pervasive. So even though they may stop making 32 bit, I think you will find that 32 bit software will continue to work on Windows 64 bit. File formats change all the time and they are normally only convertible one way – you can upgrade but not downgrade.

  3. 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 on my machine and use a separate computer to develop some share-able dashboards while I wait for a move out of the dinosaur era.
    Appreciate any thoughts / suggestions
    Mike

    • Unfortunately yes – this sounds very plausible. The good news is that at least your IT department knows what it is talking about. I don’t recal the fine details, but I remember a similar issue of DB and SharePoint version conflicts when I was working at Coke.

      i don’t know anything about migrating from v1 to v2 – this is 4 years old now and I wasn’t using PP the. I know migrating from 2010 to 2013 is a one way thing but I don’t know if this is true for v1 to v2. It is probably worth testing if you have access to both on 2 machines. It is at least plausible that you could create workbooks in V2 and host them on a v1 server but I don’t know. One thing for sure – the new functions that were first released in v2 won’t work on v1. I think DISTINCTCOUNT is one, but I am sure there are more.

      If you are able to test, please post back your findings. If anyone else knows, please post the info.

  4. 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 pop up message stating “Microsoft Power Query for Excel requires Microsoft Office 2010 SP1 or MS Excel 2013. Install MS Excel 2010 SP1 or MS Excel 2013”.

    Second, when trying to install the Power Pivot add-in I got another pop up message saying “If you have 32 bit Excel you must install the 32 add-in version”. **However there is no 32 bit version of this add-in in the web site, just 64 bit version.

    Can you help me fix these problems ? Thank you.

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

    • There is no backward compatibility from 2013 to 2010. There is backwards compatibility from 2016 to 2013 though, but of course that doesn’t help you. There are really only a couple of options
      1. Redo the workbooks in 2010
      2. Upgrade the client to 2013+
      3. Host the 2013 workbook on a server and share it that way.

      • Hi Matt,

        Could I ask you to elaborate about option 3? I have a model in Excel 2013 that I would like to share to Excel 2010-users. How do I do this? I had the idea that the users could use the model but not do changes to the model. However I would like to have the model updated automatically since it uses data from our ERP-solution. Some facts:

        The model has approx. 100.000 lines in total distributed in 8-10 tables.

        • It is not possible to interact with an Excel 2013 workbook in Excel 2010. You could try to publish the workbook to Power BI and then either share from there or you could try Analyze in Excel. If the latter works for both versions, you could rewrite the front end reports in the new thin workbook and distribute that instead. I cover Analyze in Excel in a recent blog post.

          • Hi Matt, one question more. I have rewritten the model in Excel 2010. What is the best way to distribute to people having Excel 2010 but not Powerpivot installed?

          • Soren, there is no 1 best way – it depends on your situation. If your users can install Power Pivot on their own PC, then that would be a good approach. If IT will support you, even better. You could put a download link on the front page of the workbook.

            If the workbooks are small (say under 50MB), and you have access to o365 SharePoint with the BI features, you could upload the workbook and configure it like a dashboard (read this article about sharepoint in general) https://exceleratorbi.com.au/using-sharepoint-enterprise-with-power-pivot/

            These days you have the option to publish the Excel to Power BI. From there you can browse in Excel, build new visuals in Power BI, or use the Analyze in Excel features (read about that here) https://exceleratorbi.com.au/power-bi-analyze-excel-what-you-need-to-know/

  6. 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 this too hard…
    Thanks for your help.
    Ryan

    • The latest versions/links can be found here https://exceleratorbi.com.au/power-downloads/

      The latest version is Power Pivot for Excel 2010 v2 SP2. Sorry I don’t know which build is the latest but I assume the links have the latest build (your link works fine for me by the way).

      To be fair to Microsoft, Excel 2010 is 6 years old and has been superseded by 2 complete releases since then. I realise this may not help you given your corporate environment setup, but the issues of backward compatibility have actually been managed pretty well in my view. Power Pivot was built and released after Excel 2010 was released. The fact that MS made it available at all was a good thing.

      What is the actual issue you are having?

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

    • Thanks Rachel for sharing your experience. We all go through this learning experience – I remember many years ago being stumped. But I guess once you ‘know it’ you end up forgetting that once you didn’t know – unless you go through pain like you did. I have updated the post to reflect this and hopefully save someone else some pain.

  8. 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 size of 100,276 KB. The incorrect version, Version 1, has a size of 93,284 KB. Otherwise, it is impossible to identify one from the other. Everyone: carefully examine any versions you download to insure you actually received Version 2.

  9. 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 if it was to be undertaken due to licencing issues etc.

    Also if I were to only have my PC set up with 64bit excel 2010 and PowerPivot 64bit, what implications would that have when other users with only 32 bit excel 2010 and 32bit PowerPivot add-in when querying the data and using slicers etc. ?

Leave a Reply to Søren Amstrup Cancel reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x