How to solve errors with calculations to external Excel files

Mar 24, 2022 | Excel Hints and Tips

Have you ever created a file with loads of calculations where the information is stored in external files and then found that you need to move the original files or rename them. The problem is that if you do that the calculations break. They are looking for the files where they were originally stored with their original name and if these are changed the calculations won’t work.

Apart from going and manually adjusting every calculation, which is laborious and prone to inaccurate changes, there must be a simpler way to make this change. Luckily there is. 

Changing the location of files in calculations 

There is an option that allows you to change where the calculation is pointing so it alters all the calculations using that data in one go. 

1. Go to the data tab 

2. Select Edit links 

Edit links icon

3. Check the status of the links by clicking on the Check Status button. This will show okay in the status column if the link is still working. It will show Source is open if the file it is connecting to is currently open on your computer or it shows Unknown if the file has been moved, renamed or deleted. 

Edit links dialog box
  1. 4. To change the location or name of the file click on change source and navigate to the location that the file is now stored in. 

5. Click on OK 

Other options in the Edit Links dialog box 

There are two other options in the edit links dialogue box. 

Update Values rechecks the figures in the external files and updates them in the file you are currently in. This can be useful if you have chosen not to update the figures when the file is originally opened and you then change your mind or if you have changed the source and there is still a perceived issue. 

The other option is Break Link. This changes all the external calculations into their resultant value. The calculations no longer exist so will not be able to update. This is the equivalent of doing copy followed by paste values. It is useful when sending the file to people who don’t have access to the external data files. If they tried to update the values in the current spreadsheet it would prompt them to find where the files are stored. This would not be possible to do. 

Further Reading

If you’ve enjoyed reading about how to change the location and checking the status of links in Excel Calculations, there’s some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.