29

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?

  • 1
    Not exactly answer to your question but MS list various excel limitations here - support.office.com/en-us/article/… – matrix Jul 10 '17 at 7:09
  • 2
    It’s an arbitrary design decision to keep complexity within well-defined bounds. Technically, the only limit is available resources (memory, CPU). – Daniel B Jul 10 '17 at 7:30
  • 8
    As @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ász Jul 10 '17 at 8:13
  • There's also stackoverflow.com/questions/526921/… and quora.com/… – Ajasja Jul 10 '17 at 19:05
  • 3
    It's worth noting that the maximum column limit has been 16,384 since at least Excel 2007 – Steven M. Vascellaro Jul 10 '17 at 20:19
34

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
    This makes more sense to me – Ooker Jul 10 '17 at 18:05
  • 9
    1,048,576 is the first power of 2 that is more than a million... so now you can have "more than a million columns" – HorusKol Jul 11 '17 at 7:16
  • 6
    and 16384 is the first power of 2 that is more than ten thousands. So that may be the reason the OP is looking for. – ris8_allo_zen0 Jul 11 '17 at 8:50
56

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.

  • 2
    Sure, but why not 2^16 and 2^32 (so a byte and a word)? – Ajasja Jul 10 '17 at 10:06
  • 12
    @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. – teylyn Jul 10 '17 at 10:08
  • 14
    Actually 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. – Ajasja Jul 10 '17 at 10:38
  • 5
    Note: 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 – Giacomo Catenazzi Jul 10 '17 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. – Random832 Jul 10 '17 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.