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?
2 Answers
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 32 bit register can hold.
But they also need to store the format of the cell (text, number etc) and formatting (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.
Update 2016:
Found a link to Microsoft's specification for Excel 2013 & 2016
- Open workbooks: Limited by available memory and system resources
- Worksheet size: 1,048,576 rows (20 bits) by 16,384 columns (14 bits)
- Column width: 255 characters (8 bits)
- Row height: 409 points
- Page breaks: 1,026 horizontal and vertical (unexpected number, probably wrong, 10 bits is 1024)
- Total number of characters that a cell can contain: 32,767 characters (signed 16 bits)
- Characters in a header or footer: 255 (8 bits)
- Sheets in a workbook: Limited by available memory (default is 1 sheet)
- Colors in a workbook: 16 million colors (32 bit with full access to 24 bit color spectrum)
- Named views in a workbook: Limited by available memory
- Unique cell formats/cell styles: 64,000 (16 bits = 65536)
- Fill styles: 256 (8 bits)
- Line weight and styles: 256 (8 bits)
- Unique font types: 1,024 (10 bits) global fonts available for use; 512 per workbook
- Number formats in a workbook: Between 200 and 250, depending on the language version of Excel that you have installed
- Names in a workbook: Limited by available memory
- Windows in a workbook: Limited by available memory
- Hyperlinks in a worksheet: 66,530 hyperlinks (unexpected number, probably wrong. 16 bits = 65536)
- Panes in a window: 4
- Linked sheets: Limited by available memory
- Scenarios: Limited by available memory; a summary report shows only the first 251 scenarios
- Changing cells in a scenario: 32
- Adjustable cells in Solver: 200
- Custom functions: Limited by available memory
- Zoom range: 10 percent to 400 percent
- Reports: Limited by available memory
- Sort references: 64 in a single sort; unlimited when using sequential sorts
- Undo levels: 100
- Fields in a data form: 32
- Workbook parameters: 255 parameters per workbook
- Items displayed in filter drop-down lists: 10,000
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.
Related