Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How big is BIG spreadsheet? (Excel 97-SR2)

    Hi Folks

    My latest 'masterpiece' is approaching 5Mb in size. Aside from the practicalities of e-mailing this stuff around, any observations about sensible file sizes?

    I'm not noticing any performance issues, just the size creeping up!

    Regards
    Peter

  2. #2
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How big is BIG spreadsheet? (Excel 97-SR2)

    Peter,
    Isn't this kind of a subjective question? I mean -- as long as Excel can handle it -- it isn't too big (theoritically speaking). However, what is "too big" would depend on what you are doing with this file. If you aren't sharing it around, or hoping it will load fast and you're not low on disk space, I don't see a problem with allowing it to go big. Depends on the system you are running (what it can handle) and what you plan to do with the file.
    Tracy

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How big is BIG spreadsheet? ADDENDUM

    Or perhaps I could have better stated it the way I used to tell my students in class when I taught Excel -- you are going to be more limited by the resources of your personal machine (RAM, etc). than you will ever be limited by the Excel program itself

    FROM THE EXCEL HELP:
    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
    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

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How big is BIG spreadsheet? ADDENDUM

    A very subjective question yes. As I said, I'm not seeing any performance issues. I guess what I was hoping was not to be overrun by folks who have never seen a spreadsheet larger than 50K. (Said very tongue in cheek, ok.). All looks quiet on the western front. I'll keep coding.

    Regards
    Peter

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How big is BIG spreadsheet? ADDENDUM

    Peter,

    It depends on what you have in your spreadsheet. If you're not constantly adding stuff- only recalculating etc- I'd be suspecting some sort of bloat. I have had intermittent corruption on spreadsheets like that.

    Sometimes VBA can be the source of the bloat.

    It might be worth while to make a couple of experiments.

    1. Export the worksheets to a new empty workbook. Use the organiser to copy any macros. Save the new worksheet and check its size.

    2. This one won't work for you in XL97. If you had access to XL2000, save thw workbook as HTML; close Excel, open the HTML copy, and save back as Excel. (In 97 you get a very cut-down HTML view)

    In any case, make sure you are taking backup copies regularly. It doesn't feel nice to lose a lot of work because on an Excel feature!
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How big is BIG spreadsheet? ADDENDUM

    Hi Geoff

    Many thanks... No VBA in this one. Backups... I'm on version 1.4 of my model, started at 0.1. Copies on my other hard disk and e-mailed to an office server...

    I will try exporting the sheets to a new worksheet, just in case. I'm sure the model is fine, more curious about how large peoples spreadsheets get. I've had a few 2Mb models, but this one is turning into something of a monster! Mind you, it's also the most complex (hopefully therefore smart) I've ever written.

    Thanks for the advice.

    Regards
    Peter

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: How big is BIG spreadsheet? ADDENDUM

    Peter, I would guage the issue by recalc time rather than pure size, though like you, our WAN has a 5MB limit on attachments, so monster spreadsheets have to be compressed. Geoff has covered some excellent ideas to see if the bloat is remediable. Some other things to think about are:

    If you are using massive amounts of source data, there may not be much you can do, short of dumping the data into a simple Access database and analyzing or at least drilling it partway down it within Access

    Think about the workbook design and any redundancies you may have introduced. Steve <!profile=sdckapr>sdckapr<!/profile> has argued that with repetitive data one can do everything on one sheet and pivot table it to death; and that is a very efficent way to handle some analyses. If you are substantially reusing data or reusing formulas, you have redundancy. Use Data, Tables and Scenario Manager when applicable.

    If you use data to present to people you'll need to boil it down to "data bites" they can understand and digest; if immense complexity isn't needed to derive and present conclusions, then don't build unnecessary complexity. (I'm not in the finance industry, and our corporate planning timeframes are so short we rarely use inflators or discounted cash flows; I'm the only person in the company who occasionally throws them in. I don't know if that's good or bad!)

    In my work 6 MB WBs containing a lot of data are not unusual, though most of my files are at the
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How big is BIG spreadsheet? ADDENDUM

    Most spreadsheet I work with are in the 2MB to 4MB Range.
    I've worked with spreadsheets in the 20MB range.
    They had 15 worksheets, Columns went from A to FZ
    and about 1,000 rows each sheet.
    On my P-300 it takes about 50seconds to open it from my local drive.
    On the local drive its OK but I wouldn't want to be pushing this around a network enviro. though.

    Later,
    bruce

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How big is BIG spreadsheet? ADDENDUM

    Hi Folks

    Well... 2 seconds to load and no visible delay in calculating if I change report criteria. I guess other than transferring files around, it's not too much of an issue yet. I can Zip it to under 500K.

    Regards
    Peter

Posting Permissions

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