Excel Formula Auditing Just Got Easier With New OAK 5 Release

On 24th June 2022, another major update to OAK 5 was released, featuring a brand new tool: ‘The Formula Explorer’.

Welcome to a world of smooth, easy formula navigation and visualisation. This UI-based Excel auditing tool is designed to make navigation and formula auditing faster and easier due to the development of our own OAK Parser for tracing formulas.

Main Features

Navigation

With this Excel spreadsheet Formula Explorer, you can navigate across all formula precedents and branches just by using fully customized keyboard shortcuts. This allows you to navigate up and down the formula tree, expand/collapse a specific branch or jump straight to the nth precedent.

Split

One of the most common tasks when reviewing a spreadsheet is the comparison of two distinct formulas to find any discrepancies – or comparing different branches within a single formula. With the Split functionality, the OAK user can now navigate across all precedents simultaneously. With the keyboard navigation shortcuts, these tasks have never been easier.

Editing

Edit a formula with the Formula Explorer and the change will be automatically updated across the row if left to right consistency is detected – no more ‘forgot to copy across after changing the formula’.

Pruning

The Formula Explorer allows the user to prune a formula in preview mode. They can then decide to keep the pruning or revert to the original formula.

Not a fan of UI tools? Not a problem

With OAK 5.5 we are also offering a new set of tracing tools, purely based on keyboard shortcuts. The OAK 1-9 dial-pad ‘Trace Nth Precedent’ tools are UI-free navigation tools and allow the user to navigate directly to a specific formula precedent and back almost instantly.

Q&A with Harry Gross (OAK Developer)

Where did the idea for the Formula Explorer come from?

When studying financial modelling with Operis, you’re taught that a good workbook is easy to navigate – it’s clear where things are and you can follow the formula logic directly down the page.  In practice, not every workbook is set out like this.

The idea of the Formula Explorer came about as a way to get an alternative view of Excel logic that only included the essentials– a way to separate the ‘backend’ of a workbook (the formula logic) from the ‘frontend’ (everything else).

How will this Excel formula auditing tool save analysts time and reduce mistakes in their worksheets?

Using the Formula Explorer, an analyst can understand how complicated layers of workings come together very quickly – without having to navigate through every single input and path of workings to understand what they are.

A better understanding of a workbook can save so much time that would otherwise be spent going down rabbit holes and being able to see and maintain a view of a formula logic tree makes it really easy to spot logic/misalignment mistakes.

How does this differ from other similar tools on the market?

One major advantage the Excel spreadsheet Formula Walker has is that Operis has its own homegrown Excel parser, so we don’t have to rely on the program’s arrows to trace precedents. This gives us an enormous performance advantage over most of our competitors.

I think a lot of competitors’ tools don’t get traction because they are difficult to use – we put a lot of work into making the Formula Explorer as intuitive as possible – the UI aligns very closely with what you‘d expect from Excel and the whole thing is navigable with easy-to-remember signposted shortcut keys – and is also navigable with the arrow keys!

Another issue that some of our competitors have is that their tools cover up a lot of the screen so you’re constantly having to move a window around to see the Excel workings behind it. The Formula Explorer collapses down to a small transparent rectangle when you hit ‘Escape’ or click off it, then is easily reactivatable with Alt+Tab, so it’s never in your way!