To make this easier for you, we have combined all of our best tools into our own Excel add-in, our nXt toolkit, which we are giving away for FREE, so you can start using it for your models too.
The nXt toolkit offers a range of features to help make developing and reviewing your models easier. To help you get started we wanted to explain some of the features that are included. In this blog we look at the “workbook information tool” and how it can help you with your modelling.
Of all the helpful tools that nXt has to offer, “Workbook Information” is one of the quickest to get to grips with. Every time you open the workbook, or with just one click of a button in the ribbon, nXt produces a list of useful information about the workbook, which you can use to quickly identify and address many high-level issues with your model.
Some of the critical issues that the Workbook Information tool can help you uncover include:
Large Used Range
This provides the number of sheets with large Used Range. This is useful to know if you want to reduce the file size and can indicate redundant formatting or rogue formulae outside the intended calculation area.
Names with #REF! Errors
Even if you don’t use Names, it’s easy to inadvertently add them when copying in data from another workbook. This often leads to Names with #REF errors. This nXt alert is a really efficient way to view the number of #REF! errors contained in Names within a workbook, so you can find and eliminate them.
Links to External Files
Links to external files can be problematic. If the location, or the name of a linked file changes, errors can be caused in the model. Values may be out of date and there is a risk of corruption if the structure of the referenced file changes.
Not only does nXt provide this information to you as a high-level overview, but it also allows you to “Export the Full Details to Excel”. In doing so, you can view the full details of the issue and follow up accordingly.
Protected Workbook and sheets
Workbook structure and individual sheets can be protected in a workbook. It can be cumbersome to find out what protection has been applied. This alert tells you how many sheets are protected and whether the workbook itself is protected, preventing users of the model from inserting, deleting or moving sheets. It also lets you know whether protection is in use, which makes cell locking active.
When releasing a workbook you may want to ensure that protection is switched on but if you’re wanting others to edit the workbook you’ll want to make sure it’s turned off. This is a quick way to check the protection status with a single click and get a report for each sheet with another.
Sometimes you may hide a sheet to make navigating the workbook easier but unless other users know about this it can lead to confusion and mistakes as they try to work around it.
To help you avoid these issues the tool quickly alerts you to hidden sheets, which is very useful to know, especially if they have an impact on the model. It also helps you to be aware of sheets in a model that you cannot see and helps you avoid inadvertently sharing hidden sheets that you may not want 3rd parties to have access to.
Very Hidden Sheets
Once you’ve checked for hidden sheets you should always check your model for ‘very hidden sheets. These are sheets which do not appear under “hidden sheets” and can only be made visible using Microsoft Visual Basic for Applications (VBA) or the Visual Basic Editor.
Sometimes these can be useful for developing side assumptions or calculations that only have limited bearing on the model or contain proprietary information that you don’t want to share wider.
Often people will hide these while working on a model to make the model more secure. Unfortunately, sometimes in doing so the developer ends up forgetting about these sheets and may share the model, and all of the sensitive data hidden within it, with a third party. This can open you up to significant risks.
By using the workbook information function, you can quickly check your model, or those that you’re reviewing for a colleague, to ensure there’s no sensitive data hiding where it shouldn’t be.
There you have it. Six of the top modelling errors that you can avoid with just one of the features that’s included free in our nXt tool kit. If this sounds like a functionality that would help you with your modelling, we’d highly recommend downloading our nXt toolkit.
If you have any other questions about the nXt tool or how we can help you develop, enhance and audit your models please get in touch at ku.oc.satiremun@ofni and don’t forget to download the tool to help with the common errors above.