Why is there still a row limit in Microsoft Excel?

Question

Until Office 2007, Excel has a maximum of 65,000 rows. Office 2007 bumped that up to a max of 1 million rows, which is nicer of course; but I'm curious -- why is there a limit at all? Obviously, performance will slow down exponetially as you increase the spreadsheet size; but it shouldn't be very hard to have Excel optimize for that by starting with a small sheet and dynamically "re-sizing" it only as needed. Given how much work it must have been to increase the limit from 65K to 1 million, why didn't they go all the way so it's limited only by the amount of available memory and disk space?

Answer 1

In a word - speed. An index for up to a million rows fits in a 32-bit word, so it can be used efficiently on 32-bit processors. Function arguments that fit in a CPU register are extremely efficient, while ones that are larger require accessing memory on each function call, a far slower operation. Updating a spreadsheet can be an intensive operation involving many cell references, so speed is important. Besides, the Excel team expects that anyone dealing with more than a million rows will be using a database rather than a spreadsheet.

Answer 2

Maybe after you fill up every cell it does expand. Space dog also has a good point, if you need more than 1,000,000,000,000 (1,000,000^2) cells worth of data, you might as well use a data base.

Answer 3

I think this is one of those "if you have to ask, you're doing something wrong" questions.

Answer 4

(updated because of error... A suggestion to everyone: don't post on SO before you are fully awake)

Probably because of optimizations. Excel 2007 can have a maximum of 16 384 columns and 1 048 576 rows. Strange numbers?

14 bits = 16 384, 20 bits = 1 048 576

14 + 20 = 34 bits = more than one register can hold.

But they also need to store the format of the cell (text, number etc) and formating (colors, borders etc). Assuming they use two 32-bit words (64 bit) they use 34 bits for the cell number and have 30 bits for other things.

Why is that important? In memory they don't need to allocate all the memory needed for the whole spreadsheet but only the memory necessary for your data, and every data is tagged with in what cell it is supposed to be in.

Answer 5

A counter question:

write a program that asks for users name and stores it somewhere? How do you decide how much space to assign to the variable you are going to use to store the user input. One obvious answer is to allocate all the free memory in the system. But we can do better right?