Basic Examples (3) 

Download an XLSX file:

In[1]:=

Trace the dependencies of the cell D1:

In[2]:=

Out[2]=

Visualize it as an ExpressionTree:

In[3]:=

Out[3]=

Scope (4) 

Trace a deeper spreadsheet dependency:

In[4]:=

In[5]:=

Out[5]=

Trace formulas with absolute references:

In[6]:=

In[7]:=

Out[7]=

Trace cross-sheet references:

In[8]:=

In[9]:=

Out[9]=

Trace through a column range, expanding it across all rows:

In[10]:=

In[11]:=

Out[11]=

In[12]:=

Out[12]=

Options (1) 

TraceDuplicates (1) 

Delete duplicate branches and show a more compact visualization:

In[13]:=

In[14]:=

Out[14]=

In[15]:=

Out[15]=

Applications (3) 

Find all the cells a formula depends on:

In[16]:=

In[17]:=

Out[17]=

In[18]:=

Out[18]=

Measure the total dependency depth:

In[19]:=

In[20]:=

Out[20]=

In[21]:=

Out[21]=

Visualize the full tree of dependencies:

In[22]:=

In[23]:=

In[24]:=

Out[24]=

Notice this ExpressionTree respects the ordering among dependencies, but their hierarchical structure is not captured perfectly. See proper Trace tree examples in Properties and Relations.

Properties and Relations (4) 

The output of SpreadsheetTrace[file,cell] has the same form as the output of Trace, but truncated after the elementary leafs are reached.

To truncate Trace at the leafs use replacements rules like the this:

In[25]:=

Find the full Trace of a double sum:

In[26]:=

Out[26]=

Its truncated version perfectly matches the output of SpreadsheetTrace[file,cell]:

In[27]:=

In[28]:=

In[29]:=

Out[29]=

In[30]:=

Out[30]=

Nevertheless, only the pure Wolfram Language Trace allows the drawing of a proper ExpressionTree, which respects all hierarchical relations:

In[31]:=

Out[31]=

In[32]:=

Out[32]=

See Possible Issues for other exceptions to the general analogy between SpreadsheetTrace[file,cell] and Trace.

Possible Issues (1) 

In some examples, to achieve complete analogy with Excel formulas, Wolfram built-in functions like If need to be redefined so to be cleared of Attributes like HoldRest:

In[33]:=

In[34]:=

In[35]:=

In[36]:=

In[37]:=

Out[37]=

In[38]:=

Out[38]=

Still, in this case the analogy with Trace is not yet perfect because SpreadsheetTrace[file,cell] treats as Atom elements only spreadsheet formula cells and their values, ignoring numeric constants in relations such as C4>0 (0) and C1-C3 (-1).