• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

File Size and Time it takes to open file

J

J Clark

New Member
I have been using excel for several years to do financial reporting and projections. The file sizes that I have worked with are very large (up to 900mb). The current file that we are having issues with is ~500mb. It used to open in 5 minutes, but suddenly has started to take 45 minutes to open. This is without adding any new formulas or material file size changes. We tried converting it to a .xlsb and that shave the file size down to ~380mb, but still took 45 minutes to open. There are approximately 600 tabs in the workbook (1 for each of our locations), and then a summary worksheet that identifies each location with the office name in the summary being a hyperlink to that respective office's worksheet. Once this workbook is open, the only thing that takes much time to do is to insert a row on the summary worksheet (to add a new location in whatever line we prefer -in between other existing locations). Other than that action, it is very functional.

Is there anything that anyone can think of that would trigger this change in the time it takes to open?

thanks
 
Yikes, those are some large files! :eek:
Are there perhaps a lot of volatile functions? INDIRECT, TODAY, OFFSET, NOW are all volatile and get recalculated at every change. INDEX is semi-volatile, in that it gets recalculated at every open.

On a side note, I'd recommend moving to a better database tool such as Access. Wouldn't take nearly as much file size, and would give you better options for handling the size of data that you are having to deal with.
 
We don't currently have those exact funtions in there, but do have VLOOKUP and IF funtions (I don't think i used SUMIF functions in this file as I read in the past that it could create delays as you mention above for the others).

As far as moving to a different tool - we are beginning to look at budgeting/modeling tools. I have utilized Access for storing data, but am not as comfortable in Access as I am in Excel when it comes to doing calculations. Usually what I do is bring the data back out of Access into a 'warehouse' in Excel and then do VLOOKUPS and SUMIFS to get the data by office in Excel, like what I described above (then break the links/formulas). From that point I can do my calculations, such as Same Store Sales measuring the increase/decrease in revenue for all stores open the same months in both years (ex: for May a store must have been open in May 2013 and May 2014 to qualify for the calculation). I am not sure how to do this in Access.

Would welcome further input on this from you and/or others as well.

thanks
 
You must log in or register to reply here.
Back
Top