Excel File Optimization - lots of problems

[ashutoshsethi]

Hi,

I am interning at a mutual funds division of a large firm and working on a revenue model. The model is already developed and is huge. I need to optimize it in terms of speed (opening, saving, closing, calculating) and efficiency (better formulas may be?) overall.

Problems are: it is very slow. It has more than 42 worksheets and is 23 MB right now. It links to lot of other excel files on my drive but not to the drives in the networked hard drives.

It uses 'vlookup', 'sumif', and 'sum' a lot. All worksheets are Linked to all others through formulas.

Macros are just in one sheet and are for inserting buttons in one sheet that link to other tabs in the workbook.
Any advice on improving formulas or speed would b great. Also, is there an option where I can split the excel file and keep the important tabs in one sheet..would that be faster given that I still would have to link them to the main sheet with lesser tabs may be...?

Thanks,
Ashu

[ARGK]

Hi
The speed of the calculations is not necessarily down to the number of formulae that you have in the workbook. It may be down to the power of the machine you are processing the Workbook on. In addition without seeing and "fully" understanding what the workbook is doing it would be extremely difficult to offer any constructive advice to you other than looking at the power of the machine as mentioned above.
Tony

[Pete_UK]

Take a look at Charles Williams' site here:

http://www.decisionmodels.com/calcsecretsc.htm

There are lots of examples on how you can improve things.

Hope this helps.

Pete

[ashutoshsethi]

Tony and Pete,

Thanks.

To shed more light, the excel file is basically keeping track of mutual funds (over 50 types) and their number of subcategories. For example, we fetch actual data on funds' performance, funds income, assets under management under each, and more than other 20 such attributes. My excel sheet gets data from these other excel sheets and just reports everything together so that we can make projections of revenue and income over teh next 4-5 years.

I use a windows XP, pentium R, dual core (not core two duo) 2002 version, 4gb ram, 32 bit machine. To give u scale, total 'sumif' formulas in the workbook are 113000, yes that's correct..more than 100, 000! similarly vlookups are 53000...

Let me know what more information u need and I ll be able to give out easily.

Thanks,
Ashu

[newdoverman]

It sounds like you are trying to move a mountain with a spoon. The equipment and software are old. Upgrade then sort out the Excel problems.

[ARGK]

I have to agree with newdoverman above. You need to sort the cause out before you look at the symptoms.
Good luck.
Tony

[ashutoshsethi]

Hi,

As per your suggestion, I moved the model to a much newer machine (had done this before too, just did it again). WIndows 7, 8gb ram, and all the i5 processor and other modern amenities on a machine.

The problem still persists.

I have already switched to manual mode of calculation, removed all macros (they were few, that were just for switching between tabs, and also tried to save as a binary file (binary makes it thrice faster, but I am worried, if the file turns even more large given we are adding stuff to it, would binary be a hindrance to addition of macros or even more vlookups...?)

The major problem comes in opening and saving. Only some vlookups are looking up data gathered by other vlookups. In short my questions would be:

1) How efficient would be the binary file format (have been reading on various forums about this, but still need to 100 percent sure before I recommend that to my department)
2) Can we safely call vlookups as culprit as they are more than 50000
3) is splitting the file, lets say 10 tabs in each, be more efficient than one excel with 42 tabs? is it known to make a difference? assuming I dont change anything else on the formula side, and once I split it, I just reference the new excel sheet here. This is labor intensive as well, as this seems humongous task as well. SO before venturing in this, i need to be sure, if this is the right thought process.

Your help is much appreciated.

Thanks

[Kyle123]

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[ashutoshsethi]

I am so sorry, I did not know that!

Am pretty new here so did not realize that.

Ashu

[newdoverman]

The information in this video might help you.

http://www.youtube.com/watch?v=HKbRNqcvhzs

You might want to consider changing the Excel workbook over to a relational database. Databases can hold far more data and access it more quickly and efficiently. Opening and saving are also quicker because in opening, you only open the Introductory screens whereas in Excel, you are opening the whole file. On saving, the database saves the tables that have been changed and any changes/additions to the relationships between the tables. Excel on the other hand has all the data open and has much more "housekeeping" to do in order to save the file.