It's Official

[Bob Phillips]

http://blogs.msdn.com/excel/default.aspx

[malik641]

The Excel 12 grid will be 1,048,576 rows by 16,384 columns.
GEEZ...

I wonder if they'll make User Defined Functions calculate more quickly...:think:

[MOS MASTER]

Thanx for sharing Bob! :yes

[TonyJollans]

Wow! That is a much wanted feature in Excel and I can imagine that alone driving sales.

[Bob Phillips]

GEEZ...

I wonder if they'll make User Defined Functions calculate more quickly...:think:

I have to wonder why anyone who want more than 65536 rows isn't using a database. But more than 256 columns is useful.

.

[Ken Puls]

for those of you that are curious, columns now end at XFD instead of IV

LOL! I read that as XLD the first time! :rotlaugh:

[johnske]

Specifically, the Excel 12 grid will be 1,048,576 rows by 16,384 columnsWow, that's pretty amazing :)

[TonyJollans]

I have to wonder why anyone who want more than 65536 rows isn't using a database. But more than 256 columns is useful.

.Excel is cheaper than a database - effectively free as most businesses have it anyway.

[johnske]

Excel is cheaper than a database - effectively free as most businesses have it anyway.Exactly my sentiments :) (Most everyone actually)

[MOS MASTER]

Excel is cheaper than a database - effectively free as most businesses have it anyway.

And setting up something simple in Excel as database is still the most used method cause people shy away from using Access.

They generaly think it's to difficult to understand (And it can be) but ones they get the hang of the handy wizards a simple database is easily created.

But like I said the majority of people will continue to store data in Excel rather then Access...(because of the flat table principal...easy to understand) :yes

PS..most buisnesses have Office and that includes Access of course. :*)

[sheeeng]

Thanks! I love that features.

[MWE]

I have to wonder why anyone who want more than 65536 rows isn't using a database. But more than 256 columns is useful.

.
it would be interesting to understand how many rows and columns are actually used in spreadsheets and who really needs 50x what is available today. I would guess that I have developed several thousand spreadsheets in the last 25 years and doubt that any individual sheet had more than 500 rows or 50 columns.

[malik641]

I have to wonder why anyone who want more than 65536 rows isn't using a database. But more than 256 columns is useful.I don't know if it's the same for other companies, but the one I work at generally WOULD prefer using a database as a replacement for most of their spreadsheets. The problem lies in buying more liscences because not even HALF of the computers at my company have Access. Not to mention (like earlier stated) that people stray away from Access.


it would be interesting to understand how many rows and columns are actually used in spreadsheets and who really needs 50x what is available today. I would guess that I have developed several thousand spreadsheets in the last 25 years and doubt that any individual sheet had more than 500 rows or 50 columns.Not to put down your point (because I DO agree with you), but I've only been creating spreadsheets for 4 months and have had to deal with one spreadsheet that was over 1500 rows long and from A:IV columns wide (yes, ALL the way till the END). So I guess some people do need the room....but over 1 million rows??? That may be an overkill. Though the column space is a nice new feature, especially for dates and weekly periods that need to be recorded on a yearly basis.

[MWE]

...
Not to put down your point (because I DO agree with you), but I've only been creating spreadsheets for 4 months and have had to deal with one spreadsheet that was over 1500 rows long and from A:IV columns wide (yes, ALL the way till the END). So I guess some people do need the room...
Is this a spreadsheet you inherited or one you built? I do not deny the need for lots of columns, but I wonder if worksheets with hundreds of columns might be better structured with more sheets of fewer columns each. Spreadsheets seem very easy to build. But I suggest that they are easy to expand without a great deal of design.

[Jacob Hilderbrand]

It will be nice to have 1 column for each day of the year, I know I will be using that. As for rows, I have gotten information exported to Excel that could use additional rows. Just dealing with alarm/access systems it is impossible to export long date ranges because it would exceed 65536 rows.

[malik641]

Is this a spreadsheet you inherited or one you built? I do not deny the need for lots of columns, but I wonder if worksheets with hundreds of columns might be better structured with more sheets of fewer columns each. Spreadsheets seem very easy to build. But I suggest that they are easy to expand without a great deal of design.This was inherited.

IMO the best method for dealing with hundreds of columns (an example of each column being a day of the year) would be to sort them by year, then month, then by week, etc etc...I'm sure I would be using hundreds of columns for the work I do. I'm working on something right now that only has room for one year. And I have dates set by week and days for each day of the week and so on. The sheet already wraps around once, which is a pain to deal with when it comes to macros...And I would LOVE to have hundreds of columns to project over a couple year's time. That would be SO much easier than to have to create a macro to reformat all the tables for the new year (because of dates and days aren't the same every year and yatty yatta).

BTW most sheets I deal with are inherited. And their a HUGE mess most of the time...:doh:

[BlueCactus]

Well, this is indeed good news. Might be able to avoid my half-planned migration to Igor Pro now. When each data point you collect is a full spectrum in a column at 10 ms intervals, it doesn't take long to run out of columns. And when you increase the resolution of your spectral data, you can foresee running out of rows too.

