1.1 Million Rows - A Discussion About Excel 12

[MrExcel]

There have been four instances where Microsoft has shown Office 12 to the public. Some high-level customers went out to Redmond in August. They showed it at the Professional Developers Conference in September. They showed it at the Publishers Summit in late September, and then again at the MVP Summit in late September. To get in, you had to sign an NDA saying that you wouldn't discuss what you saw. However, some facts about Office 12 have been made public on various Microsoft websites, so I feel pretty safe in talking about these items. (After I wrote this, I went back and noted the public source where someone from Microsoft talked about the feature to make sure I am not treading anywhere that I shouldn't).

1) This is the most substantial new release of Excel since '95 or '97. (Source: my opinion).

2) The grid is expanding to 2^20 rows and 10,000+ columns. The final column is column XFD. The final row is around 1.1 million. (Source: Dave Gainer Weblog)

3) Charting has been completely rewritten. There are not new chart types, but the look and feel of the charts is light-years ahead of the current charts. (Source: Julie Larsen-Green Video on Channel 9)

4) You can now natively create PDF files from all Office applications. (Source: Steve Sinofsky released us from NDA for this one fact and said we could tell about this)

5) Pivot Tables and conditional formatting have been made easier. Conditional Formatting is incredibly powerful now - you can easily create visual views of your data. (Source: Julie Larsen-Green Video). I *love* the Data Bar view as shown here in Dave Gainer's Blog

6) Keyboard-centric people will love that every single option available in the program will now be keyboard accessible. Not half, not most , but every single option. I love the keyboard, I love memorizing keyboard shortcuts for the common things (I even think, Alt-EIJ when I need to edit-fill-justify. It is hard for me not to say, "just Alt-EIJ that range"). And yes - there is a classic mode for people who know the old shortcuts. (Source: Jensen Harris blog)

7) Mouse-centric people will love that a new floating toolbar appears with the stuff usually in the right-click menu. It is the same sort of semi-transparent thing that Outlook 2003 shows when a new e-mail arrives. If you move the mouse towards the toolbar, it becomes solid, otherwise it fades away. I can see that this will be a huge timesaver - all of the good options just a few pixels from the current cell. (Julie Larsen Green Video)

8) They have completely re-thought menus and toolbars. Word 1.0 offered 20 commands. Excel 2003 has 350 commands. There is no way to effectively layer 350 commands on 9 menu options - people can not find what they are looking for. The new user interface is called "The Ribbon". It is context-sensitive like the current right-click menus. Instead of tiny toolbar icons, it has big buttons and words. The most powerful things are very evident in The Ribbon. For a lot of people blogging about the release, they all seem to have heartburn that there is not a "classic" view that will bring back the old menu system. I initially thought this was insane. However, after seeing it first-hand for a couple of days, I really think that this is a vast improvement. I think this is a small hurdle, it will annoy me for 2 days, but once I get past it, then I have the full power of 1.1 million rows and more power to analyze data with Excel. (Source: Jensen Harris blog)

9) In the MVP Excel breakout session, they showed some other features that have not been shown elsewhere. There are some gems in here, just in case #2, #3, #4, and #5 weren't enough. As soon as Dave Gainer talks about them, I will bring them up.

My take... a lot of people are still using Excel 97 or Excel 2000 and this is fine because Microsoft had not added much new stuff since Excel 97. It made it great as an author, because a screen shot from Excel 2003 looked almost like the screen shot from Excel 97 - you could write about seven years of Office releases with one book.

However, I think that this version has so much good stuff - it will be very very compelling for people to upgrade. I was talking with a casual Excel user last night, and just that day, he had been burned by the 65,536 row limit. Other people want more than 3 conditional formatting. It will be easier for regular people to find the powerful features that are currently buried.

The "gotcha" that I can see - upgrading needs to be an all-or-nothing thing for a company. If you have Excel 12 and have 350,000 rows of data and some of your co-workers are still on Excel 2000 - you won't be able to share that data.

Anyway - I've started this post for us to discuss the changes. If you hear of a new feature, feel free to post about it here.

Bill

 

[Tazguy37]

I have current files that are over 80 megs and my machine struggles at times with that.

Have you tried shinking that file size at all?

 

[mivar]

Excel 12

1 million rows sound like insanity....of course if excel performs with calculations on a bit more than 65000 it would be good but with that kind of record count I would consider using OLAP technology

But then again I'm a BI consulent .... doh!!

But what have come to my attention with excel is the enhanced collaboration with the server section of microsoft. Search here

http://microsoft.sitestream.com/PDC05/

for off322 and off323 to see videos regarding this matter.

So I'm looking forward to see what this brings along - up til now excel had some missing features regarding the support of the olap server tools - perhaps microsoft now will delilver the full package - the back bone and the front end.

 

[Joe Was]

I have been getting the MSDN News Letter and been doing the links about version 12. Like Bill pointed to the ribbon and no toolbar was the first thing that made me say "What." Most of what I have seen, only looks great. I can't wait!

Two things did scare me though:

1st: MS at first indicated that they were not supporting VB in the future and that one model of version 12 did not include VBA. With the uproar over that MS indicated that VB is here to stay for some time?

2nd: New things are being referred to as if we know about them or their use, that seem to be key to the new version. Like: Really scary Security additions, that will make coders jump through new hoops. And, data display tools, never seen before. So, I well wait until my copy to cross these bridges.

One of the things that I hoped would be address, that I have not seen details on, is the greater intergration of object property tables with VBA. Or, greater VBA reference to the Windows environment [Wscript & Shell help in VBA help] and application development tools, like an application packager?

 

[madwab]

upgrading needs to be an all-or-nothing thing for a company

The "gotcha" that I can see - upgrading needs to be an all-or-nothing thing for a company. If you have Excel 12 and have 350,000 rows of data and some of your co-workers are still on Excel 2000 - you won't be able to share that data.

Actually I disagree. There are of course many advantages in having everyone on the same version, but it's far from necessary. As a data analyst I regularly handle very large amounts of data, but the finished reports my customers see, usually in Excel, have never yet come close to current Excel limitations.

In fact I have a copy of Quattro Pro installed for the occasions that I need to import and filter over 65k rows. Once that's done I finish the job in Excel.

1 million rows sound like insanity....of course if excel performs with calculations on a bit more than 65000 it would be good but with that kind of record count I would consider using OLAP technology

Same answer, really - I have to export large reports to one of a limited range of formats, currently .rtf, .doc, .pdf or .xls. I can't do much with .doc or .pdf; .xls filtered through Quattro is a simple solution, tho' I'm now looking to VB.Net, ADO/ODBC, SQL etc.

I agree that currently a 1m sheet would be a questionable design. However when I first used Excel, the idea of serious video editing on a home PC was laughable, yet now we take it for granted. Technology is still advancing rapidly, and the idea of using Excel for some kinds of large semi-database type work no longer seems that daft, it being so much more easy to learn than Access or SQL.

 

[keepitfrozen]

While i'm hardly as technical as you guys are just the look of the menus excites me but what about backward compatability with previous version of excel. Our office uses 2000 still, so what would happen if i had a sheet that went past the 65536 mark and i sent it to a co-worker, would he/she be unable to view the file?

Mark

 

[madwab]

They would not

...at least, they would certainly not be able to see all of the data. I believe v12 will intruce a new save format, in which case if you save in the default format your colleasgues won't be able to open the file. Even if they can, their version of Excel can't and won't import data beyond 65k rows. If you save in an earlier format the same applies, but I would hope that Excel will simply refuse to save it anyway, or at least warn you that the whole file won't open in earlier versions.

But you don't really have a requirement to send colleagues spreadsheets with that much data very often, do you? The more likely scenario is that an anallyst will need access to the full dataset, but will distribute summaries to colleagues; practicalities such as the size of emails, performance and macro security issues mean that it's already rarely appropriate to distribute the full data set, and those issues will get worse with v12. Few people will thank you for a 20MB email that takes 5 minutes to open and many seconds to respond to every change...

Even if you do need to send lots of data, v12 still helps: you can use it to move that data into multiple tabs, each with the maximum number of rows or less, then save in an earlier version - not ideal, but workable. In earlier versions of Excel you couldn't even do that, because the source data was truncated before it got into Excel.

 

[SydneyGeek]

Dave Gainer's log talks about a setup called Excel Server -- basically, using Sharepoint to make workbooks available over a browser. I'm not sure how full the feature set will be. I guess the other problem is Sharepoint. MS is pushing it pretty hard, but do you know any / many businesses that use it at all?

Denis

 

[PrinceNeptune]

The mere mention of the phrase "excel server" makes my stomach turn. The problem doesn't lie in the server itself, but how people will use them (mainly incorrectly).

I went to a company to do some analyst work who claimed to have an access database all created. The day I got there, it was one giant spreadsheet of information with no primary key. There were duplicate values in just about every column. So my job became sorting through 30,000 entries to make a useable database (meaning that from day 1 they have entered this stuff continously not knowing what they were doing).

I can only imagine how people will use a MS spreadsheet that has the capability of 1 million rows. Though I think it is insanely impractical and will take years before that number proves to be useful.

Appreciate all the updates you guys have given, this is a very interesting project I like to follow.

 

[just_jon]

The puching of thin client setups may well make the point that everyone has to upgrade at once a moot point.

My office is testing the thing out, and I can see he day in the very near future where the only people w/ big boxes will be dinasaurs like moi ...

 

[doco]

I suppose for a lot of large organizations (like ours), when XL12 comes out will be of little matter. Most of the organization (State Government) just upgraded to 2003 from 97! So my guess is it will be late 2015 before the next upgrade