Disabling Formula Updates for Broken Links in Excel

In order to get around the problem of an Excel workbook attempting to update formulas by referencing outside spreadsheets it’s no longer linked to, it's necessary to force Excel to work with certain settings. If an outside linked to workbook is not present in the same linked to network location a #NAME error will occur.

If you already have a different Excel file open, with the default ‘Automatic’ setting activated, a setting of a new file to only manually update formulas will be overridden and formulas will automatically update, causing an error.

If the Excel files you’re working with themselves have the ‘automatic’ setting selected for formula calculation, the #NAME error will still occur, even if when they are opened one by one while all other workbooks have been closed.

To avoid this problem follow these steps:

1. Open a new blank workbook.

2. Go to File . . . .Options , then click ‘Formulas’ on the menu on the left. In the ‘Calculation options’ section select the radial button for ‘Manual’ under Workbook Calculation and uncheck the ‘Recalculate workbook before saving’. We need this option set so we can save the workbook on the worksheet that is used without having the formulas with broken links update.

3. On the left side menu, click on the Trust Center . . . and click ‘Trust Center Settings’

. . . you’ll see you have the option to ‘Disable all Data Connections’ and then to ‘Disable automatic update of Workbook links’ under the ‘External Content’ section.

Keep this blank workbook open, and then open each of the spreadsheets you want to review. They will inherit the settings from your 'priming' workbook.