Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Maximum file size (2002/SP3)

    Hi all,

    I am wondering what the maximum workable file size is for Excel. I have a file with a lot of lines which I use for source data in an pivot table. There are aprox. 40-45.000 lines in the file at the end of the year. Last year the file became to big so I switched to Acces. This year I already started with Access but after an update i have to go back to Excel because they are not giving everybody Access.

    To prevent the same problem for the end of the year I want to know if with a faster computer i could end up with the same problem. My computer is a Pentium 2.8Ghz 256Mb.

    Regards Marcel <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Maximum file size (2002/SP3)

    An Excel 2002 sheet can have a maximum of 65536 rows. As far as I know, this limitation still holds in Excel 2003; I don't know if it will be changed in the version of Excel after that. If you can divide your data into logical pieces, you can use a pivot table based on multiple consolidation ranges; this is a way to work around the 65536 row limitation. However, recalculating a pivot table with tens of thousands of rows or more will be slow. How slow depends on the complexity of the data (how many columns, what kind of calculations.) A faster processor will help, of course, and more memory will probably help too (more RAM memory means less swapping to disk.)

    Note: It is possible to work with a pivot table based on data in an Access database even if you don't have Access installed, you only need MS Query and the appropriate ODBC driver to connect to the database.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Maximum file size (2002/SP3)

    Hi Hans,

    I know about these limitations but last year at about october or november the file could not be opened anymore, so the pivot table was no question about it. The size was huge I am not sure now but it could have been 20 MB or more. The data in the file was from a download from a AS400 based accounting program.

    I know you can link a acces file to excel through MS query but that is no options since i add information on a monthly bases and have to evaluate the records before i make the pivot table. But thanks for the idea anyway.

    Marcel <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximum file size (2002/SP3)

    A 20MB Excel file is not huge. I regularly work with files 10-40GB in size. Excel is well known for self-destruction so you may want to periodically copy all your data to a new workbook. If you find you can no longer open the file, it's somehow got corrupted. There are many posts about this topic in The Lounge. It is a real pain to have to re-create a file but the more the same file is messed with (adding, deleting data, especially VBA code) the more likely it'll self-destruct. Excel isn't the most stable application around.

    Also you may have lots of unused rows that never get cleared (they look empty but Excel still sees them as having data). If you do Cntrl+End on each sheet does it go past your visible data?

    Deb

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximum file size (2002/SP3)

    Hans
    I had a case with a requirement for more than 64K rows.
    I broke it into a 50K and 20K row pair of sheets
    When I went to build a pivot table of both ranges - it locked, at I presume, the point at which it hit a total of 64K.
    That is, while a pivot table will consolidate multiple ranges it still seems to only be using variables that will hold 64K.
    DO you have experience of more? if you do I'd love to give it another try as Access really isn't the right answer for my current needs.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Maximum file size (2002/SP3)

    One of the limitations of pivot tables, whether the data live in Excel or elsewhere, is a maximum of 8,000 unique items per field; I suppose you ran into that. Although I never work with such huge data tables in Excel itself, I just tested it; I created a pivot table based on 4 sheets with 40,000 records (rows) each, but with a limited number of unique items, without problems.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximum file size (2002/SP3)

    Hans
    Thanks for the reply - was that 8000 limit the fields I have in the pivot table itself, or merely anywhere in the data's non-selected columns?
    (There may be hope for it yet <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Maximum file size (2002/SP3)

    I don't know. I hope that someone else can help.

    (I just noiticed that Excel 2002 will handle a row field with more than 8,000 distinct items, although the online help mentions that limit.)

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Maximum file size (2002/SP3)

    In XL97 (I won't speak of the others since I don't use them) if you have a field with more than 8000 unique items, You will "choke" the wizard and it will give you an error. It does not cause a problem if it is used in other fields. It is just an issue with making more than 8000 row items (you are already limited to 256 column items by spreadsheet design)

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maximum file size (2002/SP3)

    Thanks Steve & Hans - I'll try it all again (but perhaps with a little less data <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •