Excel formula tracing is an invaluable, visual tool that can help users understand which cells a given formula is referencing.
In financial modelling, the process is critical for understanding the relationship between variables and formulas, swiftly identifying errors and ensuring all inputs and outputs are connected. This is particularly important in complex financial models that involve multiple variables and calculations, where even a minor error can impact results.
While Excel offers native tools that can aid the user in identifying precedents and dependents of a formula, these tools have several limitations when dealing with the formula complexity that characterises the current financial models:
Visual Clutter: Tracing arrows can overlap and create visual clutter in large spreadsheets.
Limited Scope: It traces precedents only within the active worksheet by default.
Manual Refresh: Arrows do not update automatically after changes; a manual refresh is needed.
No Detailed Information: It doesn’t provide detailed information about cell relationships.
Single-Layer Tracing: Only traces one level of precedence at a time, requiring manual tracing for deeper layers.
No Support for Complex Formulas: Struggles with complex or array formulas, often missing some precedents.
Static Arrows: Arrows are static and not interactive, lacking navigation capabilities.
Performance Issues: Can slow down or crash Excel in very large workbooks.
No Historical Tracking: This does not keep a history of traced precedents once removed.
Difficulty with Named Ranges: Lacks clear indication of named ranges and their corresponding cells.
Nevertheless, this is how these native tools can be used:
Selecting the cell containing the formula you want to examine.
Navigating to the Formulas tab on the Ribbon.
Clicking on Trace Precedents (or Dependents). Arrows will appear pointing from the cells used in the formula to the selected cell.
But, when working with complex spreadsheets, you must ensure the accuracy and integrity of formulas. That’s where OAK comes in. Installing this Excel add-in allows you to simplify the process, identify potential errors early, utilise advanced features and achieve results, fast. And overcome all the limitations listed above!
OAK’s Excel Formula Tracing Tool: Features
Navigation
With the Formula Explorer, users can navigate across all formula precedents and branches using customized keyboard shortcuts.
Split
When reviewing a spreadsheet, the most common tasks include comparing two formulas to find discrepancies and comparing branches within a single formula. Utilizing the Split functionality allows individuals to navigate simultaneously, using one tool across all precedents.
Pruning
The Formula Explorer allows users to prune a formula whilst in preview mode. They can then decide whether to continue pruning or revert to the original formula.
Using the Formula Explorer, analysts can better understand a workbook, saving ample time. Instead of relying on Excel’s arrows to trace precedents, OAK has its own Excel parser, making it the ideal choice over competitors which are often difficult to navigate. Plus, the Formula Explorer collapses down to a small transparent rectangle when users click ‘Escape’, so it’s never in your way while working!
