32

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?

TRiG
  • 1,310
  • 1
  • 15
  • 33
Karth
  • 431
  • 1
  • 4
  • 5
  • 1
    Not exactly answer to your question but MS list various excel limitations here - https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US&fromAR=1 – matrix Jul 10 '17 at 07: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 07: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 08:13
  • There's also https://stackoverflow.com/questions/526921/why-is-there-still-a-row-limit-in-microsoft-excel and https://www.quora.com/What-is-the-reason-behind-an-Excel-spreadsheet-having-1048576-rows – 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](https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2007) – Stevoisiak Jul 10 '17 at 20:19
  • Someone "on the internet" posited that, since the columns are letters, the programmers may have been trying to avoid the appearance of certain 4-letter words. This is plausible, since 26^3 = about 17,500. Now I am curious. – Yorik Jul 11 '17 at 20:45

2 Answers2

58

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.

teylyn
  • 22,498
  • 2
  • 40
  • 54
  • 3
    Sure, but why not 2^16 and 2^32 (so a byte and a word)? – Ajasja Jul 10 '17 at 10:06
  • 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. – teylyn Jul 10 '17 at 10:08
  • 15
    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
  • 1
    @Random832 - while true, that is also quite likely to be a potential source for bugs, so I can understand why the Excel team wouldn't want to do that. – Jules Jul 11 '17 at 00:38
  • 1
    @Jules in particular if they did so in the past the upgrade from 2003 (65536 rows, 256 columns) would've been a lot more painful; as would any future increases to sheet size. – Dan Is Fiddling By Firelight Jul 11 '17 at 15:11
  • 1
    @Ajasja I assume that something like 2^32 rows would lead to memory shortage; presumably there is an integer array or a list holding references to rows or cells indexed by row number. Surely only used cells are actual objects in memory (and in the extreme case of using all cells many normal PCs would run out of it in current Excel); but with 2^32 rows even the "administrative" information in a sparsely but extensively populated sheet would become huge. – Peter - Reinstate Monica Jul 12 '17 at 10:07
35

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.

David
  • 548
  • 5
  • 9