CODEWITHSUNDEEP
excel
Alex Poca
Alex Poca

Reputation: 2566

Excel: Calculation trace

I need to analyse an Excel365 sheet to port its formulas. The task is quite time expensive and error prone: for each cell I ask for the dependencies (by means of [F2] or Trace Precedents) and recurse manually the calculation tree.

In this example there are 4 cells with formulas and a fixed value. I assume Excel follow this logical order in order to solve them:

enter image description here

In this case I would like to get a (debug) trace of the whole calculation process, as in

enter image description here

or, even better, as a tree graph where all cells dependencies are represented graphically.

Is anything like this available?

Upvotes: 1

Views: 228

Answers (1)

Charles Williams
Charles Williams

Reputation: 23550

Excel uses a dynamic multi-threaded process to determine the calculation sequence: it may change the sequence at every re-calculation as it tries to optimize the sequence.
There is a CalcChain part in the file xml that can give you the last sequence used (but it needs some decoding).
Also note that Excel's Trace Precedents dependents tools do not resolve functions like INDIRECT and OFFSET.

If you just want to view the precedents/dependents I recommend Jan-Karel Pieterse's RefTree Analyser: it has graphic representations of the Tree

https://jkp-ads.com/RefTreeAnalyser.asp

Upvotes: 1

Related Questions