Some other numbers ...

re: Some other numbers ...

David Gainer — Wed, 19 Oct 2005 05:46:14 GMT

Peter – Thanks for your comments. We believe that increasing the limit roughly 16 fold will dramatically reduce the number of users that ever hit this limit. Secondly, in a world of more structured formatting with improved cell styles, table styles, and themes (which I will cover in later posts), we believe there will be less need to create one-off formats, so it is possible that the average number of unique formats an Excel 12 workbook will be fewer than an Excel 11 workbook. Finally, both cell and table styles have their own 64k limits separate from each other and the “unique cell styles” limit in question. The result is that cell styles and table styles take zero slots from the custom format limit.

re: Some other numbers ...

Peter Quarrell — Tue, 11 Oct 2005 10:51:48 GMT

I really am pleased that the limit of "approximately 4000" on the number of different cell formats (inadequately discussed in support.microsoft.com/default.aspx) is to be increased to 64K.

BTW you refer to this limit as being on "the total number of unique cell styles". This term is also used in the KB paper listed above, and by several of those who have posted comments about the problem in various places. The reference to "styles" actually confuses people, and takes attention away from the fact that this limit is on something (format combinations) when most people don't know of its existence. You will find, if you do a Google search, that some people think the problem can be solved by deleting unused custom number formats, but they don't make the combinations list hit its limit.

I'm pleased the limit is being increased even though this means there will be an end to a steady small income I have been making for a few years from sales of my utility QAid. Although QAid offers quite a range of useful analyses to improve the efficiency of large Excel workbooks, most of the sales go to people who have run into the "approximately 4000" limit (actually anything from 2300 to 4482 in my experience) and discovered that it makes the workbook _completely_ unmanageable. When you open the workbook, up comes the error message; whatever you do to it then, up comes the error message again. (But see my web site for advice on how to get it working, and thus repairable, again).

Since creating my website to publicise QAid some years ago, I have been contacted on average once every 14 days by someone interested in acquiring a QAid licence. QAid works by identifying and counting occurrences of each of the format combinations used, thus producing a shortlist of those which are used once only and can therefore reduce the problem by being changed to a less unique format. In the last 18 months QAid has also provided shortlists of the combinations most similar to the one you want to eliminate, thus almost automating the mending process (while leaving to the developer the necessary choice of which feature(s) of the combination to sacrifice).

So hopefully my sales of QAid will begin to dry up once Excel 12 is on the market -- indeed, I will be duty bound to add a note to my web site advising interested potential customers to switch to Excel 12 to sort out their problem. It seems sad, really; I've had emails from all over the world, with recent sales including the US Department of Treasury, a Hollywood studio, a major oil company, local governments in 3 continents, a New Zealand sawmill, and an Australian bank. Often we have corresponded at some length as new vagaries in Microsoft's offering have become apparent.

Two main questions:

(a) does the increase to 64K mean that workbooks that hit the new limit will give some warning and not become unmanageable just as their victims discover the problem? If users could try to deal with the limit without having to worry about whether their workbook can ever be made useable, they might not feel as bad about Microsoft as most of them do (there is a universal feeling among them that this aspect of Excel must have been developed by a Seattle high school student on work experience during the summer vacation, who was inadequately supervised because the managers who should have done so were on vacation themselves -- and that customer complaints have not got through often enough for anyone to consider doing the repair work that has been needed since day 1).

