I just noticed that the MS Excel column count increased from ZZ to XFD, i.e., 16,384 columns. What is the significance of this particular value? Why didn’t MS go further, up to ZZZ? Why did they stop at XFD?
-
1Not exactly answer to your question but MS list various excel limitations here - support.office.com/en-us/article/…– matrixCommented Jul 10, 2017 at 7:09
-
2It’s an arbitrary design decision to keep complexity within well-defined bounds. Technically, the only limit is available resources (memory, CPU).– Daniel BCommented Jul 10, 2017 at 7:30
-
8As @teylyn has explained in her answer, powers of 2 are common choices in informatics, however exactly why MS has choosen this value is probably not possible to answer here, so I'm voting to close this question as primary opinion based.– Máté JuhászCommented Jul 10, 2017 at 8:13
-
There's also stackoverflow.com/questions/526921/… and quora.com/…– AjasjaCommented Jul 10, 2017 at 19:05
-
3It's worth noting that the maximum column limit has been 16,384 since at least Excel 2007– StevoisiakCommented Jul 10, 2017 at 20:19
|
Show 1 more comment
2 Answers
The significance of 16,384 is that you can have internal column index values from 0 to 16383. Let's look at what 16383 is in binary:
0011 1111 1111 1111
It's 2 bits short of a 16-bit word. 1 bit is likely to be an absolute/relative flag, which leaves 1 bit for some other purpose, and the whole thing packs neatly in a single word.
The row numbers are similar: a maximum index value of 1,048,575 is this:
0000 0000 0000 1111 1111 1111 1111 1111
To me, as a programmer, that looks like a 32-bit word is being used, with the bottom 20 bits as the row index, and the top 12 bits used for something else.
-
1
-
91,048,576 is the first power of 2 that is more than a million... so now you can have "more than a million columns"– HorusKolCommented Jul 11, 2017 at 7:16
-
6and 16384 is the first power of 2 that is more than ten thousands. So that may be the reason the OP is looking for. Commented Jul 11, 2017 at 8:50
16,384 is 2^14.
At the same time as columns were expanded to 16,384, rows were expanded to over a million, i.e. 1,048,576.
These numbers relate to two to the power of 14 (2^14) and two to the power of 20 (2^20), respectively, so are natural (logical) progressions on the scale of computer memory units that were initially structured in bytes.
As computer memory increased, it was possible to load and evaluate bigger numbers of rows and columns in memory for calculations, so the Excel grid could grow from the limitations of earlier versions.
These maximum numbers for rows and columns have been selected by the people who design how Excel works, of course, and have become possible with growing computer memory. They could have chosen different numbers, but the number of rows and columns in Excel since version 2007 is rooted in the power of 2.
-
3
-
13@Ajasja You will need to ask the Excel development team about that. I said that they could have chosen different numbers in my answer. I only explained where the pattern comes from, not why a certain magnitude of the pattern was chosen.– teylynCommented Jul 10, 2017 at 10:08
-
15Actually as an Excel Microsoft MVP you are in a much better position to ask the Excel development team this question then, for example, my self:) Still a +1.– AjasjaCommented Jul 10, 2017 at 10:38
-
5Note: most of excel coding are about internal representation on formulas: 2^16 is impractical for 16-bit integer: Excel needs to store also relative positioning, so both negative ans positive numbers. Possibly one of the bit is used as flag (single/interval), so the total number of columns was adapted Commented Jul 10, 2017 at 12:26
-
1@GiacomoCatenazzi Relative positioning would work fine without a dedicated sign bit if you just consider any number that would take you past the last column to be negative. Commented Jul 10, 2017 at 15:25