Formula Auditing in Excel Just Got Easier With New OAK 5 Release

The latest version of OAK, released on the 24^th of June 2022, is another major update to OAK 5.

Following our commitment to innovate and develop our offering to customers we are pleased to announce that this update brings you a brand-new tool: ‘The Formula Explorer’.

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

Main Features

Navigation

With Formula Explorer, you can navigate across all formula precedents and branches just by using fully customized keyboard shortcuts. Allowing you to navigate up and down the formula tree, expand/collapse a specific branch or jump straight to the n^th 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 simultaneously, using a single tool, across all the precedents. 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 on a preview mode – and 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 dialpad ‘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 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 Formula Walker has is that Operis has its own home-grown Excel parser, so we don’t have to rely on Excel’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 of it, then is easily reactivatable with Alt+Tab, so it’s never in your way!