bpmTraverse

bpmTraverse is a free add-in to Microsoft Excel which identifies the components of a worksheet formula and allows the user to quickly identify and walk through each of these components. The tool also allows the user to 'drill-down' into the formulas within the precedent ranges of a formula.

bpmTraverse Dialog Box
bpmTraverse Dialog Box (Click to watch movie)

bpmTraverse is freely available to download, and is also included in bpmToolbox, along with hundreds of similarly powerful best practice modelling tools.


See Also


Traversing Formulas

Consider a cell containing a formula which has two precedent ranges; Precedent Range 1 and Precedent Range 2. Each of these precedent ranges contains a formula which has three precedent ranges. These relationships could be represented using a hierarchical structure known as a 'formula precedents tree', as shown below:

A Formula Precedents Tree
A Formula Precedents Tree

bpmTraverse allows the user to navigate a formula cell's formula and drill down into any of its precedent ranges. In the example above, the user could drill down into the formula in Precedent Range 1 of the Formula Cell, and then further drill down into each of the precedent ranges of the formula within Precedent Range 1. This process could be repeated in order to determine the assumptions underlying any formula in a workbook.


Using bpmTraverse

To traverse a formula, select a cell containing a formula and load the bpmTraverse Dialog Box. The bpmTraverse Dialog Box will be displayed, loaded with the components of the selected formula, and will look something like the following:

The bpmTraverse Dialog Box
The bpmTraverse Dialog Box

By using the mouse or the cursor in either the Formula or Precedents list boxes, each of the components of the formula can be viewed and drilled down on as required. Each of the fields in this dialog box is explained below:

 
Functionality Description
'C', 'F' or 'M' (top right corner of dialog box)
Formula
  • Displays the active cell content (similar to the formula bar).
  • Formula components are color-coded to aid identification, with repeated formula components being colored the same.
  • Hovering the mouse over a component in the Formula window momentarily will display the current value.
  • Formula components can be drilled down on by double-clicking on one of the components in the Formula window, selecting it in the Precedents list box (below) and pressing the Enter key or by pressing the Right Arrow key.
Precedents
  • Lists the precedent ranges of the formula in the cell being traversed.
  • Especially useful when a range is referenced several times by the formula.
  • Formula components can be drilled down on by double-clicking on one of the components in the Formula window, selecting it in the Precedents list box (below) and pressing the Enter key or by pressing the Right Arrow key.
Back
  • If possible, loads the bpmTraverse Dialog Box with the formula one level back up the formula precedents tree (discussed above).
  • Only available when the cell in the Formula window is not the highest level formula in the formula precedents tree (i.e. the Formula Cell in the above formula precedents tree) - i.e. only after the tool has drilled down on a precedent cell.
  • Keyboard shortcuts are Backspace, 'B' or the Left Arrow key.
Target Mode
  • When first clicked, sets the bpmTraverse Dialog Box to 'Target Mode', thereby allowing the user to select a different cell to load into the bpmTraverse Dialog Box.
  • When clicked again (or another part of the dialog box has been clicked on), the selected cell in the worksheet will be loaded into the bpmTraverse Dialog Box as the Formula Cell.
  • Keyboard shortcut is 'T'.
Go To
  • Closes the dialog box and activates the precedent range that was last traversed.
  • Keyboard shortcut is 'G'.
 

Note that in order to close the Traverse Formula Dialog Box and return to the original selected formula range (i.e. not the precedent range that was last traversed), simply press the Escape key or close the dialog box via the dialog close 'X' button.


Example

Consider a scenario where the following cells on a worksheet contain the following formulas:

 
Cell Formula
A1 =B1+C1
B1 =D1+E1+F1
C1 =G1+H1+I1
 

Additionally, assume that the numbers 1 - 6 are entered into the six cells in the range D1:I1 respectively. If the cell A1 is selected and the Traverse Formula tool is loaded, the Traverse Formula dialog box will appear as follows:

bpmTraverse Dialog Box - Cell A1 Selected
bpmTraverse Dialog Box - Cell A1 Selected

Note that the value of cell B1 ('6' - i.e. the sum of the values '1', '2' and '3' in the three cells D1 to E1 respectively) will be displayed when the mouse cursor is hovered over the precedent in the Formula window. In this example, cell A1 will become the Formula Cell in the following formula precedents tree:

Resulting Formula Precedents Tree
Resulting Formula Precedents Tree

If bpmTraverse is then used to drill down on the precedent cell B1 (by either double-clicking on one of the components in the Formula window, selecting it in the Precedents list box and pressing the Enter key or by pressing the Right Arrow key), the tool will load the formula in cell B1 into the dialog box, as follows:

bpmTraverse Dialog Box - Cell B1 Drilled Down
bpmTraverse Dialog Box - Cell B1 Drilled Down

Note that the dialog box now contains the formula within the cell B1; the first precedent of the original formula within the cell A1. This process could then be repeated to drill down on cell B1's precedents – i.e. D1, E1 or F1. In each case, the user could return to the original formula by clicking on the Back button or close the dialog box on a selected precedent by clicking the Go To button.

This is a very simple example of the use of bpmTraverse. The value of the tool increases exponentially as the formulas that it is being used to review become more complex and include references to precedent ranges on external sheets, external workbooks and/or hidden sheets, rows and/columns.