Google Answers Logo
View Question
 
Q: microsoft excel ( Answered,   3 Comments )
Question  
Subject: microsoft excel
Category: Business and Money > Finance
Asked by: msg-ga
List Price: $5.00
Posted: 26 Apr 2002 11:32 PDT
Expires: 03 May 2002 11:32 PDT
Question ID: 6207
how do i get excel to go beyond 65,536 rows?
Answer  
Subject: Re: microsoft excel
Answered By: researcher-ga on 26 Apr 2002 11:58 PDT
 
It is not possible for Microsoft Excel to go beyond 65,536 rows in a
single worksheet (also known as a sheet or a spreadsheet). If you
require more than 65,536 rows, you will have to split the information
across multiple worksheets.

There is also a limitation on the number of columns and characters in
a single cell per worksheet. At most 256 columns and 32,000 characters
in a single cell can be used per worksheet. Again, to get beyond this
limit in Microsoft Excel, one will have to open a new worksheet.

As pdreyn-ga points out in the Comments below, if you require using
that much data, you may want to look into database alternatives, such
as Microsoft Access Lotus Approach, or FileMaker Pro


Additional information:

Jan's Excel Intro: Excel Interface
http://www.jegsworks.com/Lessons/numbers/intro/interface.htm

"Is There A Limit On Excel 97?" Webboard Posting
http://www.tek-tips.com/gviewthread.cfm/lev2/3/lev3/18/pid/68/qid/222000

Microsoft Knowledge Base: Importing Text Files Larger Than 16384 Rows
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q120596

Ebay: Databases
http://listings.ebay.com/aw/plistings/list/all/category3773/index.html


Search terms used:

excel 65,536 rows
://www.google.com/search?q=excel+65%2C536+rows
Comments  
Subject: Re: microsoft excel
From: pdreyn-ga on 26 Apr 2002 11:40 PDT
 
you don't, that's the limit.  If you have that much data, you should
really be using a database application like MS Access.
Subject: Re: microsoft excel
From: kudut-ga on 26 Apr 2002 12:26 PDT
 
You can use Excel's VBA to process more than 65,535 rows, if the
original source is a text file or database.

I've had to do that here at work, where a text file came by that was
exported from a database, but the calculations required to be done on
that data were beyond the capabilities of Access.  So, I put together
an quickie import macro which imported about 30,000 rows per sheet,
did the appropriate calculations, then exported it back out again as a
text file with the calculated values.

The benefit of Excel is its incredible flexibility.  But if your needs
are relatively simple - joining tables, etc - then Access will work.

It's too bad, imho, that Excel does not go beyond 65,535 rows -- plus
which it never gets respect among IT.  It gets respect from the
Business users, especially analysts and creative people who look for
new ways to mine data.

So, yes - there are ways to get around the 65535 row limit - but
they're not obvious ways.

One thing you can do, if it's a text file that is the source, is find
a freeware file-splitter in which you can specify how many lines to
split up.  So, for example, you can have it do "every 30000 lines,
split" - then import each of the resulting files into Excel.  When
you're done with your calculations, save each sheet as a text file,
and join them together again elsewhere.
Subject: Re: microsoft excel
From: biggles-ga on 26 Apr 2002 18:47 PDT
 
You can work with an external database from within Excel.  You will
find that performance is better because the entire data set does not
have to be loaded into memory.  I recommend a copy of the excellent

Mastering™ Excel 97 
By Thomas Chester and Richard H. Alden 
   see http://www.sybex.com/sybexbooks.nsf/9c3e1a82d846780c882569730064cc58/7c033a68bb6cc2df8825693d0057f3ff!OpenDocument

for details, or, if you are using Office 2000, 

Mastering™ Excel 2000 Premium Edition 
By Mindy C. Martin; Steven M. Hansen; Beth Klingher 

http://www.sybex.com/sybexbooks.nsf/9c3e1a82d846780c882569730064cc58/22a7cb419e97fa7a8825693d0057f8e3!OpenDocument

They are highly recommended for novices and experts alike!

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy