Excel 2013 logo - www.oostdam.infoThis is an article about converting, and setting up the use of your previous build Pivot table based Excel dashboards into the new Excel 2013 version. Now, in all previous office versions, you had to download the Pivot-table add-in separately, per machine or installation. Now, due to the big increase in BI solutions, that are becomming more and more available, Microsoft has decided to build in the Pivot table functionality which is now by default buildin in Excel 2013. However,... the functionality is not enabled by default.. Well,.. I can understand that too..  I guess it will be enabled by default in the next, Excel 2015 version... In this article I want to guide you through some steps that can be followed to upgrade and convert, your existing excelsheets towards Excel 2013. After following this almost step by step guide, you will be able to use your imports, graphics and all those build-in filters again. 


Let's start with the error that appears on your screen when you are trying to access an older Pivot table data model. Must of the times it will say something like: "This workbook has a PowerPivot data model created using a previous version of the PowerPivot Add-in. You'll need to upgrade this data madel with PowerPivot for Excel 2013.". An example is below here.

Pivot table in Excel 2013 - www.oostdam.info - picture 1





Okay, lets start doing that,.. Close your current sheets, and open Excel 2013 with a blanc, empty sheet. Go to the green tab "File" and click on "Options". Now various Excel options will appear. Look for the "Add-Ins" somewhere left below. At the bottom there is a "Manage" option. Select "COM Add-Ins" from the drop-down list, and click "Go". The second option from above is the "Microsoft Office PowerPivot for Excel 2013" Add-in. Select the checkbox in front of that line. Close this dialog box and re-open your oldstyle PowerPivot sheets from your "Recent Worksheets" or browse towards it, if it is located on a different location. Now another message will pop-up, as the PowerPivot add-in is enabled.

Pivot table in Excel 2013 - www.oostdam.info - picture 2

 

 

It says: "This workbook contains a Data Model that was created with a previous version of PowerPivot. Opening this model will upgrade it to a newer version. The upgrade might take some time. If you upgrade, you can no longer open this workbook with the previous version of PowerPivot. To continue using the previous version, click Cancel. Do you want to upgrade this workbook?"



Yes, we want that, but all you administrators out there, watch out!! Upgrading a workbook that is located on a share or Sharepoint (2013) location, can cause you serious troubles if a single person is upgrading the dashboards sheets it will make them inaccessible for other users that are still using Office 2007 or Office 2010. Be carefull about this, and block by GPO the modifying of your sheets and/or the enabling of the PowerPivot add-ins in Excel 2013.


After clicking on the OK button, The conversion of your (dashboards)sheets will start.   

Pivot table in Excel 2013 - www.oostdam.info - picture 3
The message, as shown in the picture on the left, will be as follows. "The workbook is currently being upgraded. Upgrading the data model might take some time. You can cancel the upgrade, this will revert the data model to the state before upgrading. Applying changes to the PowerPivot workbook."

During the convert of the data model, a havy flashing of reading and writing is taking place, and that is shown in the action part, in the green space below, a little right of of the middle. Do not interrupt this unless you have come so far by accident. 


I did some conversions earlier, and a 20MB Excel sheet can easily take more than 5 minutes to convert, depending on the set of references and lookups that where builed-in to it. Just let it run. I have seen errors on this action with colleagues of mine, as they where only having 4GB of memory on there laptops. Mine has 8 GB, as I'm a trainer and use my laptops often for multiple Hyper-V machines :-) .. and I have never experienced memory issues on converting PowerPivot sheets. And believe me,.. I've done some heavy ones,..

After the convert, another message will appear. 

Pivot table in Excel 2013 - www.oostdam.info - picture 4


It will be saying: "The workbook upgrade has finished successfully but Excel is still in compatibility mode. To exit Excel compatibility mode, you need to save, close, and reopen the workbook. Do you want PowerPivot to save, close and reopen the workbook now?" Stupid question,.. Yes, we want that, so we click that button. Administrators,.. pay attention,. see me warning above...


Follow these instructions,.. Save (as) , ... Close,... and reopen the workbook and you will immediately see the difference on opening your graphics and/or dashboards. The build-in functionality for graphics and animations in the offcie 2013 suite makes that happening instantly. See another article on this site if you want to disable these animations. 


Tips:

1.) Created seperate dashboards for your users, if you are only testing or during a migration towards the Office 2013 suite for your end-users. Double check there modifying rights and backups before implementing and/or allowing the use of these sheets. Once converted, your done, and licenses can be very expansive,... :-) 

2.) The PowerPivot Add-in in Office 2013 contains also some great new functionality. Please check them out, after you are a bit more familiar with Excel 2013. Try and discover, you probably will like it. I will try to create another article on that topic in a couple of weeks.



I hope this has been informative for you, and have fun, and productive working, with Pivot tables & Excel 2013 !





 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Ben OostdamBen Oostdam has been working with Windows systems since 1993. Worked for several companies as a system administrator, and is currently a Senior Support Engineer and a (Freelance) Trainer, for a large company in the Netherlands, specialized in Mail and and System Center Solutions.

Disclaimer: The information contained in this website/article is for general information purposes only. The information is provided as is, by Ben Oostdam and others, and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk. In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website. Through this website you are sometimes able to link to other websites which are not under my control. I have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them. Every effort is made to keep the website up and running smoothly. However, I take no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control. All entries in these articles, are my individual opinion, or from co-writers, and they don't necessary reflect the opinion of my employer.


Tuesday the 16th, July 2019. All rights reserved.. // Oostdam WebDesign