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
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.
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.