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.
Tips
How-to
News
Videos
Stories
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
Comments: (32) Collapse
I second increasing the number of outline levels.
Absolutely incredible!
"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!
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.
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?
Hi Rob - we have not made any changes to querytables and paramaters.
What about the number of sheets?
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.
-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.
>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.
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
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.
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.
"
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.
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!
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