No matter how many changes Microsoft makes to Excel as they release new versions, they never add more columns. This has been aggravating me for a long time, and now I have run into this limitation again. They always add more rows, but never columns.
Why is it you can have tens of thousands of rows, but only 256 columns? Why can’t the grid just be square(#rows=#columns)?
Because of the way the spreadsheet is defined in the program, Microsoft has to limit it to something. They could add more rows if they really wanted to but I don’t think that the popular demand is really there. Most people obviously preferred to have more rows than more columns. Even Oracle, the mother of all enterprise database systems, has a 1000 column limit on its tables. May I ask just what you are doing that requires so many columns? There may be a better design for it. I have built huge spreadsheets for years and never come close to maxing out the column limit.
Some wit (anyone know who?) advised software developers that when they had to put an arbitrary limit in software, to always make it a power of two. People will assume it’s a hardware limit rather than arbitrary.
Probably because they use a 1 byte address. It’s not a conspiracy, it’s arithmetic. Powers of 2 are going to show up in computer limits, since the number of addressable elements will always be 2**n, where n is the size of the address.
It sounds like they used an unsigned char ( a byte, or 8 bits ) to store the name of the column. If memory serves, there is a limit of 64k ( 65536 ) rows - an int, back in the good old days of lotus 123, 2 bytes now.
So, it takes 3 bytes to describe any single cell. Which means that the clever programmers who wrote the first version optimized for the fact that an entire row is described by one byte. Which means that to increase the number of rows, you have to rewrite to addressing routines - the ones that find and control the internal representation of data.
I’m guessing about all of this (I am a software developer, but not for MSFT ), but my educated opinion is that, in order to increase the number of columns, you’d have to rewrite a huge portion of Excel itself. Besides being decidedly nontrivial, there doesn’t seem to be demand for it.
From certain angles, using the knowledge of the internal structure of the cells in order to optimize speed and memory constraints is a Bad Thing ™. Given the age of Excel, and the requirements at the time, it was probably a good payoff.
It IS a hardware limit. The number of cells is, as bashere pointed out, and unsigned char. That means it is stored in the XLS a single byte with a value ranging from 0 (00000000) to 255 (11111111). Since you aren’t going to make a spreadsheet with 0 rows, they just add 1, and it makes it 1 to 256.
So why hasn’t Microsoft added more than 256 rows? Because doing so would require coming out with a whole new file format that would be incompatible with earlier versions of Excel (unlike, for example, NTSC color, which works on B&W and color televisions). And it’s a pain in the ass to get people to buy new software just so that they can use up to 65536 rows. Even for Microsoft. I guess they’re not as evil as I thought…but the point is, there isn’t enough demand to rewrite the whole program.
And just what were you doing that requires more than 256 rows, anyway?
If you want the full XLS specs, there is an explanatory TXT file at http://www.wotsit.org/, but unless you’re a programmer, it won’t be much use.
Great site, KJ! I’m bookmarking that one …
But didn’t the XLS spec change several times already? I seem to recall exporting spreadsheets in 95 and 97 format, although I may be mis-remembering …
Arjuna34
Dynamically allocating memory in a two dimensional data structure is significantly more difficult than for one dimension. It certainly would be possible, but would be one more problem that the designers probably didn’t want to deal with. By having a set number of columns rather than a variable number, the dynamic part of the data structure is reduced to the one dimensional case. Having a square spreadsheat would be even worse, since every time you add a row, the memory for the columns would have to be reallocated. If you really want more than 256 columns, interlace the rows (row 1 => columns 1-256 of row1, row 2 => colums 257-512 of row1, row 3 => columns 1-256 of row2, etc.)
I have to go with KJ here. What the heck are you trying to shoehorn into that poor spreadsheet that takes more than 256 columns? Sounds like an inappropriate use of the tool.
Seems that, given that rows and columns are pretty much functionally interchangeable, you could just map your whole project orthogonally 90 degrees clockwise or counterclockwise and have tens of thousands of columns–course then you’re limited to 256 rows.
Can’t have everything, I guess…
Although there is a limit of 256 columns on any **worksheet **there is nothing stopping you from having multiple sheets cross referencing each other in the **workbook ** and bypassing the restriction that way, since you aren’t going to show 256 columns on your computer at one time anyways.