Excel Utilities

Casing

While Microsoft Word has offered this functionality for several years, no similar tool is available in Excel. OAK is pleased to offer this capacity to all OAK users. The cell text can be converted to any of the available Casing options:

All Casing options are available through a customized keyboard shortcut defined on ‘Settings/Shortcuts’.

Colors

Using colors to differentiate various content types is a widely used technique for managing complex models. By highlighting inputs, assumptions, formulas and specific cells that require later evaluation, the process is simplified significantly.

While Excel’s native ‘Fill Color’ feature allows for cell color changes, this process can be time-consuming and maintaining consistent colors across a model can be challenging.

OAK addresses these issues by providing a custom color palette, where each color has an assigned shortcut, streamlining the process and ensuring consistency throughout the model.

Jump

The Jump tool is intended for short and frequent use. Jumping will take you to the next cell of a different ‘Category’ to the active cell.

It can be used to activate off-screen data when ‘interesting’ data in the first few columns or rows are similar or zero.

For ease of use, we recommend assigning Shortcuts to Jump features.

Switch Signs

As the name suggests, the Switch Signs tool allows users to switch the signs in all cell references of a formula.

This is incredibly useful when a range of cells, either containing constants (inputs) or formulas require the opposite sign (+,-). An example of this would be a full set of Operating Costs in a financial model.

A new set of tools has been introduced with version 5.4.001 to facilitate operations that were identified by Operis’s auditing and modelling teams as essential to the review and auditing processes. ‘Switch Signs’ is one of those features.

Here’s how it works:

Considering the formula below:

The ‘Switch Signs’ command will invert the signs on ‘Revenue’ to ‘0-Revenue’ (the ‘0’ reduces the risk of a reviewer missing the negative sign, making it more “visible”), ‘Costs’ to ‘+Costs’ and ‘-Working Capital’ to ‘+Working Capital’

If the original formula was wrapped around brackets then the ‘Switch Signs’ command would invert the entire result:

Convert References

While Excel offers the possibility to change formula references to absolute references, this has to be done on a reference-by-reference basis. With the potential to miss one of the multiple cell references that constitute a formula, the process is incredibly time-consuming and error-prone.

The ‘Convert References’ command offers the capacity to apply absolute references, in its different forms, to all cell references of a formula. It can also be applied to a range of formulas.

How it works:

Considering the formula below:

Invoking the ‘Convert References’ command from the ribbon, four options are offered:

Applying the first option, which will convert both column and row of all references, OAK changes the formula to:

If the user decides to remove all absolute references within the formula, the 4th option of the ‘Convert References’ will deliver the initial formula:

Delete Styles

The use of Cell Styles in financial models is a widely spread practice due to the variety of currency and accounting formats, as well as date formats and text formats.

For each cell with a different format from an existing one, Excel automatically creates a new custom style that will be saved in the workbook.

Also, when copying ranges/sheets between different workbooks, Excel copies all the custom styles used on the source to the destination file. This behaviour causes the number of styles to increase dramatically, potentially impacting the file size and performance, even causing Excel to crash.

Excel allows the use to delete styles, but only one-by-one, making the task of deleting 100s of styles virtually impossible. This is where OAK’s ‘Delete Styles’ tool comes to the rescue!

Allowing the user to delete multiple styles in one go and lock styles that are crucial to the spreadsheet, ‘Delete Styles’ ensures styles are never automatically selected by the tool when deleting a set.