Training Index l How errors happen l Vital Tips

Find and Replace in Microsoft Excel

The FIND/ REPLACE function has proved to be very useful in the auditing of spreadsheets. Along with its normal function of finding words, it can be used to find external links or particularly risky functions.

This is how:
When looking at the results of the LINKS function, find the external reference that concerns you and look closely at the path. Try and identify some unique components of the path e.g. special characters, unique names etc.

By using the unique set of characters you can search for them in the spreadsheet and quickly identify the cells that contain the links.

So in the picture example you would search for the words: "Other Workbook". For older versions of Excel you will need to perform this on every sheet. For Excel XP, by clicking on the OPTIONS button when you are in the FIND/ REPLACE menu, you can choose to search the entire workbook. If you do not find a formulae with the external link then look at any graphs you have and review the source data. As a result the combined use of LINKS and FIND/ REPLACE can make it easy to track down errant links.