Alex Rivera | Logout

Why is there still a row limit in Microsoft Excel?

Asked 2009-02-09T01:53:01.003
58

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?

excel row-number limits
Edit
Report

1 Answer

48

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 th
answered 2009-02-09T02:54:36.193

Your Answer