(b) is there any chance that Microsoft might give users some VBA access to the parts of the Excel Object Model that deal with these format combinations? There must be a hidden list of all the different combinations, with all the relevant format property values for each one, and a pointer system in one direction at least (from cell to list) if not in both directions. QAid generates such a list (and takes ages, since it has to evaluate 40 different format aspects for every single cell _and_ merged area in the workbook's full UsedRange). Give us read-only access to the list and the task becomes much less.

The Object Model's list seems not to be particularly often subjected to housekeeping (just as deleting hunks of VBA leaves unused gaps in a workbook, which can only be closed up by exporting all the forms and VBA modules, saving the workbook, and then reimporting them). When a workbook is close to the ~4000 limit (a state identified by formatting a cell with a previously unused font or colour, and watching it crash), the margin for new formats can best be increased by _both_ saving the workbook _and_ exiting Excel completely. It looks as though only closing Excel actually tidies up that aspect fully. Not very easy to prove that point, but I've given that advice frequently, and many people have found it helpful.

re: Some other numbers ...

Rob van Gelder — Sun, 09 Oct 2005 20:36:41 GMT

I wondered about the column/row limits. Why were there limits at all?

My guess is that in "unlimited mode", any series of characters followed by any series of numbers would be treated as a cell reference.

eg. vangelder1 would be a valid cell reference.

Is this the reason to limit the number of cells on a worksheet or was there some other reason? I'm interested to know.

Cheers,

Rob

re: Some other numbers ...

David Gainer — Wed, 05 Oct 2005 06:22:37 GMT

Greetings,

Calvin – We have not changed any limits around the number of sheets.

Harlan – As you have identified, the problem with characters formatted as text only appeared in cells containing between 255 and 1024 characters (text displays “####” instead of the characters).

Joseph – We have done some work in application settings to make it much clearer which settings relate to a specific workbook. Watch Jensen’s UEX blog for details, and I may write something too at a later point.

Scott – Glad you like the feature. We are doing our best!

re: Some other numbers ...

Scott — Tue, 04 Oct 2005 13:40:20 GMT

The total number of available columns in Excel

Old Limit: 256 (2^8)

New Limit: 16k (2^14)

The total number of available rows in Excel

Old Limit: 64k (2^16)

New Limit: 1M (2^20)

It's about time. Now please release the software. We needed this years ago, not years from now.

re: Some other numbers ...

Jan Karel Pieterse — Tue, 04 Oct 2005 10:48:15 GMT

Adam:

About the long defined names:

Download my name manager utility from

www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm

It allows you to edit names with mulitple areas without being bothered by the 244 character limit.

re: Some other numbers ...

Harlan Grove — Mon, 03 Oct 2005 23:40:03 GMT

Andrew...

||If anything the functionality you mention

||would seem to be a bug in the interaction

||between REPT called with a 2nd argument

||>= 256 and the Text number format.

|No, it's not. If you type 256 a's into a

|cell, and format it as text, you'll see the

|same behavior as if you used REPT and

|formatted it as text.

There's a bug somewhere. I'm willing to grant it may not be in the REPT function. However, in Excel 2002 (at least), if you open a new workbook with default number format other than Text, enter the formula

=REPT(REPT("a",255),128)

then format the cell as Text, Excel continues to display a long string of a's. Not all 32640, but much more than the width of the cell. Why does Excel Text number format choke on a formula-generated string of 256 chars but not on a formula-generated string of over 32K chars? How do you define 'bug'? I suppose you could hide behind programming terminology and call this an instance of nonorthogonality rather than a bug.

re: Some other numbers ...

Andrew — Mon, 03 Oct 2005 18:42:33 GMT

>If anything the functionality you mention would seem to be a bug in the interaction between REPT called with a 2nd argument >= 256 and the Text number format.

No, it's not. If you type 256 a's into a cell, and format it as text, you'll see the same behavior as if you used REPT and formatted it as text.

re: Some other numbers ...

Joseph — Sat, 01 Oct 2005 16:28:43 GMT

-A suggestion for a new feature

It would be nice to see Application settings to have a worksheet specific option (Example: Calculation settings). This would be nice to have so the settings won't affect any other open workbook and/or any other worksheet.

re: Some other numbers ...

Harlan Grove — Fri, 30 Sep 2005 21:38:42 GMT

Jim Rech...

>Anon:

>>>"we increased the number of characters that >can be stored and displayed in a cell >formatted as Text from 255 to 32k"

>>>Hmm? Zero improvement here,

>1. Enter =REPT("a",256) in A1

>2. Now change A1's number format to Text.

Alternatively, with A1 not initially formatted as Text enter

=REPT(REPT("a",255),128)

then change A1's number format to Text.

If anything the functionality you mention would seem to be a bug in the interaction between REPT called with a 2nd argument >= 256 and the Text number format. Yes, it is nice that Microsoft is fixing bugs, but I'll wait to see how XL12 handles MOD(2^30,3) before deciding whether they've been thorough or not.