The Connected Workbook Delay
If you build complex Microsoft Excel spreadsheets, you likely rely on external links—formulas that pull data from other Excel files saved elsewhere on your network. By default, whenever you open your master spreadsheet, Excel will immediately attempt to contact those external files to refresh the data. If those linked workbooks are massive, or if they are stored on a slow, congested corporate network drive, this automatic updating process can cause your master spreadsheet to freeze for several minutes before you can even begin working.
How to Disable Automatic Link Updates
You can change the security and trust settings within your master workbook to prevent Excel from automatically fetching external data, giving you control over when the refresh occurs.
1. Open the master Excel workbook that contains the external links.
2. Click on the File tab in the top-left corner of the ribbon.
3. Click on Options at the bottom of the left-hand menu.
4. In the Excel Options window, select Advanced from the left sidebar.
5. Scroll down until you find the section titled “General” (usually near the very bottom).
6. Locate the checkbox labelled Ask to update automatic links and ensure it is unticked. (This stops the annoying pop-up prompt).
7. Now, scroll back up slightly to the section titled “When calculating this workbook.”
8. Untick the checkbox labelled Update links to other documents.
9. Click OK to save your settings.
Refreshing Data Manually
With these settings disabled, your master workbook will open instantly, but it will display the data exactly as it was the last time you saved it. When you are ready to fetch the latest numbers from the network, you must trigger the update manually. Navigate to the Data tab on the ribbon, click the Edit Links button, select the specific source workbooks you want to refresh from the list, and click Update Values. This allows you to work fast and only incur the network delay when you absolutely need the freshest data.