[Paleo]

Excel is cheaper than a database - effectively free as most businesses have it anyway.
This isnt true, because you can create Access databases through WebMatrix, a free download from Microsoft at www.asp.net (http://www.asp.net).


it would be interesting to understand how many rows and columns are actually used in spreadsheets and who really needs 50x what is available today. I would guess that I have developed several thousand spreadsheets in the last 25 years and doubt that any individual sheet had more than 500 rows or 50 columns.
I just finished working on a project where we had a spreadsheet with 90 columns and 40,000 rows and the client didnt want us to use a database, so sometimes it happens to use a big spreadsheet. For financial analyses I have used many times spreadsheets that needed to read data in slices and I must admit that more than 1,000,000 rows will solve this problem for a good amount of time.

[Bob Phillips]

it would be interesting to understand how many rows and columns are actually used in spreadsheets and who really needs 50x what is available today. I would guess that I have developed several thousand spreadsheets in the last 25 years and doubt that any individual sheet had more than 500 rows or 50 columns.

Exactly mate, and when they do it is time to look at your design isn't it.

My biggies have always been temporary, where I dumped some data to do a quick analysis then thrown away.

I once tried as a test a spreadsheet with 30000 rows, and I put hundreds of formulae in it, and then watched it grind every recalc. Anyone who uses the new capability is going to need some serious computing power.

.

[Bob Phillips]

So I guess some people do need the room....but over 1 million rows??? That may be an overkill. Though the column space is a nice new feature, especially for dates and weekly periods that need to be recorded on a yearly basis.

I agree, extra columns is useful.

The one good thing about the extra rows without even using them is that now when you use ROW(INDIRECT(A1&":"&A65536)) as a way of testing for dates, you are restricted to 01/01/1900 - 05/06/2079. We can now test up to 25/11/4770. Whoopee!

.

[Bob Phillips]

BTW most sheets I deal with are inherited. And their a HUGE mess most of the time...

I think this is the nub of the problem.

Spreadsheets are easy to use, and because of that every Tom, Dick and Harry thinks he can build them. Very few people give any thought to what they need to do, what the key goals are, and design in accordance. And even if they do, someone else adds on to it, and before long you either have an unmaintainable spreadsheet, or a very inefficient one.

And don't even get me started on auditing spreadsheets.

Extending facilities like this will not improve matters as I believe that most people (I accept that there are always exceptions, although most 'exceptions' I have seen are better suited to databases) that cry out for more rows are doing so because they have created poor spreadsheets. I don't blame MS for reacting that way, but I don't necessarily think that it is a good thing. And as I said elsewhere, how are these beasts going to perform?

.

[TonyJollans]

Performance will be an interesting issue.

I also wonder how much VBA will possibly be broken, such asSet myRange = Range("A65536").End(xlUp)

[Bob Phillips]

Performance will be an interesting issue.

I also wonder how much VBA will possibly be broken, such asSet myRange = Range("A65536").End(xlUp)

Well if you had listened to me when I evangelised ...

myRange = Range("A" & Rows.Count).End(xlUp)

It becomes a real issue now.

.

[TonyJollans]

Indeed! But I don't think everyone heard you :)

There are going to be other issues though - SUMPRODUCT formulae using "A1:A65535" because they won't take a full column, for example.

[Bob Phillips]

Indeed! But I don't think everyone heard you :)

There are going to be other issues though - SUMPRODUCT formulae using "A1:A65535" because they won't take a full column, for example.

Indeed Tony, that is the real problem for me. This is what I posted in one of the NGs yesterday

... Also, will there be any way make the code bullet-proof. For instance, SUMPRODUCT doesn't work with columns so you see many solutions of the ilk =SUMPRODUCT(--(A2:A65536=.... With the new version, if you want you will be able to use =SUMPRODUCT(--(A2:A1048576..., but this gives a #NAME in earlier versions. How can we make our formulae backward compatible? ISERROR is not necessarily an option if we don't want to mask real errors.

[mdmackillop]

Of course Range("A65536").End(xlUp) will still work until you use more than 65536 rows:devil:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Cells.SpecialCells(xlCellTypeLastCell).Row() > 65536 Then
MsgBox "Time to update your macros!"
End If
End Sub

[Bob Phillips]

Of course Range("A65536").End(xlUp) will still work until you use more than 65536 rows:devil:


Yes of course it will, but that is not the point. There are 2 problems

- anyone who uses 1048576 in Excel 12 cannot use that in an earlier version

- anyone who inherits an earlier version spreadsheet and then writes data beyone 65536 may have a problem and not even know it

[XL-Dennis]

- anyone who uses 1048576 in Excel 12 cannot use that in an earlier version

- anyone who inherits an earlier version spreadsheet and then writes data beyone 65536 may have a problem and not even know it



We will propably see a situation where the new sheet size is only available in the new fileformat which in return only can be read by Excel 12 and later version. The historical development of Excel support this strategy.

[TonyJollans]

We will propably see a situation where the new sheet size is only available in the new fileformat which in return only can be read by Excel 12 and later version. The historical development of Excel support this strategy.

That is quite possible, Dennis, but from what I've seen so far the new xml format (.xlsx?) will be a default but the old format will still be available - the implication being that you can save new workbooks in the old format if you wish. Perhaps there'll be some sort of warning saying you'll lose 'features (i.e. data) not supported by the old format'. Also, there's nothing to stop someone saving old workbooks in the new format, and then there could be problems.

It will be interesting to see what else come s out about this. I'm sure lots of people are asking the same questions at the moment.

[XL-Dennis]

It will be interesting to see what else come s out about this. I'm sure lots of people are asking the same questions at the moment.


Agreed, I look forward to see if MSFT will improve VBA or single out Excel itself to be an end-users software only.

After all, the heavy promotion of VSTO, especially with C#, strongly indicate that MSFT want developers to adapt this platform.

Kind regards,
Dennis

[Bob Phillips]

After all, the heavy promotion of VSTO, especially with C#, strongly indicate that MSFT want developers to adapt this platform.

I hope not. VSTO is not an appealing product and is not gaining much ground. We could get amother VB.Net/Class VB backlash.

[Cyberdude]

The engineer in me makes me wonder what amount of memory (RAM) will be needed for a maximum sheet to execute in a reasonable manner. I can just see the old PC thrashing its virtual storage like crazy when it does a recalc. Isn't there a law somewhere that says a spreadsheet quadruples its size every 2 years (or did I make that up??)?

[XL-Dennis]

We could get amother VB.Net/Class VB backlash.


True and as we all know what MSFT's answer was...



The engineer in me makes me wonder what amount of memory (RAM) will be needed for a maximum sheet to execute in a reasonable manner.


Hardware & software tend to walk hand in hand which means that new softwares should be used together with new hardware.

BTW, I wonder how many different versions of MS Office Vista will be available. I recently read that MS Windows Vista will be available in 7 different version whereof one version will be based on a yearly subscription.

Kind regards,
Dennis

[BlueCactus]

The engineer in me makes me wonder what amount of memory (RAM) will be needed for a maximum sheet to execute in a reasonable manner. I can just see the old PC thrashing its virtual storage like crazy when it does a recalc. Isn't there a law somewhere that says a spreadsheet quadruples its size every 2 years (or did I make that up??)?
RAM won't be a problem. However, use of more than 256 columns requires the reinstallation of IE and corresponding removal of Firefox. Use of less than 257 columns results in a task bar popup every 2.5 minutes helpfully informing you that 'You have unused columns in your Microsoft Excel spreadsheet."

[malik641]

You know what I'd like to see.

It would be nice if you can disable a SINGLE worksheet's calculation, instead of disabling the entire workbook and every other workbook that's open while the calculation setting is turned to manual.

Or basically any application setting can be worksheet specific, that would be cool :cool:

[malik641]

Just read the update on that site - http://blogs.msdn.com/excel/default.aspx

These look great:


Number of unique colours allowed a single workbook
Old Limit: 56 (indexed colour)
New Limit: 4.3 billion (32-bit colour)

Number of conditional format conditions on a cell
Old Limit: 3 conditions
New Limit: Limited by available memory

Number of levels of sorting on a range or table
Old Limit: 3
New Limit: 64

The number of levels of nesting that Excel allows in formulas
Old Limit: 7
New Limt: 64

Total amount of PC memory that Excel can use
Old Limit: 1GB
New Limit: Maximum allowed by Windows

The new amount of colors feature is definitely cool (and fun).
And I definitely like the new conditional format feature.

The new nesting feature seams a little extensive (I would NOT want to look at a nested IF statement with 64 conditions...:bug: )

[brettdj]

The sinners who abuse For .. Each range loops will really be waiting for a long time for their code to execute now.

[Zack Barresse]

Well if you had listened to me when I evangelised ...

myRange = Range("A" & Rows.Count).End(xlUp)

It becomes a real issue now.

.
Oh how true! You convinced me some months ago. I tell you, I'm glad I made the switch. I have found myself in more than a few arguments that it was never needed. I can just sit back and smile now. Ah. :thumb

[brettdj]

Most of these changes are fantastic, especially the conditional formatting and styles mods. I've long wanted to apply our corporate colour scheme (constants, links to other sheets, links to other workbooks etc) dynamically with conditional formatting using XLM and defined names, but three conditions (exluding formatting tweaks & VBA) weren't enough

I'm not sure about the wisdom of enabling a 64 level nested IF though.

[Zack Barresse]

All that has got me wondering one thing, and it keeps going back to this too - what have they changed about the calculation algorithms?? I mean, everythings larger, how is their programming going to be able to handle it?

Well, for me, time will tell. :)

[BlueCactus]

All that has got me wondering one thing, and it keeps going back to this too - what have they changed about the calculation algorithms?? I mean, everythings larger, how is their programming going to be able to handle it?

Well, for me, time will tell. :)
M$? It's the user's responsibility to acquire a faster machine. :giggle

[Zack Barresse]

ROFL!!! I'm just really curious overall. :yes