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 (Click to watch movie)|
- Watch bpmTraverse in action
- bpmTraverse - Free Download
- Subscribe to the Best Practice Modelling Network
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|
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.
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|
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:
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.
Consider a scenario where the following cells on a worksheet contain the following formulas:
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|
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|
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|
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.