Operis recently added a new feature to the award-winning Operis Analysis Toolkit (OAK). Included in OAK 5, the Formula Walker is a key tool that helps spreadsheet auditors and or indeed anyone trying to understand how a complex spreadsheet works to dynamically “walk” through the precedents and dependents of any formula.

• Read more: Seven ways to trace precedents in Excel

Thanks to the unique features in Formula Walker, spreadsheet users and auditors can obtain a more intuitive understanding of how a spreadsheet works.
In this article, we take a closer look at how Formula Walker works, and why Formula Walker can help anyone who wants to understand or audit a complex spreadsheet.

Want to continue your investigation?

Simply click on one of the listed precedents or dependents and Formula Walker automatically jumps to the cell you selected, immediately updating the list of precedents and dependents to those associated with the new cell.

Along the process, your tracing actions are recorded, and you can navigate back and forth through your steps – right back to the first cell you examined.

Formula Walker was developed to make it easier to navigate through complex formulas in a workbook. It does so by interactively refreshing the content in the Formula Walker panel as the user navigates through a spreadsheet. Users can:

• Instantly see formula links –  List and trace all precedents and dependents of any formula just by clicking on the cell.
• Rapidly step through a formula –  Navigate back and forth through the Formula Walker sequence using keyboard shortcuts to move between precedents and dependents.
• Keep track of the auditing process –  Create cell bookmarks and add comments for future reference.
• Export and import audit history – Bookmarks associated with a spreadsheet can easily be exported and transferred to another device.
• Access OAK features – Direct links to other essential OAK features including Reconstruct, Prune, and Optimize makes it easy to take the new steps.
• Identify data validation rules – If the formula under scrutiny has an Excel data validation rule you can rely on Formula Walker to view it in the Data Validation section. Excel does not have a similar function.

Rui Sobreiros, OAK product manager, says: “The Formula Walker allows people to understand the logic behind a formula. If you understand the logic this reduces the chance of missing something that is not correct.”

Thanks to user-definable keyboard shortcuts expert spreadsheet users can quickly and effortlessly step through a workbook and trackback at the tap of a key.

This bookmarking feature helps users to create a list of potential issues to track and aids in prioritising issues. The export feature makes it easy for teams to collaborate. To wrap it up, using Formula Walker has the following key benefits:

• The combination of live tracing and keyboard shortcuts greatly speeds up the process of stepping through a spreadsheet.
• Because Formula Walker relies on the built-in OAK parser it makes formula interpretation much faster than using Excel’s native parser.
• With the included bookmarking tools auditing teams can use Formula Walker to co-ordinate the reviewing of complex spreadsheets.

According to Rui, “The Formula Walker has advantages over Excel because it enables the user to keeps a record of an investigation. Users can trace the precedents and can always navigate back and follow that same path. That’s something that you can’t do with Excel.”

Where spreadsheets contain hidden sheets, Formula Walker will alert you to the fact when you try to step through the spreadsheet, giving you the opportunity to unhide the sheet. Alternatively, you can also request Formula Walker to automatically unhide hidden sheets via an option in the Formula Walker settings.

Formula Walker is set to see continuing development with new features rolling out on an ongoing basis. One of these upcoming features is a tree view of formulas – making it much easier to explore the branches of a complex formula.

While it is a new feature, Formula Walker is now integrated into OAK 5 as a standard feature. Any user or team that purchases OAK 5 will have access to the new Formula Walker.

Learn more about OAK

Earlier this year OAK received a Lifetime Achievement Award at The Financial Modelling Innovation awards.

OAK5 offers a powerful set of cutting-edge tools that help any Excel user identify potential formula risk, lack of compliance with industry-standard modelling good practices, and improve model quality.

You can try OAK, including Formula Walker, by downloading the 15-day trial edition.