Microsoft Excel

I appreciate humungous workbooks as a programming challenge for the Excel developers, but in the wild very large workbooks are always problematic and more often than not nightmares.

In no small way Microsoft is to blame for this by having Access in some versions of Office but not others. Databases are the appropriate places for really large datasets, even if most of the values are calculated. However, people are cheap, so many (most?) are unwilling to pay for Access, so they MISUSE Excel as a database and Excel workbooks as data stores.

On balance, if the main raison d'etre for 64-bit Excel is larger capacity, you're not doing sensible spreadsheet users any favors.

Best use that I can think of for 64-bit Excel is creating large Gemini models. This alone is probably worth the price of admission. Having to update macros and add-ins will be a pain for many.

Harlan is EXACTLY right. As a computer science guy I'm jazzed about the work to make Excel hum in 64 bit mode -- as someone who works with Fortune 50 companies that ALREADY abuse Excel this is HORRIBLE news.

Excel 2003 was more than adequate in terms of worksheet space. If you need more that 65K rows you're doing it wrong! Use a database!

As someone who has been tracking Excel Memory Limits since Excel 5 (16 Megabytes!) I welcome this change.

Sure, there will be misguided people using Excel as a database, just as there have always been in the past.

And often a great solution is an Excel front-end to a Jet/Sql DB (don't need Access for that).

But that's no reason to ignore genuine customer needs for huge workbooks full of calculations that are difficult/slow to do in a DB, or trying to do pivots on large DB recordsets (its not difficult to hit the current limits with a Pivot), or getting rid of those pesky links to external workbooks, or ...

I look forward to trying out 64-bit Excel and 64-bit VBA (pity about 32-bit VB6 DLLs)

Great change! As they boost their memory, many consumers highly appreciate the innovation.

As someone who has had to analyze large amounts of data, I welcome this change.  I understand the concern people have with huge spreadmarts, but the amount of data I'm being asked to do some "quick" analysis on has only increased over the years.  Now days it's common to exceed the old 64K limit.  I think that's a good thing since I can focus more on my work and less time on moving my data to Access or summarizing over the details just to get it to fit in the old 64K row limit in Excel.  With that being said, I love my databases too, and realize that any production data that will be used on-going by multiple users should not be sitting in a spreadsheet.

I use very large Excel spreadsheets (upwards of 250 MB), and such large spreadsheets are stable in Excel 2003, and very slow but still stable in Excel 2007. Excel 2010 gives me great speed, but a simple copy and paste operation invariably causes the spreadsheet to crash (however, copy and paste macros seem to work). Also, long macros tend to stop by themselves in the middle. The only way I found of remedying these problems is by significantly reducing the number of horizontal columns and breaking the long macros down into shorter macros. If you have any insight into whether these problems can be fixed for the official release, please let me know! My e-mail is sue.khim@gmail.com.

I really wonder where MS is going with Excel.  Huge spreadsheets... users don't know what they are doing with spreadsheets now!  "a great solution is an Excel front-end to a Jet/Sql DB"... Since when is Excel a great front-end tool?  Maybe with a pile of customization to make it bullet proof from prying users.

"250mb" spreadsheets!! Are people crazy. I can just imagine the fun when some decides to email that one. Just another example of using the wrong tool for the wrong job.  Who needs 250mb of 'in-memory' data? Use the right tool.  Do these people use an open end wrench set to build a car when a socket set is available?

Great . 100 million rows in Excel . It's  just amazing. With this , peoples'  analytical capabilities will increase drastically . And it is for these analytical masteries and mysteries that all reporting tools count compatibility with Excel  as a selling point .

If  Excel 2010 has any good layouting capability to render any data in data source in any database , csv files  and xml files in a read-only manner and print it it on the fly (with adequate runtime events for detail and summary lines where some .net code can be wrritten ), it will do magic for all.

Being the univeral load engine in BI  cycles since Excel 2000 , it is already a 'universal BI last mile'.  If there are good layoutting capabilities , it will enhance it's  status from 'universal BI last mile (universal load engine )' to 'Universal BI ETL engine '.

But that's only a mystery which God and MS developers only know .And we too will know it in future.

It is also a fact that lack layoutting capabilities in Excel  is the reason why reporting tools sell like hot cake .

I thought that one of the reasons for high performance clusters and lack of data mining *in Excel* was because Excel was 32 bit thus could not do high computing, etc. I was also under the impression that with 64 bit Excel (and 64 bit OS) data mining and large databases will be possible to be run in Excel without the need to hook up with other Microsoft solutions e.g. SQL. But all I am seeing now is that you are trying to sell us a stack (Analysis tool and now Gemini all need SQL to run) instead of making Excel more useful. While I understand and appreciate the motives, I am disappointed with this.

There was nothing compelling with Excel 2007 and there is nothing compelling with Excel 2010. And what about all the work with compute shaders and other parallel work Microsoft is doing with Windows 7. Shouldn’t this make Excel more powerful thus reducing the need to have to link up with SQL, etc?

I hope Apple puts more work in iWork (mainly Numbers) by copying all the best that is left in Excel in their next release as there is very little left that is holding me from jumping ship.

Great post, looking forward to hearing more about excel 2010 and 64-bit in particular. Personally I welcome any expansion in the amount of data that excel can use, I accept the argument above about databases but lets be honest databases are for storing data, very few if any have the same ability as excel when it comes to processing of data and changing it in to something that directors and managers can understand easily and put to use, excel can and it's easy, I'm not saying you should use them instead of a database but if you are trying to extract and use just the relevant data in a database to do some analysis this can easily exceed 65K lines.

Anyway really looking forward to hearing more "under the hood" improvements as to me that's as interesting as the surface improvements.

Something directors and managers can understand involves no more than 1,000 data values possibly arising from counts, sums, averages, minimums and maximums of large amounts of underlying data. Databases most definitely can & do calculate simple summary statistics like these just as quickly and maybe more reliably than Excel (more reliably because database fields, unlike Excel ranges, are strongly typed, so text that merely appears numeric can't be stored in numeric fields in databases).

Anything nontrivial, such as ARIMA time series analysis or econometric forecasting, is more reliably and more quickly done using dedicated statistical analysis software.

Excel is strongest at high level summarization and decision support. That type of model may require many worksheets performing different analyses with source data and other intermediate calculations, but millions or rows and thousands of columns are NEVER necessary.

It's only people who misuse spreadsheets as other types of software (most often as database or stats analysis) who may believe they need worksheet grids with billions of cells.

Too often Excel is the only tool remotely capable of the tasks business users need to perform, but that's not a good reason to make it an ever larger hammer.

What is the use of a big ass grid (billions of cells) if excel is slow at calculating and/or sorting the data ? And two, what is the use if you cannot do data mining in Excel without the need for SQL? For everyday reporting and analysis the current grid is already an overkill. What we lack are tools to use these billion cells to extract useful information from data. The tools are there but they need other Microsoft systems to work.

Need of managers for large row numbers

1. I agree with that managers and directors really need / understand hardly 1000 rows. In that sense 65K  Excel  2003  also was sufficient because it too allowed  analysis of 2 million rows which reduced to less than 8000 rows in pivot .

2. But if u c a bigger picture, there is an allegation against Excel that it’s not scaleable (by competitors of course for selling their tools). And based on that many companies provide no integration or inferior integrations to Excel to their current solutions. And then eventually excel turns out to be just ‘last mile ‘with last (and least also) value for itself in overall BI analysis schema. And if raising these limits wards off this allegation of ‘no/little scalability’ and hordes of companies provide state of art integrations with their solutions , its good for users and good for Microsoft also. Also it gives MS a powerful option in pricing MS office.

3. It is also a fact users competitive scaleable solutions hardly use their scalable functionality bcos 95% of the time operational users need only latest / relevant / exceptional data which is hardly few 1000 rows.

4. So I agree firmly that 100 million rows must in no way degrade performance & functionality. Also 100 million rows may command different tech and commercial configurations.

Bundling various BI components in different solutions

Further with respect to bundling various BI components in different solutions (like analysis services and reporting service in SQL server, Excel services in sharepoint , development in .Net platform etc ) , I feel this is against Microsoft interests only because it was WYSIWYG kind of easy to use software (like windows and MS Office) for SME laymen users (which are very large in number ) which brought MS  to this leadership status. If they expect people wanting to shop reporting software buys all these software it’s just not possible even for enterprise users (not to talk of SME laymen users). There is no harm in keeping such components in all such places. But there is dire need for a inter-operable reporting / layoutting package in MS Office (which differentiates itself from other freeware / commercial Offices / reporting packages). One may term / brand this as Designer / Layout designer / Report designer. Such package must come with lots of easy to understand, use and deploy (with real life examples of say invoices, packing slips, delivery challans etc with VBA kind code at certain events). This is dire necessity of MS if they are to sustain the demand pull for its product against other OS/Office/databases/ reporting tools/ERPs. In that larger perspective this is kind of a cherish / perish decision which MS  must/ should have made . Even aging / dying VFP of MS has good report designer comparable to stalwarts. Superior integration with MS Excel / Outlook / sharepoint / SQL server / AD , compatibility with any browsers / databases / file formats, automation / schedulability must be it’s key ingredients. It may be a special option in MS Office pricing. It’s server deployment may either be stopped or be licensed appropriately just like Office.

Positioning Excel as Universal ETL engine

If Excel 2010 is portrayed and visualized as Universal BI last mile (I have gathered such impressions), this is nothing new. It’s already the BI last mile since Excel 2000 considering many reporting tool outputs ended in export Excel. And this is not a major consideration for people planning to migrate to other Office or for people to upgrade from lower excel version or for people to migrate from other office / reporting tool to Excel 2010.

One must remember that in long distance travel last mile is least important and is of least value.

Though limited ETL capabilities (Extract as data import/file import/web query , Transform in pivot summary and Load in sorting,formatting,charting ,grouping etc) in raw form already existed in Excel even earlier, it has never been visualized and used that way . So  visualizing Excel 2010 with full blown ETL capability (with a report designer) along with this 100 million capability may be a strategic opportunity for MS to be aggressive (trying to enhance/upgrade it’s status)  rather than defensive (trying to retain last mile king position) .

Report designer is ‘an extra mile’ which if MS walks (and all leaders have to walk such an extra mile), it has the paradigm changing implications. Report designer of basic capability doing mundane reporting also will do bcos few customers of competitive tools hardly use anything more than that (just like people  not using office to its max potential)

Web or not web

True all applications go web and there is nothing wrong incorporating web capabilities in the desktop applications. However that must not be done at the cost of security , ease of use ,flexibility and speed which are the forte of desktop applications.

People are driven by passions . All such developments (in direction of office/excel enhancements) can / will fire up excel users (even non-excel users) euphoria which in the overall analysis will be only beneficial to MS. And it is only such fired up laymen users only who will bring large sales for MS in large numbers (which happened earlier Windows 95 / office 97 and the stimulus impact of which is still there in the MS goodwill).

Post sale services like training /implementation / support

Further I have seen MS partners interested only in selling and not post-sale hand holding and optimizations. This is serious . Even today most people use hardly 30-40% of Excel.  Not much of the partners have invested in training and optimizations of MS product. This must be taken care off.

Data mining without need for SQL

In excel u can mine the data even without SQL . Don’t understand what T. Kwetane means.

I'm going to need to update my Excel add-in products to work with 64-bit Excel (several calls to standard DLLs). When will I be able to get a beta to work with? I signed up for the 2010 beta but haven't heard anything.

Greg

I use large excel tables extensively. Even at 20+ megs on a 64 bit machine with 4 gigs of memory excel chokes. I look forward to working with a 64 bit version.  

Access may be the 'right' tool but it ain't the fastest or most flexible tool for doing ad hoc reports and analysis -- just like vba is better than c# for most quick and dirty apps.

For example, exporting the pl data from quickbooks can be 100's of thousands of rows. Creating pivot tables  with that data can be tedious because of excel's speed.

Wow this actually quite amazing. Comments about (relational) databases non-withstanding, this is potentially enhancing the current platform that is Excel. Maybe one day there will be an Excel Server to load and access data in ways not possible with SQL-type databases. Google, for instance, does not use relational databases in its system.