Excel 2010 – Now With More Bits!

Thanks to Chad Rothschiller for helping me put this post together.

We’ve spent the last few weeks talking about improvements to the various visualization features in Excel.  For the next few posts, I am going to cover some “under the hood” type improvements, things like performance, internal limits, and the like.  For this post, I will focus on the work we did to make a 64-bit version of Excel.

64-bit Excel, simply put, is for building really really (really) big workbooks. 64-bit Excel is for those who want to work with large data sets and get past the 32-bit Windows address space constraint.

Why a 64-bit Excel?

In short: enormous capacity; humungous workbooks. Now that 64-bit machines and operating systems are common, we wanted to provide a version of Excel that runs natively in that environment and takes advantage of 64-bit strengths. One of the more obvious benefits is the lifting of the addressable memory constraint we were dealing with in 32-bit Windows. Applications built with the 64-bit version simply can use more physical memory than ever before.

This is good news for those of you who need to work with really large data sets, for example.

However, there was a lot of work for us to do to ensure that Excel operations scale efficiently with the size of the data, use lots of memory efficiently, and not run into internal limits. We’ve made specific investments in our 64-bit architecture to optimize our memory consumption while keeping the cell table (and related operations) as fast as possible.

We also spent significant time investigating and optimizing our algorithms for common “Large Data” scenarios like copy/paste and sorting & filtering (more on that in an upcoming post about Excel 2010 performance).

What to Expect

Generally, you can expect to create bigger workbooks, whether that’s due to tons of data in the grid, tons of charts in your workbook, several really huge PivotCaches supporting your PivotTables, and so on. Most features take some amount of memory to use them, so you can essentially have more features and create bigger workbooks.

This may seem obvious, but worth stating: because we’re enabling Excel to scale beyond 32-bit windows memory limits, if you have a really beefy machine, you should be able to do more without bringing your computer to a crawl. Your code / solutions will also run out of memory less, being able to go farther in processing your data.

In many of our test cases the 64-bit Excel working set is very similar to 32-bit Excel working set (64-bit was under 1.2 times 32-bit). Intensive use of the cell table shows around a 1.5 times increase in 64-bit. If you have a case where 64-bit Excel working set exceeds 2 times that of 32-bit Excel, let us know so we can look into it.

I’ve heard some people comment that 64-bit Excel means that it’ll just run faster than 32-bit Excel. Well, that’s not true as a general rule. We have some tests that run a bit faster on 64-bit Excel, and other tests that run a bit slower on 64-bit Excel. Again, if you find an operation that you think should be faster in 64-bit Excel, let us know and we can look into it.

One test I decided to look into: how much RAM does it take to fill up my columns, A-Z, with data, e.g. using =RAND() ? I found that it takes a little more than 2GB, and that scales pretty well as I add additional columns of 24 (e.g. filling A-AZ takes about 4GB, A-BZ takes about 6GB), though there is some overhead of just running Excel that also needs to be added on top of that.

File and Solutions Compatibility

File Compatibility

First of all, workbooks are generally interchangeable between 32-bit and 64-bit. There’s no special flag in the file marking it as a 64-bit workbook. Your average, every day, smaller-sized workbooks will be fine in both environments. However, with 64-bit Excel you will be able to create workbooks that are too big for 32-bit Excel to open (you’ll hit “out of memory” alerts before getting the file open completely). Note that even prior to having a 64-bit version of Excel, I can create a workbook using 32-bit Excel on a beefy machine (say 4GB RAM) that someone else might have serious performance issues opening using the same 32-bit Excel on a less-beefy machine (say 1GB RAM).

Running Solutions on 64-Bit Excel

You might need to review your VBA code and make updates in order to get it working with 64-bit Excel. You may also need to have 64-bit versions of any ActiveX controls, COM Add-ins, or XLLs if these are used in your solutions. I’ll have more details in a future post about programmability.