Some other numbers ...

re: Some other numbers ...

anon — Tue, 27 Sep 2005 10:15:15 GMT

"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, that's available since Excel 97. I installed good ol' Excel 97 the other day and this feature title appears on one of the install screens while installing.

What could possibly be fixed however, is that there were clipboard and round-tripping problems with the COM OM when using cells with more than 255 bytes under certain circumstances. Since Excel 97.

re: Some other numbers ...

Sam — Tue, 27 Sep 2005 10:36:59 GMT

Hi David
How about
1) Validation - Allowing list types to be referenced from other sheets/workbooks
- Workaround - Defined names
2) Circle Invalid Data - Limited to 256 Red circles (I think)
3) Copy - Paste on Validated Cells removes validation without warning,same for conditional fromating
4)Allow Macors to work in the "Cell Edit Mode"
5)Improving the Interface for Advanced Filter
6) Improving the Syntax of all Database functions...DSUM,Dcount etc

Sam

re: Some other numbers ...

Boris — Tue, 27 Sep 2005 11:00:52 GMT

How about improving the way INDIRECT works?
I know the official line is that Excel is not a Lotus 123 clone but their version (@@) has considerably more flexibility when creating variable links to other files.

re: Some other numbers ...

Boris — Tue, 27 Sep 2005 11:04:47 GMT

Two things about this one:

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

1) I think you meant that the NEW limit is 64
2) You know that Lotus 123 has had an unlimited restriction on this since its original DOS 3D version?

Pip pip

re: Some other numbers ...

Frank — Tue, 27 Sep 2005 11:16:48 GMT

So how will the columns be numbered? 3 characters?

re: Some other numbers ...

Sam — Tue, 27 Sep 2005 11:27:54 GMT

Hi David.
Please do not change the interface... the worst nigthmare I can imagine is to start finding all the buttons again!!

Dont try and improve something thats working fine already
Sam

re: Some other numbers ...

Boris (once more) — Tue, 27 Sep 2005 12:09:58 GMT

DSUM function, and all the other D functions.

Allow SQL syntax as the argument rather than having to set up a criteria range ... a bit like the wat 123 has been doing it for years :-)

re: Some other numbers ...

Nigel Harper — Tue, 27 Sep 2005 14:14:00 GMT

David,

Well done to you and your team. This is a quantum leap forward in many areas, and is certainly more than I expect many people believed would be delivered. I can't wait to get my teeth into it
Could we hear a little more about the Calculation side of things (Dependency Tree, Calculation Algorithms) given that it already can be slow to calculate some array formulas over a couple of thousand rows let alone trying it over a couple of hundred-thousand. In other words how are you catering for this increase in worksheet size in the calculation engine?
Also, could we hear a little more about the Object Model (will we be seeing a full diagram anytime soon?) and VBA side too? One of my personal bug-bears is trying to place long array-formulas in cells via VBA and being limited to the 255 character length string for the formula when being entered in by code this way.

re: Some other numbers ...

Jim Rech — Tue, 27 Sep 2005 17:22:00 GMT

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.

re: Some other numbers ...

Adam Gentry — Tue, 27 Sep 2005 17:27:24 GMT

I work for a large bank and we currently are pushing the boundaries of what your product can support. One of those boundaries we encountered is in defining a range with a large quantity of non-contigous cells. If you define a named range and the length of the string of cells locations (including page names) exceeds 255 characters an error is thrown up. We currently have a CDCR with your organization and if this can not be solved in the current version, I would highly recommend removing this limitation in the next.

Reg,
Adam

re: Some other numbers ...

Kaleb — Tue, 27 Sep 2005 18:47:08 GMT

Will these changes mean changes to the BIFF format, or are the new limits supported only with the new XML-based format?

re: Some other numbers ...

John Greenan — Tue, 27 Sep 2005 18:50:42 GMT

This is all very good sounding stuff. Could you please take a moment to describe any changes you are making to the RTD components of Excel?

While RTD is much better than the very clunky DDE server, it's still lacking in features and in granularity of control.

Thanks in advance,

John

re: Some other numbers ...

simon murphy — Tue, 27 Sep 2005 20:30:21 GMT

David
Thanks for the info - very useful.
Q: Is VSTA going to be there alongside VBA??
cheers
Simon

re: Some other numbers ...

