Eliminate Excel Spreadsheet Errors – Part 1 – Inter-Sheet Links
Microsoft Excel has become a vital component of most Organisation’s decision-making and reporting processes at all business levels. It is credited with being critical to the running of many Organisations.
Yet, according to a recent UK banking sector survey, over 75% of spreadsheet models don’t have a quality assurance procedure governing the use of spreadsheets. According to another KPMG survey, 95% of spreadsheets contained errors, and 95% of Users claimed their spreadsheets were error free.
Therein lies the danger; Excel is an incredibly flexible tool, and this is its attraction, usefulness, and effectiveness. However, it is that flexibility left unchecked that can also be responsible and the carrier of hidden error-strewn information that in some cases, as highlighted in a feature in the Guardian newspaper earlier this year, for the demise of an Organisation.
It takes only one error in many cases to result in disastrous consequences: Financial, commercial, and reputational loss can be a heavy price to pay for unaudited spreadsheets. So why would any Organisation gamble with using unaudited spreadsheets? The situation often doesn’t present itself until disaster strikes before considering taking the appropriate measures to protect against and avoid spreadsheet errors. By this time, it could be too late. It is always better to audit, eliminate, and prevent the errors from occurring by using the most comprehensive auditing tool – ExcelAnalyzer.
There are various tools available that purport to check and correct spreadsheet errors. Some of these are only track and trace solutions that don’t identify errors or allow you to correct those errors; others are not as comprehensive, intuitive, or as easy to use as ExcelAnalyzer. There is only one that stands out head and shoulders above the rest: ExcelAnalyzer from Spreadsheetsoftware. ExcelAnalyzer is the most potent Excel Add-In available to produce error-free spreadsheets!
This blog is the first part of a series and will focus on just one source of error that is prevalent in spreadsheets. Excel has a very useful and commonly used feature to share data across sheets and workbooks by allowing the User to link sheets in the same or different workbooks.
An Inter-Sheet Link, also known as an external reference, is a formula that dynamically pulls in data from another sheet in the same or different workbook.
When the value of the referenced cell changes, the target sheet, when opened, displays the updated value if the User has remembered to turn on Auto Calculation.
The spreadsheet Link feature can help build efficient workbooks because it eliminates having the same data maintained in multiple spreadsheets, thereby saving time, reducing errors, and improving data integrity. However, it can also be a poison chalice by introducing hidden errors and risks to an Organisation.
This risk of errors can occur because the linked sheet is using data from the same workbook or a different workbook, especially if there are multiple versions or locations where the source workbooks or data can reside.
Management consultancies offer an expensive outsourced service to build and check spreadsheets for Organisations. However, some of these management consultancies could benefit from ExcelAnalyzer.
Fortunately, Spreadsheetsoftware’s Excel audit add-in, ExcelAnalyzer, provides a quick and easy way to review workbooks showing how sheets are connected to each other and to other workbooks to identify errors, inconsistencies, and anomalies and allows the User to correct them easily.
ExcelAnalyzer’s Model-Flow report shows all of the links between sheets which includes the inter-sheet links and the external links. This report is particularly useful if you have a multi-sheet workbook with many links, especially if you need to delete any sheets to reduce the workbook size without causing #REF errors.
As you can see below, the report is colour-coded and shows instantly how the sheets are connected with each other. It shows which sheets contain External Links and clicking on any of the dark green (signifying outgoing links) or orange (signifying incoming links) numbered box links will navigate to that sheet and highlight those inter-sheet links and provide a link back to this report.
So Sheet 4, the C_Expense sheet is highlighted red signifying outgoing links coloured dark green and incoming links which are coloured orange. There are 3 outgoing links go to the B_Budget sheet and 1 to the J_Employee_Sheet and the 2 incoming links are from the F2_World_Population sheet.
The Model Flow report also shows that there are 9 External links from 3 sheets and these are highlighted blue. As can be seen, 1 External link is from the C_Expense sheet.
The colour-coding and links are explained in the two on-line help windows below:
This report also shows Additional Inter-sheet links and External formula links as you can see below. This report also identifies errors caused by the target of an external link being accessed from two different disk locations. This is probably different versions of the same link which is clearly an error.
There are several uses for the Link feature. For example, it is beneficial when generating a summary sheet that retrieves data from one or more sheets. Conversely, you could create one or more interdependent spreadsheets, possibly for different User Departments, from a large and complex data source.
However, Excel doesn’t provide an automatic way to find all the cells in a workbook that contain links. Activities like expanding the cell range in the source sheet can be a laborious manual process to find and keep track of links in the target sheet(s) unless you use ExcelAnalyzer.
ExcelAnalyzer allows you to display links or external references with just a couple of clicks of the mouse. Users can either display the links through the Formula Analysis process to display all the external links in the Model Flow Report, displaying the file name and location. This information is essential to identify whether the same-named link exists in different disk locations on the User’s hard drive. This highlights what is most probably a potential error.
Act Now! Avoid the risk of financial and, or reputational loss; let ExcelAnalyzer audit and protect your Organisation.