Workbook performance and bottleneck analysis

[tatoon]

Hi,

I have inherited an Excel workbook which is quite large and performance is quite poor.
So far I have calculations set to manual, but I need to recalculate formulas very often.

I know there are many tips on how to optimize worksheets (use this or that formula, avoid this or that, etc). I don't really have time to analyze and optimize the entire workbook, that would be a work project on its own.
But I would like to identify some quick wins.

In the past I used a VBA code that went through the entire workbook testing each cell and gave you a report of which cells took the longest to run.
This was very useful, but I have lost this code (it was in my previous work laptop, mistake not to backup that code.

Are you aware of any similar VBA code or free tool to analyze workbooks and give some kind of report with the main bottlenecks?

Thanks in advance.

[tim201110]

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

[Zer0Cool]

Excel 2016 has an addin built in called Inquire. It has an option called Workbook analysis which can give a report about the content in the file (no calculated results, just whats where which can shed light on performance). If you are able to post a sanitized sample we could check it out for you.

Also, not to sound condescending, but you will get back what you put into cleaning up the file. I can understand if you really dont have the time to work it out, but I wouldnt expect miracles then either.

What extension is the file? (xlsx, xlsm, xls, etc)

The first thing I do when evaluating files is consider the amount of actual content in the file vs the file size. This can be an indicator of excess content bogging the file down. For example a 10MB file with 10 sheets, 250,000 rows per sheet...probably a reasonable size. On the other hand a 10MB file with 10 sheets and 250 rows per sheet....something is wrong (and can likely be addressed by checking a couple of things).

Signs of excess content:

• Scroll bars take you way beyond your data to blank areas of a sheet (excess content)
• Home | Styles drop down has many many styles, some duplicated
• With the Home | Find & Select | Selection Pane open switch to each sheet. See a ton of entries in the pane? May have extra objects that can be deleted
• Formulas | Name Manager. See more than a couple dozen entries...you have excess named ranges.

Another way to check if a specific aspect of the file is too large (works for xlsx and xlsm only!). Create a copy of the file. Rename it and change the file extension to .zip. Open the .zip version and navigate to the xl folder. You should have a column for compressed size and one for size. Both matter, so use either. Check the size of:

calcChain.xml
styles.xml
workbook.xml
worksheet/ (all .xml in here, one for each sheet)
all other folders except _rels

So lets say for example you have 10 sheets, and they are all basically the same...however when you check the worksheets folder you see that sheet3.xml is like 10x larger than the rest. That means sheet3.xml (not to be confused with the 3rd sheet in your file or a sheet named "sheet3" in the file) is likely has excess content in it.

So if you cant post a sample for us to review, I can guide you through some steps to identify possible issues within the file.

Also, and I will add the disclaimer that this isnt a fix and comes with its own issues, you could consider saving the file as an .xlsb. It should reduce the file size a bit and speed up calculation in larger files. If there is an underlying issue and you do not correct it, having the file be an xlsb will make it harder to recover from issues down the road.