anon — Tue, 27 Sep 2005 21:39:54 GMT

Outline levels? (currently limit is 8)

re: Some other numbers ...

simon murphy — Wed, 28 Sep 2005 00:15:06 GMT

Message for Adam Gentry
Bob Umlas has some workarounds for this in his new book - its not Excel its magic.
1. temporarily rename sheet to 'a' whilst defining range, rename after
2. create 'sub' names and union them together
3. Select and use the name box - limit here 224 or so non contigous areas
cheers
Simon

re: Some other numbers ...

Susan — Wed, 28 Sep 2005 05:28:01 GMT

I second increasing the number of outline levels.

re: Some other numbers ...

Maurice — Wed, 28 Sep 2005 07:01:16 GMT

Absolutely incredible!

re: Some other numbers ...

Chartman of the Bored — Wed, 28 Sep 2005 09:07:10 GMT

"Next post I will step back a bit and review all the areas where we have made feature investments in Excel 12."

I'm keeping my fingers crossed that one of these includes a "Median" option in pivot tables... Seems like a no-brainer... More support/options for calculated fields would help too! End of rant.

Thanks for the updates!

re: Some other numbers ...

David Gainer — Wed, 28 Sep 2005 10:17:14 GMT

Again, thanks very much for your interest and questions and comments.

Sam, Boris, Adam, anon, Susan – The list of limits above that we worked on this time is pretty much complete; we didn’t make changes in Excel 12 to the further areas you asked about.

Frank – The new columns will be characters continuing from where we leave off today – column 257 will start at IV and columns will continue up to XFD.

Nigel – I will write an article in the future about our calculation changes, so bear with me – we have a lot to talk about this release! I will also add VBA OM additions to my list of topics.

Jim – Thanks for the clarification.

Adam – See Simon’s comments below, and thanks for the feedback.

Kaleb – The answer is a bit of both, and compatibility will be another place where I will write a few posts at some point in the future. Some of the limits – like rows and columns – will only be available using Excel 12 files. Other limits – like more colours – can be successfully round-tripped through BIFF8 files (Excel 97 – 2003 format), although you won’t see the colours in earlier versions of Excel.

John – We have not made any changes to RTD in this version.

Simon – For the core Office apps, VBE remains the integrated development environment.

re: Some other numbers ...

Rob van Gelder — Wed, 28 Sep 2005 11:25:48 GMT

David,

Thank you for taking the time to answer questions.

I have some solutions where I use QueryTables Parameters.
Querytables come in a few flavours. I use ODBC Querytables mostly. I want to use OLEDB querytables but Parameters dont work for them. Only ODBC querytables support parameters.

Could you please comment on whether OLEDB querytables will support parameters?

re: Some other numbers ...

David Gainer — Wed, 28 Sep 2005 18:50:46 GMT

Hi Rob - we have not made any changes to querytables and paramaters.

re: Some other numbers ...

calvin — Thu, 29 Sep 2005 08:02:47 GMT

What about the number of sheets?

re: Some other numbers ...

Harlan Grove — Fri, 30 Sep 2005 23: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.

re: Some other numbers ...

Joseph — Sat, 01 Oct 2005 18: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 ...

Andrew — Mon, 03 Oct 2005 20: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 ...

Harlan Grove — Tue, 04 Oct 2005 01: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 ...

Jan Karel Pieterse — Tue, 04 Oct 2005 12: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 ...

Scott — Tue, 04 Oct 2005 15: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 ...

David Gainer — Wed, 05 Oct 2005 08: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 ...

Rob van Gelder — Sun, 09 Oct 2005 22: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 ...

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

I really am pleased that the limit of "approximately 4000" on the number of different cell formats (inadequately discussed in http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;213904) 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 ...

David Gainer — Wed, 19 Oct 2005 07: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.

Excel 2007 – The Final Look, and more rows and columns

Microsoft Excel 12 — Fri, 10 Mar 2006 00:14:29 GMT

The Final LookFor most of the history of this blog, I have been showing you screenshots produced using...

Excel 2007 documents – easy on the eyes

Microsoft Excel 2007 (nee Excel 12) — Mon, 13 Mar 2006 21:27:40 GMT

One of the themes we focused on with Office 2007 was “great looking documents”.&nbsp; For the next half-dozen...

