Excel File Size Limit

Polonious

Does anyone know if there is a size limit on an excel file. A colleague has managed to build and excel file up to 164mb and now it won't allow him to open it anymore. Can't say i'm suprised really, but does anyone have any ideas?? Error that pops up says something about not enough available resources to open the file. It is a pretty beefy PC he has and it has 1gb of ram so i doubt it's the PC....???

KdjaCL

Check temp dir maybe an older version of it in there before it went to high size.

The memory thing is a Excel thing maybe a patch for it it only uses a certain amount of memory ,tru regedit may be able to increase it.

kdjac

Polonious

They actually have an older version that can open. Problem is I know that it's going to get big again, so i just need to know how big microsoft are willing to let us go!!!

Stephen

OpenOffice seems remarkably good at opening buggered up excel files.

anykaxopom

If I were you I would split this file into more manageable pieces. Even if you get Excel to open your mammoth of a file, it is not healthy.

Did you think of saving the data as a text file? Or do you need to access any of the data at any time?

Polonious

It need to be all in one file unfortunately. It's for running a report on a weekly basis and each week a few hundred lines are added... That the problem! I've tried OpenOffice, but it's not had any luck either. My need to call Bill on this one!

ColmOT [MSFT]

It sounds like this is more like a DB file than an 'spreadsheet'

Have you considered using Access (or SQL Server)?

Polonious

Have definitely thought about it, it's just finiding the time to implement it properly. There are about 15 worksheets with thousands of lines in each, so it would be a job and a half making sure it all transferred over properly to access or sql. If i could find out what MS view on whats a max size for an excel file was then i could get some closure on this and tell the user that he just needs to reduce the amount of data in it. Need hard facts though, you know how it is!!!

ColmOT [MSFT]

I've mailed a contact in the Excel team, so I'm waiting for a reply on the max file size.

I'll keep u posted!

Polonious

Nice one, thanks mate.

sceptre

Pulled this from Excel 2000 info:

Open workbooks Limited by available memory and system resources
Worksheet size 65,536 rows by 256 columns
Column width 255 characters
Row height 409 points
Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook 56
Cell styles in a workbook 4,000
Named views in a workbook Limited by available memory
Custom number formats Limited by available memory
Names in a workbook Limited by available memory
Windows in a workbook Limited only by system resources
Panes in a window 4
Linked sheets Limited by available memory
Scenarios Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 3 in a single sort; unlimited when using sequential sorts
Undo levels 16
Fields in a data form 32
Custom toolbars in a workbook Limited by available memory
Custom toolbar buttons Limited by available memory


Return to top

Calculation specifications
Feature Maximum limit
Number precision 15 digits
Largest allowed positive number 9.99999999999999E307
Smallest allowed negative number -9.99999999999999E307
Smallest allowed positive number 1E-307
Largest allowed negative number -1E-307
Length of formula contents 1,024 characters
Iterations 32,767
Worksheet arrays Limited by available memory. Also, arrays cannot refer to entire columns. For example, an array cannot refer to the entire column C:C or to the range C1:C65536. However, an array can refer to the range C1:D65535 because the range is one row short of the maximum worksheet size and does not include the entire C or D column.
Selected ranges 2,048
Arguments in a function 30
Nested levels of functions 7
Number of available worksheet functions 329
Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered 9999:99:99


Return to top

PivotTable report specifications
Feature Maximum limit
PivotTable reports on a sheet Limited by available memory
Items in a PivotTable report 8,000
Row or column fields in a PivotTable report Limited by available memory
Page fields in a PivotTable report 256 (may be limited by available memory)
Data fields in a PivotTable report 256
Calculated item formulas in a PivotTable report Limited by available memory

Because the number of sheets allowed is limited by available memory, the workbook size appears to be theoretically unlimited. Which isn't what you wanted to hear. Colm may come back with a different answer (and seeing as he's going to be asking someone who knows, his aswer is automatically better:))

However this 164MB Excel file with worksheets probably coming out of its rear is plain nuts. Every time you hit 65000 rows you'll be adding a new worksheet. Excel's turned into a pretty damn good app (especially since that disc changing thing was solved after Excel 4, it bugged the hell out of me at the time) but it was never designed to do what your boss is using it to do. Even dumping it into Access as a flat database for a start wouldn't be too bad of a job and I think you'd be foolhardy not to.

Polonious

Wow, good stuff sceptre, thanks. It's just gotten out of hand. From your list i can see several limitations that probably have been exceeded by the file. Will need to go check it out now. Thanks.