Why only 65536 rows?

[Gabriel Lozano-Morán]

A question that has been bugging me is why Excel has a limit of 65536 rows
(2^16)? Is the reason just plain and simple that Microsoft limited the row
pointers to two bytes? Or is there an other reason like the rows are limited
to 65536 because every rows gets a handle from the system and except for the
x64 based systems all OS's are 16-bit compatible thus there is a limit of
max 2^16 handles?

If you search on google the only thing you will find is that Excel is
limited to 65536 rows but there is no explanation on why this is so.

Gabriel Lozano-Morán

 

[Pete]

Quattro (I'm sorry, is that a forbidden word?) had 1,000,000 rows in
versions that were brought out in the 1990's, well before any 64bit
systems were around, so that's no excuse for Microsoft.

Pete

 

[Bob Phillips]

FYI the next Excel version has over a million rows.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Nick Hodge]

Gabriel

I suspect a lot of this is 'legacy' problems. There are billions of copies
of Excel out there and they must be very careful to support backward for
some time and much is driven by grid size. This will be changing soon, but
personally I dread Excel having more than it's 65k...maybe a few more
columns but given them people will use them and then complain how slow it is
when they apply a VLOOKUP to 1m rows!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Harlan Grove]

Nick Hodge wrote...
....

columns but given them people will use them and then complain how slow it is
when they apply a VLOOKUP to 1m rows!

....

Or maybe they'll learn the benefits of sorting lookup tables to take
advantage of binary search. With binary search, it takes only
ROUNTUP(LOG(NumberOfRows,2),0) compares to locate matches. For 2^20
rows, that's only 20 compares.

It'll be interesting to see whether it's 'give 'em enough rope to hang
'emselves' or 'given enough rope, they'll learn how to tie square knots
rather than granny knots'.

 

[Bucky]

A question that has been bugging me is why Excel has a limit of 65536 rows

Because 20 years ago, Bill Gates decided that no one will ever need
more than 64K rows in Excel. =)

(Yes I know that Bill Gates didn't actually say that no one ever will
ever need more than 640K memory for a personal computer. But it's
still fun to make fun of him.)

 

[JE McGimpsey]

I know where I'm placing *my* bet...

 

[Nick Hodge]

Me too....;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Gabriel Lozano-Morán]

I have posted my findings on our companies blog site:
http://www.realdn.net/msblog/PermaLink,guid,47c73ccf-068b-4c43-89e3-bade2d890cc4.aspx

I hope that it is accurate enough... please feel free to comment if I got
something wrong because I hate to give people the wrong information...

Cheers

Gabriel Lozano-Morán
MCSD .NET
Real Software
http://www.realdn.net
http://www.realsoftware.be

 

[Suzette]

Perhaps because when you get to that many rows the information should be in
a database? If that many rows are being used the file has to be huge and
cumbersome.

 

[Jerry W. Lewis]

Excel 97 increased the row limit from 16384=2^14 to 65536=2^16. The
next version will increase to 1048576=2^20
http://blogs.msdn.com/excel/archive/2005/09.aspx
but the fact remains that (in almost all cases) if you are constrained
by by the row limit, then there is a better tool for the task than a
spreadsheet.

Jerry

 

[(PeteCresswell)]

Per Gabriel Lozano-Mor?n:

..that Excel is
limited to 65536 rows but there is no explanation on why this is so.

It's to keep us MS Access developers in business....-)

 

[Ed Ferrero]

Hi Gabriel,

That looks fair enough. Also remember that Excel actually stores cell
information sequentially - try running this little bit of code to see what I
mean...

Sub tst()
For i = 255 To 258
Debug.Print Cells(i).Address
Next
For i = 16777214 To 16777216
Debug.Print Cells(i).Address
Next
End Sub

So there are 16,777,216 cells in a worksheet 2^24, arranged in 256 columns.

Ed Ferrero
http://edferrero.m6.net/

 

[Nick Hodge]

Pete

When Excel 'discards' something it passes it to it's other Office Add-Ins
like Access, Word, PowerPoint, etc ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Bill Sharpe]

Pete

When Excel 'discards' something it passes it to it's other Office Add-Ins
like Access, Word, PowerPoint, etc ;-)

"Only" 65,536 rows? That's still a lot of rows...
Then go to sheet 2.

Bill

 

[David McRitchie]

Hi Ed,
That does not prove anything about how Excel stores information.
What you are looking at is how Excel returns consecutive items
in a range, as a logical arrangement, not as physical storage.

 

[Ed Ferrero]

Hi David,

Youre right, sorry for the sloppy wording. The thread reminded
me of an old discussion on treating worksheets as if they had
more than 256 columns.

For example, if I wanted a (virtual?) worksheet with 512 columns,
I could use the following function to determine the value of a
cell with an address of 8192 rows by 512 columns.

Function ValCell(bigRow, bigCol)
cellInd = (bigRow - 1) * 512 + bigCol
ValCell = Cells(cellInd).Value
End Function

Adding two cells could be accomplished by;

Function BigAdd(row1, col1, row2, col2)
BigAdd = ValCell(row1, col1) + ValCell(row2, col2)
End Function

Ed Ferrero
http://edferrero.m6.net/

 

[David McRitchie]

Hi Ed,
Clever, what kind of data do you store in that manner that you
don't really have to look at.

 

[Ed Ferrero]

Hi David,

I once toyed around with data that had more than 256 columns. Tried this
approach but it quickly became kind of messy - the data also needed to be
filtered. Much easier to spread the data over two sheets and use VLOOKUP to
build a summary sheet. I forget what the data was all about - some sort of
survey with lots of variables I think.

Ed