VIRGINIA LOTTERY WINNING NUMBERS » Blog Archive » baynews9.com - Lotto (Virginia lottery winning numbers) Numbers

Thu, 29 Jun 2006 01:20:55 GMT

PingBack from http://virginia-lottery-winning-numbers.aandecatalog.com/baynews9com-lotto-virginia-lottery-winning-numbers-numbers/

All About File Formats

Microsoft Excel 2007 (nee Excel 12) — Fri, 21 Jul 2006 00:39:51 GMT

To this point in the blog, I haven't talked too much about the file formats that Excel 2007 uses, since...

XL[S|T|A][X|M] or XLSB?

Alexander Holy's Weblog — Wed, 23 Aug 2006 14:21:54 GMT

The new Excel File Formats. Like all "new" file formats coming along Vista and Office 2007,&nbsp;these...

links for 2006-10-10 at DeStructUred Blog

links for 2006-10-10 at DeStructUred Blog — Tue, 10 Oct 2006 22:57:58 GMT

PingBack from http://www.joselise.com/wp/2006/10/10/links-for-2006-10-10/

Daily VaR » Blog Archive » No more limits on Excel ??? almost

Daily VaR » Blog Archive » No more limits on Excel ??? almost — Sun, 07 Jan 2007 01:52:41 GMT

PingBack from http://dailyvar.com/?p=3

Importing HTML Tables with DataDefractor at DataDefractor Blog

Importing HTML Tables with DataDefractor at DataDefractor Blog — Wed, 24 Oct 2007 01:12:22 GMT

PingBack from http://www.datadefractor.com/blog/index.php/archives/importing-html-tables-with-datadefractor

conditional formatting in excel

conditional formatting in excel — Sun, 25 May 2008 09:08:00 GMT

PingBack from http://jamarion.availablenewsmaterial.info/conditionalformattinginexcel.html

excel text limit

excel text limit — Sat, 21 Jun 2008 03:27:23 GMT

PingBack from http://destini.brookwork.com/exceltextlimit.html

increase virtual memory

increase virtual memory — Thu, 03 Jul 2008 12:43:21 GMT

PingBack from http://stone.sexyadultstories.com/increasevirtualmemory.html

65536 | keyongtech

65536 | keyongtech — Thu, 22 Jan 2009 07:41:03 GMT

PingBack from http://www.keyongtech.com/762806-65536

Plage dynamique - Page 2 | hilpers

Plage dynamique - Page 2 | hilpers — Thu, 22 Jan 2009 18:57:36 GMT

PingBack from http://www.hilpers.fr/791284-plage-dynamique/2

Ilo?? znak?w w kom?rce Excel | hilpers

Ilo?? znak?w w kom?rce Excel | hilpers — Fri, 23 Jan 2009 06:38:38 GMT

PingBack from http://www.hilpers.pl/44205-ilo-ae-znakow-w-komorce

Microsoft Excel Some other numbers | Patio Chairs

Microsoft Excel Some other numbers | Patio Chairs — Wed, 03 Jun 2009 05:28:48 GMT

PingBack from http://patiochairsite.info/story.php?id=29380

Microsoft Excel Some other numbers | Cellulite Creams

Microsoft Excel Some other numbers | Cellulite Creams — Tue, 09 Jun 2009 06:00:51 GMT

PingBack from http://cellulitecreamsite.info/story.php?id=8203

Microsoft Excel Some other numbers | Quick Diets

Microsoft Excel Some other numbers | Quick Diets — Tue, 09 Jun 2009 13:46:19 GMT

PingBack from http://quickdietsite.info/story.php?id=12001

Microsoft Excel Some other numbers | patio set

Microsoft Excel Some other numbers | patio set — Thu, 18 Jun 2009 05:17:53 GMT

PingBack from http://patiosetsite.info/story.php?id=164

Microsoft Excel Some other numbers | patio umbrella

Microsoft Excel Some other numbers | patio umbrella — Thu, 18 Jun 2009 07:04:18 GMT

PingBack from http://patioumbrellasource.info/story.php?id=1769

Microsoft Excel Some other numbers | debt consolidator

Microsoft Excel Some other numbers | debt consolidator — Fri, 19 Jun 2009 18:41:15 GMT

PingBack from http://mydebtconsolidator.info/story.php?id=23065