G
Guest
why does excel have a limit of 65536 rows? My users want reports in Excel but
the records exceed 65536.
the records exceed 65536.
F
Frank Kabel
Hi
that's just the way Excel is designed
If you need more records you
probably should use a database. No way around this limit in Excel
that's just the way Excel is designed
If you need more records youprobably should use a database. No way around this limit in Excel
F
Fred Smith
Excel stores its row number in a two-byte (16-bit) field. So the maximum
number you can store is 2^16 or 65,536. That's why the limitation. Same for
column numbers, which Excel stores in a on-byte field. IV is column 256
which is 2^8. Doesn't solve your problem, but that's why.
The obvious next question is, with today's virtually limitless memory, why
doesn't Microsoft expand the size of these fields? They'll tell you it would
affect some existing VBA macros, but I think the real answer is they are
lazy.
number you can store is 2^16 or 65,536. That's why the limitation. Same for
column numbers, which Excel stores in a on-byte field. IV is column 256
which is 2^8. Doesn't solve your problem, but that's why.
The obvious next question is, with today's virtually limitless memory, why
doesn't Microsoft expand the size of these fields? They'll tell you it would
affect some existing VBA macros, but I think the real answer is they are
lazy.
G
Guest
Well, from the computer user's point of view, you say that "I can pay to buy
more memory", "I have plenty of disk space", "My computer is super fast".
Therefore, the problem is with Microsoft, as it appears that they're lazy and
didn't make any improvement in a worksheet's dimension.
However, if you think from Microsoft Excel development team's perspective,
there could be a lot of concerns. Some are obvious and known concerns. Some
are potential and uncertain concerns. It will be highly risky to the company
if they proceed to make such change in Excel. Even their share price may
drop! Then, who will be responsible?
Imagine. IF, the dimensions of a spreadsheet is changed to:
4,294,967,296 rows X 65,536 columns.
It can then potentially contains 16,777,216 times more formulas in one
worksheet than the current dimensions.
It will then have 16,777,216 times more formulas to re-calculate whenever a
cell is changed.
Users will immediately complain their worksheets are frozen and cannot
refresh or cannot save!!! Although you MAY hit the Esc key to abort
re-calculation sometimes, the figures will be wrong, as they file has not yet
been completely re-calculated!! Then, more complains.
Also, if your current Excel file is 15MB in size, bring out your calculator,
and work out how many new 160GB hard drives you (or your company) will be
prepared to buy.
Auditors (or IT Auditors) in corporations will keep reporting that their
corporations do not have the capacity in their storage and backup media for
handling the critical business data, e.g. finance and accounting data. And
they'll recommend corporations either buy plenty of 160GB hard drives or do
not use Excel any more.
All corporations will need to perform critical review on all usage and
potential usage of Excel workbooks, and issue new operational and IT
procedural guidelines on the usage and handling of new Excel files. (You can
imagine the cost of doing so.) The big-4 audit firms (such as PWC, KPMG)
will then make a lot more money by carrying out such reviews.
.... The potential issues are endless. If a certain project manager in
Microsoft is brave enough to put this into the new specifications of the next
Excel, he must be one of the bravest staff and he'll have prepare to approach
head hunters soon.
Edwin Tam
(e-mail address removed)
http://www.vonixx.com
more memory", "I have plenty of disk space", "My computer is super fast".
Therefore, the problem is with Microsoft, as it appears that they're lazy and
didn't make any improvement in a worksheet's dimension.
However, if you think from Microsoft Excel development team's perspective,
there could be a lot of concerns. Some are obvious and known concerns. Some
are potential and uncertain concerns. It will be highly risky to the company
if they proceed to make such change in Excel. Even their share price may
drop! Then, who will be responsible?
Imagine. IF, the dimensions of a spreadsheet is changed to:
4,294,967,296 rows X 65,536 columns.
It can then potentially contains 16,777,216 times more formulas in one
worksheet than the current dimensions.
It will then have 16,777,216 times more formulas to re-calculate whenever a
cell is changed.
Users will immediately complain their worksheets are frozen and cannot
refresh or cannot save!!! Although you MAY hit the Esc key to abort
re-calculation sometimes, the figures will be wrong, as they file has not yet
been completely re-calculated!! Then, more complains.
Also, if your current Excel file is 15MB in size, bring out your calculator,
and work out how many new 160GB hard drives you (or your company) will be
prepared to buy.
Auditors (or IT Auditors) in corporations will keep reporting that their
corporations do not have the capacity in their storage and backup media for
handling the critical business data, e.g. finance and accounting data. And
they'll recommend corporations either buy plenty of 160GB hard drives or do
not use Excel any more.
All corporations will need to perform critical review on all usage and
potential usage of Excel workbooks, and issue new operational and IT
procedural guidelines on the usage and handling of new Excel files. (You can
imagine the cost of doing so.) The big-4 audit firms (such as PWC, KPMG)
will then make a lot more money by carrying out such reviews.
.... The potential issues are endless. If a certain project manager in
Microsoft is brave enough to put this into the new specifications of the next
Excel, he must be one of the bravest staff and he'll have prepare to approach
head hunters soon.
Edwin Tam
(e-mail address removed)
http://www.vonixx.com
J
JE McGimpsey
Never ascribe to laziness what can be adequately be explained by
cost-benefit ratio...
At least so far, the enormous cost of expanding rows and columns (and it
would be enormous - every bit of millions of lines of code would be
affected) just hasn't been enough perceived demand (i.e., additional
copies of Excel/Office sold) to justify the cost.
I would guess that if MS decides that they can make a buck by expanding
rows or columns, they'd be anything but lazy, much like when they
expanded from 16768 to 65536 rows with XL97. Until then, it makes no
sense for them to invest the capital.
cost-benefit ratio...
At least so far, the enormous cost of expanding rows and columns (and it
would be enormous - every bit of millions of lines of code would be
affected) just hasn't been enough perceived demand (i.e., additional
copies of Excel/Office sold) to justify the cost.
I would guess that if MS decides that they can make a buck by expanding
rows or columns, they'd be anything but lazy, much like when they
expanded from 16768 to 65536 rows with XL97. Until then, it makes no
sense for them to invest the capital.
D
Don Guillett
Don't complain or they will go back to 16k rows like xl95
S
Sandy V
FWIW it appears Excel can handle 676 columns in A:ZZ !!
SaveAs: html
Publish... > Add interactivity with
Publish (tick open in browser)
OK, this is not very useful but how does it do that ...
Regards,
Sandy
SaveAs: html
Publish... > Add interactivity with
Publish (tick open in browser)
OK, this is not very useful but how does it do that ...
Regards,
Sandy
D
Dave Peterson
This uses the Office Web Components COM components
From:
http://www.microsoft.com/downloads/...59-0A86-4FB2-A7EE-5F3A499515DD&displaylang=en
(one line in your browser)
The Office Web Components tool provides view-only functionality for users who do
not have Office XP installed. By installing this tool, users can view published
components and data access pages on the Web without having to install Office XP.
Note: If you do not own an Office XP license, the Office Web Components will run
with view-only functionality rather than with full interactive functionality.
(Data access pages do not require an Office XP license, so you will always be
able to take advantage of a page's full interactive functionality.)
====
I've never even loaded this on my pc. There are a lot of restrictions on
when/how you can use them.
From:
http://www.microsoft.com/downloads/...59-0A86-4FB2-A7EE-5F3A499515DD&displaylang=en
(one line in your browser)
The Office Web Components tool provides view-only functionality for users who do
not have Office XP installed. By installing this tool, users can view published
components and data access pages on the Web without having to install Office XP.
Note: If you do not own an Office XP license, the Office Web Components will run
with view-only functionality rather than with full interactive functionality.
(Data access pages do not require an Office XP license, so you will always be
able to take advantage of a page's full interactive functionality.)
====
I've never even loaded this on my pc. There are a lot of restrictions on
when/how you can use them.
S
Sandy V
Thanks Dave for this illumination.
Like you I've never downloaded this, but can get Excel to work in a
limited way with 676 columns.
Wonder what else Excel can do with a bit of a nudge...
Regards,
Sandy
Like you I've never downloaded this, but can get Excel to work in a
limited way with 676 columns.
Wonder what else Excel can do with a bit of a nudge...
Regards,
Sandy