Is there a limit on Excel97?

[chifu]

I have a user with an Excel 97 Spreadsheet that is about 4 columns from the IV column, but only about 400 rows deep.

There are no more columns after IV and does not seam to allow any more to be created. This user is finding that this spreadsheet sometimes does not open and shuts itself down automatically.

Is this the limit of Excel 97 and is there a way around it?

Thanks

 

[mbarron]

Yes, it is the limit.

From the Excel help file:
Program workspace specifications

Feature Specification
Maximum number of custom toolbars in a workbook Limited by available memory
Maximum number of custom toolbar buttons Limited by available memory
Maximum number of open workbooks Limited by available memory and system resources
Maximum worksheet size 65,536 rows by 256 columns
Column width 0 (zero) to 255 characters
Row height 0 to 409 points
Maximum length of cell contents (text) 32,000 characters
Maximum length of formula contents 1,024 characters
Maximum number of sheets in a workbook Limited by available memory (default number of sheets is 3; the maximum number of sheets in a default workbook is 255) Mike

 

[Zebadiah]

Also, in re: size of files, my experienced limit is around 120mb before excel has problems with the files, at 140 mb, I've been able to create this size but can never reopen the file. (Dangers of running macros that post lots and lots of data into what starts as empty cells)

 

[DaleWatson123321]

chifu,

In addition to checking out the amount of MEMORY on your user's PC, I would suggest you also check out the following possibility...

Some spreadsheet users do not appreciate that spreadsheets can (and often SHOULD) be set up as a type of "database".

A typical "database", for example, would have field names like: Date, SalesPerson, Quantity, Price, etc, where the data is entered "vertically".

The "newbie user", on the other hand, enters data "horizontally", with an additional "block of field names" for each "time period". For example, if the user wants a "Weekly" or "Monthly" report, then they would repeat the "block of field names" for EACH Week or for EACH Month. Such a practice is not only "inefficient", but as in this case (possibly the case), the user soon realizes that there are only 256 columns. Excel has, on the other hand, 65,536 rows.

More importantly, however, there are "built-in" "database functions" in Excel. They aren't anywhere nearly as powerful as those of Lotus 123. However, they are still powerful, and should be used.

There are "Data - Filter" options, but also "Data - Filter - Advanced Filter" options. It is the "Advanced" Filter options that allow the user to "extract" data from the one (database) sheet to another sheet where "selective" data is captured to produce the various reports required.

These various reports can be for a specific period, by specifying "criteria" which specify the "start date" and "end date" of the records required for the report.

Similarly, reports can be generated to include all the sales for a particular salesperson, etc.

An alternative to producing reports that contain all the records, is to produce a report with just the "totals" - i.e. a "matrix" of totals by salesperson by month, for example. This is accomplished with "database formulas".

Another option is "Pivot Tables".

If you feel that your user has gone off on "that other direction", and could benefit from re-shaping the file into a "proper" database type of spreadsheet, then please feel free to ask me for an example file or two. Just email me, and I'll return the file(s) via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[coachdan]

It sounds to me that DaleWatson is right on target. Your friend sounds like he/she is using Excel as a database. They should consider using a database tool for this. MS Access is relatively easy to learn and you can handle more data. If he/she wants to perform calculations in Excel, they can query the results to a smaller subset and export or link it to Excel to perform the calculations.