Calculation Timer for excel 2016?

[lynnsong986]

Hello, I have a workbook that takes a long time to open. I'm trying to figure out what takes most of the time to calculate. Does anyone have a sub that works in excel 2016? I found one online but it didn't work (it says it works for 2010).

I can't even paste it in here as I got a message saying access denied due to SQL something something (sorry I can't remember)...
thanks!

[Pepe Le Mokko]

Welcome to the forum: Apply the fallowing to attach a file

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[lynnsong986]

https://docs.microsoft.com/en-us/off...on-performance

I found this link and will give it to try and report back.

thanks!

[lynnsong986]

I followed the instructions and am able to use the timer now. but what I don't understand is that the workbook takes like 10 secs even just to open (calculation mode is set to manual), does anyone know how can this be? why it is so slow even without calculation? the file is too big to upload though I'd love to!

thanks!

[CK76]

There could be multiple reasons for that.

1. If it contains PivotTable and if you inadvertently duplicated PivotCache(s)

2. You have unnecessary formatting applied to area outside of data range. You can check this by hitting CTRL + END. If the selected cell is to right and below data range. You have formats in empty range. Select all the range outside of your data range, and use Clear All.

3. You have multiple conditional format, or have large range with conditional format applied. CF is reevaluated each time display area changes, and can slowdown workbook performance.

There are other causes, but bit hard to help without the file unfortunately.

[lynnsong986]

thanks I think it must be the pivotcache(s) as I have tons of them in the workbook, unfortunately the source data is all different. I'll keep digging and hopefully I can make it run faster. thanks so much for your help!