PowerPivot for Excel - "Memory error"


You may receive the following error when attempting a data refresh using PowerPivot for Excel:

 

ppv-memory

 

Memory error: Allocation failure : Not enough storage is available to process this command. . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

The current operation was cancelled because another operation in the transaction failed.

 

This error may occur when using the 32-bit version of Excel, which enables you to work with up to 2GB of data in memory when using PowerPivot for Excel. The PowerPivot add-in runs as an extension of Excel and the PowerPivot Vertipaq in-memory engine loads within the same process space. As 32-bit Excel is limited to a 2GB virtual address space, once you start adding up all of the uses of that virtual address space, including Excel, all of the add-ins, and the in-memory database itself, the largest PowerPivot workbook that you can create on a 32-bit machine may actually be much lower than 2GB.

 

Installing the 64-bit versions of Excel 2010 and PowerPivot for Excel 2010 would increase the 2GB limit to 4GB. Furthermore, if you use 64-bit Excel 2013, there is no imposed file size limit, so you would be able to fully utilize the amount of memory you have installed when using 64-bit PowerPivot for Excel 2013. Alternatively, you could try reducing the imported datasets and tables by applying filters in the Table Import wizard to avoid hitting the limit when using 32-bit Excel and PowerPivot.


Comments (4)

  1. Abba says:

    This is very useful, am using windows 10 with Excel 2016 and am getting the same error. What should I do to avoid hitting the memory limit?

    1. Abba, you should use the 64-bit version of Office 2016 to help avoid getting this error.

  2. Teresa says:

    I currently have office 2016, how do I know if I have 32 or 64 and how would I get the 64 bit?

    1. Hi Teresa. It depends highly on the version of Office you have and how it is deployed to your machine. "Office 2016" is a pretty generic term these days as it covers multiple versions of the product. The best way to tell is by looking at the "Account" or "About" sections inside of your "File" menu. That will show you almost everything you need to know about your version, build number and bitness.

      As far as how to get 64bit, again it is a difficult question to answer. If you have a 32bit Windows machine, you will not be able to use 64-bit Office. Also, if your Office product is deployed by your business, you may need to ask your IT department if they make the 64 bit version available to you. If you have Office 365 as a home user, you can download the 64-bit version from your account. There is a section on the O365 FAQ that tells talks about it: https://products.office.com/en-us/microsoft-office-for-home-and-school-faq?legRedir=true&CorrelationId=b48b0a2e-ca74-4a7d-9ef7-6699adb55af4. There are many more ways and possibilities to get ahold of 64 bit, but like I said, it will really depend on your scenario and it would be far to much to cover within the scope of the blog. Feel free to call into support or talk with your IT department for assistance. Either will get you going in the right direction.

Skip to main content