65 thousand rows, the end of the Excel universe

23 October, 2007 – 4:42 pm

It has long since annoyed me and no doubt numerous others, the inability of Excel to get beyond 65,536 rows of data or more simply 2^16 …

A deft upgrade to Office 2007 sees an improvement in this limitation and am now able to have at least 1M rows of data. This is good news for us performance testers stuck between learning a dedicated statistics package like R or dialled in to on-board analytics with tools like Load Runner.

It’s nice to be able to analyse a greater sample size using standard tools in the workplace, although MSO2007 is probably not standard just yet …

Some of the old constraints still apply, so before you rush out and buy a copy consider the following:

The maximum number of data points you can use in a data series for a 2-D chart is 32,000. If you want to use more than 32,000 data points, you must create two or more series.
This makes sense from a data plot point of view, after say 1,000 points on your chart, you really are starting to run out of pixels, and I normally thin my data when presenting it anyway. This leads to another question on what is the best way to thin or aggregrate data? Definitely a post in that one … But in any case, this is no different from 2003 and offers no improvement in the charting options that I can see.

If the Data Analysis dialog box is displayed, click the tool that you want to use under Analysis Tools, and then click OK.
After some more searching of help, I found the analysis pack under the Office Button in Excel Options. I had installed the data analysis pack when upgrading, but it was not enabled by default. This can be done by selecting the somewhat ambiguous Go button.

At first glance, the statistics pack offers no more detailed analysis than that already available in Office 2003.
Verdict: thumbs down

The Ribbon
I really can’t get used to this, I must be getting old. It’s really frustrating at first to use this new toolbar, as the old style menus are gone for good (except in Outlook 2007 :)) To me it feels like Office (and Vista for that matter) are ‘dumbing’ down their user community. The keyboard shortcuts are still there, but you’ll have to rediscover their combo’s with the alt key. I’ve been using the ribbon for a couple of months now and still not a big fan, but that’s just my opinion …
Verdict: thumbs down

Overall opinion? Spreadsheets like Excel really are great at throwing together a quick analysis of data for reports etc. Presentation is up to the user, but you can certainly tweak and theme to your heart’s content. It is however a very static resource, in the sense that you can’t easily share the content with other people short of emailing spreadsheets around (Edit: there’s proabably some sharepoint service out there that achieves this)

If you want to get a bit more dynamic, consider building your own reporting system (web based) and storing the multitude of data in an appropriate resource (database) rather than having lots of static, large sized spreadsheets. I’m starting something of my own here but due to the volumes of data involved it’s really only suitable for intranet applications.

If you’re in doubt, stick with Office 2003 for the time being.