Speed Up Spreadsheet Reviews Using the New Formula Walker Feature 4 Feb 2021
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.
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.
How does the Formula Walker work?
When you select a cell the Formula Walker pane displays an easy-to-access list of precedents and dependents of that cell. In the list of precedents and dependents, Formula Walker tells you which sheet the cell is on, the cell address, the formula contained in that cell as well as the value.
Ticking the “Workbook Name” checkbox automatically displays the workbook name too, useful where a cell refers to an external file. You can also view all comments and data validation rules.
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.
Key features of Formula Walker
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.
Formula Walker not only offers improvements over Excel’s built-in tools. Formula Walker also performs better, thanks to the proprietary OAK parser which processes complex formulas more quickly and more reliably.
Benefits of using Formula Walker
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.
Because Formula Walker bookmarks are saved in a local file that’s associated with the active workbook users can ensure that bookmarks are transported with the workbook if it is sent to a colleague or external party.
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.”
What else should you know about Formula Walker?
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.