You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
As part of the Excel team’s work to increase the number of rows and columns in Excel 12, we also increased a number of the other “limits” in the product. This work falls into a two categories.
First, we increased a number of limits to support our “big grid” work. These are limits that we increased to make sure that all of Excel’s features could scale to handle more rows and columns. A lot of folks have already asked about these sorts of limits in comments to my first post, in emails, and in comments on other blogs and websites. For example, we increased the number of rows allowed in a PivotTable from 64k to 1 million (2^20 to be precise), we increased the amount of memory that Excel can use from 1GB to the maximum allowed by Windows, and we completely eliminated the limit on the number of rows of a column or columns that can be referred to in an array formula.
Second, we took the opportunity to increase a number of other limits that our customers had asked us to increase over the years. For example, we increased the number of colours allowed in a single workbook from 56 (indexed colour) to 4.3 billion (32-bit colour), and we increased the number of characters that can be stored and displayed in a cell formatted as Text from 255 to 32k. We increased the number of levels of sorting possible on a range, and the number of conditional formats possible on a cell. Some of these limits – like the number of levels of sorting possible on a range – obviously require UI changes; I will discuss those in later posts when I cover the other work we have done in those areas.
Here is a list of all of the major changes we made to Excel 12 in the area of limits.
The total number of available columns in ExcelOld Limit: 256 (2^8)New Limit: 16k (2^14)
The total number of available rows in ExcelOld Limit: 64k (2^16)New Limit: 1M (2^20)
Total amount of PC memory that Excel can use Old Limit: 1GBNew Limit: Maximum allowed by Windows
Number of unique colours allowed a single workbookOld Limit: 56 (indexed colour)New Limit: 4.3 billion (32-bit colour)
Number of conditional format conditions on a cellOld Limit: 3 conditionsNew Limit: Limited by available memory
Number of levels of sorting on a range or tableOld Limit: 3New Limit: 64
Number of items shown in the Auto-Filter dropdownOld Limit: 1,000New Limit: 10,000
The total number of characters that can display in a cellOld Limit: 1k (when the text is formatted)New Limit: 32k or as many as will fit in the cell (regardless of formatting)
The number of characters per cell that Excel can printOld Limit: 1kNew Limit: 32k
The total number of unique cell styles in a workbook (combinations of all cell formatting)Old Limit: 4000New Limit: 64k
The maximum length of formulas (in characters)Old Limit: 1k charactersNew Limit: 8k characters
The number of levels of nesting that Excel allows in formulasOld Limit: 7New Limit: 64
Maximum number of arguments to a functionOld Limit: 30New Limit: 255
Maximum number of items found by “Find All”Old Limit: ~64k (65472)New Limit: ~2 Billion
Number of rows allowed in a Pivot TableOld Limit: 64kNew Limit: 1M
Number of columns allowed in a Pivot TableOld Limit: 255New Limit: 16k
Maximum number of unique items within a single Pivot FieldOld Limit: 32kNew Limit: 1M
Length of the MDX name for a Pivot Table item; also the string length for a relational Pivot TableOld Limit: 255 charactersNew Limit: 32k
The length at which fields’ labels are truncated when added to PivotTable; this also includes caption length limitationsOld Limit: 255New Limit: 32k
The number of fields (as seen in the field list) that a single PivotTable can haveOld Limit: 255New Limit: 16k
The number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)Old Limit: 8kNew Limit: Limited by available memory
The number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)Old Limit: 64kNew Limit: Limited by available memory
The number of array formulas in a worksheet that can refer to another (given) worksheetOld Limit: 65kNew Limit: Limited by available memory
The number of categories that custom functions can be bucketed intoOld Limit: 32New Limit: 255
The number of characters that may be updated in a non-resident external workbook referenceOld Limit: 255New Limit: 32k
Number of rows of a column or columns that can be referred to in an array formulaOld Limit: 65,335New Limit: Limitation removed (full-column references allowed)
The number of characters that can be stored and displayed in a cell formatted as TextOld Limit: 255New Limit: 32k
For those of you that read this far, thanks. Next post I will step back a bit and review all the areas where we have made feature investments in Excel 12. It is a pretty big list, and I am excited to share it with you.
PS Updated to fix a typo
"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.
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
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.
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?
So how will the columns be numbered? 3 characters?
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
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 :-)
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.
>>"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.
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.
Will these changes mean changes to the BIFF format, or are the new limits supported only with the new XML-based format?
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,
Thanks for the info - very useful.
Q: Is VSTA going to be there alongside VBA??
Outline levels? (currently limit is 8)
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