So this is just huge, especially if you work with Power Pivot models and are stuck in 32 bit Excel… Microsoft has just released a 32 bit Excel Memory Limit increase for users of Excel 2016, effective build 16.0.6868.2060 (which is the current build for the Insiders program.)
UPDATE: Effective June 7, 2016 (and build 15.0.4833.1000), there is now a patch available for Excel 2013 (both MSI and subscription versions). More info here: https://support.microsoft.com/en-us/kb/3115162
Why a 32 Bit Excel Memory Limit increase?
Users stuck on 32 bit Excel were limited to only using 2GB of RAM for their Excel/Power Pivot models, no matter how much memory was available on the PC. The answer to this in the past was to install the 64 Bit version of Excel, as that could address up to 8 TB of memory (if you had it, of course.)
There has been a hack/patch available for a while, (see below,) and I spoke to a user at the PASS BA summit who told me that without that he simply couldn’t use Power Query at all.
How big an increase is it?
Before you start thinking that you’ll now get the same memory access as with 64 bit Excel, let’s disabuse you of that notion. It’s better, but not parity. How much you get actually depends on the bitness of your operating system.
- 32 bit Windows: up to 3 GB
- 64 bit Windows: up to 4 GB
I suspect the first is an operating system limit and that the second is probably more of an internal architecture decision. The world needs to move to 64 bit, but this will help give companies (even more) time to make that move.
What about non-Power applications?
This change doesn’t just benefit Power Pivot and Power Query; it benefits anyone who has been running into memory constraints. So if you’ve been running out of memory because you’ve been pushing huge data sets via VBA/SQL, you’ll love this too.
How about Excel 2010/2013?
Yeah, no. Sorry. This is part of the benefit of being current… Microsoft is building for the current version of Office. Excel’s biggest competitor is previous versions of Excel, so by providing a fix like this to a prior versions they’d actually be giving you reasons NOT to upgrade. You’re in business, and I’m sure you understand that – as much as this sucks for you right now – you’d probably make the same call.
Having said that, if you want to install “the patch” to get your access in previous version, Rob Collie has a link to it in point 3 of this article.
The “Official Word” from Microsoft…
You can find that be reading KB3160741 for more details.