+ Reply to Thread
Results 1 to 7 of 7

Reduce File Size

  1. #1
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    263

    Reduce File Size

    Hi am building file for our company to analyze the amount our clients customers spend over 3 months, all within certain distances of their store. The data set I use as a baseline (client ID, radius from store, trips per month and avg. spend per month) are copied and pasted into 12 columns of an excel file (with up to 300,000 rows/records, which feeds into formulas I have written to analyze this info on various levels. Ultimately all the data flows into a predictive pro forma we created that is unique to the work we do. The worksheet has grown as our interest in looking at the data from different angles has grown. The problem is the size of the file is now so large it take a great deal of time to open, and worse, to recalculate each time I either cut or copy and paste data from one area to another. I am hoping to find ways to shrink the file from its current 80MB size so wanted to turn to Excel Forum for advice/tips.

    A few things to note:
    I have noticed the system crashes about 3 times a day for no apparent reason. I assume the calculations freezes the computer brain. I am using Windows 2011 for Mac.
    I am not using many Named ranges, which perhaps I should do if it helps streamline the process.
    When I copy and past the initial set of data, which could include up to 300,000 rows (12 columns), I have formulas that run down parallel columns performing calculations of the data. Most of the data only covers 100,000 records, but I have the formulas run up to the allotted 300,000 rows to make sure I do not paste outside of the last row, thus messing up my calculations.
    I used to have the analysis broken up into several different worksheets, but decided to combine them to minimize the chance for broken links as well as to more easily manage updates or bug fixes. The file is ultimately sent to the sales reps who use the results (pro forma tab) in their presentations.

    Thanks in advance.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Reduce File Size

    If you haven't done this yet you should do it.

    Save your workbook using 'Save As' and choose Excel Binary File (*xlsb) format. To learn more about this very useful format read this:

    http://blog.datasafexl.com/excel-art...binary-format/
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,341

    Re: Reduce File Size

    Some other things to check...
    1. hit end/home and see where you end up. If it is "out in the middle of nowhere", start deleting rows/columns until you get to where you know (for certain) your last row/column of data is.
    2. check to see if you have any ARRAY formula that may be using entire rows/columns - change these to cover just the ranges you need
    3. If you are using ARRAY formulas, consider adding helper columns, that may allow you to use regular formulas instead
    4. consider using those same helper columns to reduce the complexity of any other big formulas you have
    5. Check to see if you use volatile functions (like TODAY/NOW) in your formulas - if so, consider putting them in their own cell and referencing them. Volatile formulas calc with every WB change, so will slow your file down eventually. here is a link to see more about them...
    http://www.decisionmodels.com/calcsecretsi.htm

    Once you have done those, save, close and reopen - check to see if it made any difference
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    263

    Re: Reduce File Size

    Thank you. This cut the file in half. I have to read up a bit more on this file type as I see one of the drawbacks is that no ribbon modification are allowed in xlsb. I am not sure I am clear on this. For example are certain ribbon functions disabled?

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Reduce File Size

    Quote Originally Posted by Perk1961 View Post
    Thank you. This cut the file in half. I have to read up a bit more on this file type as I see one of the drawbacks is that no ribbon modification are allowed in xlsb. I am not sure I am clear on this. For example are certain ribbon functions disabled?
    No, Nothing is disabled. It just means that you can't make modifications to the ribbon while using xlsb format. To read more about ribbon modifications follow the link below.

    https://support.office.com/en-us/art...3-68a254a80ea6

  6. #6
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    263

    Re: Reduce File Size

    Thank you. The tip of removing rows that are way outside the dataset used for the calculations did help a bit, but only reduced by about 4MB.
    I am not using any ARRAY formulas.

    Every tip helps so thank you!

  7. #7
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    263

    Re: Reduce File Size

    Got it. Thanks for clarifying.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Reduce File Size
    By hasanpmp in forum Excel General
    Replies: 3
    Last Post: 05-15-2014, 05:31 AM
  2. Replies: 1
    Last Post: 12-17-2013, 03:18 PM
  3. Replies: 8
    Last Post: 11-28-2012, 02:33 AM
  4. Help me reduce 30 mb file size
    By ZenButcher in forum Excel General
    Replies: 6
    Last Post: 11-06-2009, 03:34 AM
  5. Reduce File Size
    By tbaker897 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2008, 08:24 AM
  6. Need help to reduce file size
    By iturnrocks in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-11-2007, 06:17 AM
  7. [SOLVED] Reduce size file
    By tempêtje in forum Excel General
    Replies: 2
    Last Post: 03-29-2005, 08:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1