See full free course on with downloads on https://online-excel-training.auditexcel.co.za/course/auditing-an-excel-spreadsheet/
00:00 Importance of External links
00:12 ExcelAnalyzer Model Flow report for external links and if stored in different locations
01:28 Which cells contain the external links in all the sheets

Download the software used in this video- https://online-excel-training.auditexcel.co.za/lesson/loading-your-version-of-the-excel-auditing-add-in/

Part of the Excel Spreadsheet Best Practice Playlist- https://www.youtube.com/playlist?list=PLI-_sCxnVtYOscPnlwSTqr1wKhwKTBlEv

Images on https://online-excel-training.auditexcel.co.za/lesson/check-the-external-links-for-obvious-spreadsheet-errors/.

Another quick win with regards spreadsheet errors is looking for external links errors? It will be either right or wrong and you will be surprised how often a spreadsheet is looking at a very old external link instead of the new one.

There are ways to find all the external links using Excel’s inbuilt tools (https://www.auditexcel.co.za/blog/how-to-find-cells-with-external-links-in-excel/). For ease of display we are showing the ExcelAnalyzer method (https://online-excel-training.auditexcel.co.za/course/auditing-an-excel-spreadsheet/).

List of all the External Links used (and are they actually the same spreadsheet)
The first question should just be what links are there in the spreadsheet you are reviewing. As shown below, the Model Flow report highlights 2 external links. What is interesting about these links is that the name is very similar except one is version 5 and one is version 6. Should the spreadsheet not always be using the latest links? You will often find links from months ago which have somehow stayed in the spreadsheet.

You can also find in which cells the external links are used. In the Formulas report you can request to see all the formulas, in the entire spreadsheet, that make reference to external links. As shown below you can see the different versions being used. It also shows you how many times they are used (12 and 42) and you can click on the hyperlinks and go see exactly where they are and what they are doing.

Check where the external links are looking on the external file
External links are often simple formula in that they just point to a cell in the other spreadsheet. If they are not (included in a complex VLOOKUP for example), you probably need to spend a bit more time with it. However, for the simple external links, just have a look at where it is looking.

Some points:

– If you don’t have/ aren’t given the external file, that is a problem. Should it be linked if we don’t have access to it?
– External links can go wrong when someone makes a change in the other file. Physically look to see if it is still looking at the right cells.
– The default method for Excel linking to another file is in absolute terms ($ on the row and column) which is different from how it links by default to other sheets in the same workbook. Check that the user hasn’t accidently left the $ signs in when they were meant to be removed (in this case don’t follow the first cell in the range, look at a copy one to the right or down and it will be easier to spot).

Understanding the External Links in a spreadsheet
External links are either right or wrong. A reference to an old spreadsheet is wrong and should be picked up and addressed immediately. If the same external link appears to be in different locations there is a problem. This is also something that an add in can do better than a human.

Please take the opportunity to connect and share this video with your friends and family if you find it useful.