Workbook level calculation
We need the ability to calculate a single workbook when multiple workbooks are open.
When working with multiple large slow workbooks which are often not even linked together (think multiple versions of the same workbook or a large mostly static workbook being referenced by a smaller rapidly changing workbook) you want to make changes and recalculate without globally recalculating all open workbooks.
This should be implemented as
a) New calculation setting (persistent) - Calculate Active Workbook Only - works for Automatic and Manual F9, but Ctrl/Alt/F9 would full calculate all open workbooks regardless of the setting.
Automatic Calc in Active workbook only would recalc the active workbook whenever the active workbook changed.
b) VBA Workbook.Calculate would only calculate the referenced workbook.
This will also become a critical need when handling multiple workbooks on non-workstation endpoints
Thanks for the post Charles, and for the in person conversation at the latest MVP conference. We definitely understand the ask and will keep our eye on it – it looks like it’s moving up the vote charts pretty quickly. As always, keep upvoting the things you are about most.
John [MS XL]
Oh my, I think I've just found out why my spreadsheets sometimes run so slow. Auto calc in active workbook makes sense to me. Auto calc in all open workbooks? It is a no-brainer and needs to be fixed.
Markus Radtke commented
Please see below suggestion related to this issue. I believe deactivating calculation on heavy workbooks instead of just calculating the currently active workbook may be a more effective way to allocate calculation power efficiently. At least this should be part of the solution. Voting link below. Hope this helps to refine the discussion. Thanks.
Allow for a transparent Manual vs. Automatic Calculation setting by workbook vs. a global setting.
When you have one calculation-heavy workbook and distribute it to other people, it would be best if you could turn off calculation for only that workbook, without affecting the other workbooks these users are working on. It is confusing and outright dangerous in potential consequences that suddenly their workbooks do not calculate anymore. On the other hand, if the calculation is on, their whole excel freezes for a considerable amount of time every few keystrokes.
Suggestion: Allow for a transparent Manual vs. Automatic Calculation setting by workbook vs. a global setting.
There should be a popup when you open the workbook if the calculation has been turned off by the previous user of the document, giving you the choice to accept this (applicable only to the workbook) or activate the calculation again.
This would be very, very, very useful.
Mike Gledhill commented
I couldn't agree more.
It's ridiculous that if you turn on/off Automatic Calculation, or call the Calculate function (either from VBA, or using C# code with Visual Tools for Office) it recalculates *all* open Workbooks.
So, Microsoft gives us the ability to call Calculate on a specific Worksheet, or across *all* Workbooks... but we can't just Calculate just on the active Workbook.
Why hasn't been this been fixed already ?!
Patrick Brunck commented
@Paul: Dear Paul, I also support this great idea with a few of my votes. Unfortunately, I think it will take some time until we actually see this enhancement in Microsoft Excel. Happily, there might already be some possible workarounds you could use for your personal scenario. If you would like to save 20+ minutes every day, you are welcome to contact me over the contact form of my website: http://www.patrick-brunck.de/en/contact.html
Unfortunately, the main part of my website is still just in German, but the contact form is available in English as well. I look forward to hear from you. Best regards, Patrick
@All others: Sorry, I know this is really off-topic.
This is a critical functionality that needs to be added back into Excel 2016 (as well as future releases). Every morning I begin by filtering a single column to show duplicate values only for > 130,000 rows. Even though this is only one column wide, it takes 20 minutes to process. While this processes, I am unable to work in other instances of Excel - they are all busy processing the function for the non-active workbook. 20+ minutes of down-time every morning is not productive in business when there are deadlines to meet.
Patricia McCarthy commented
Yes- I think that this would be very useful and save a bit of time.
YES!! this would be a big help!!
Charles Williams commented
Technical implementation suggestions:
Either 1) process the global calc chain skipping everything that is not contained in the active workbook, but dirty all references from other workbooks to the active workbook.
(from VBA workbook.calculate works similarly but with respect to the referenced workbook)
Or 2) rebuild a subset of the global calc chain that relates to only the active workbook and references to the activeworkbook whenever the active workbook is changed
Alternative 1 is probably simpler to implement but still carries the overhead of traversing the entire chain at each recalc.
Alternative 2 is probably more complex to implement and is slower for the first recalc ( and for VBA workbook.Calculate) but faster for subsequent recalcs on the same workbook.