Control when links are updated in Excel

In a corporation, working with large data sets usually means working with Microsoft Excel. But things get a little tricky when you begin splitting the data into multiple files, located on the corporate intranet. My biggest annoyance here is how Excel stubbornly insist on asking me every time if I want to update the fields linked to other files. I have learned from experience it’s best not to do that; it’s often slow, keeping you from doing what you first set up to modify in that file, and secondly most of the time it reports not finding the right files. In short, a big waste of time!

You can go around this by working with a local file or breaking the links to other files completely, but that can require a lot of work later, trying to update the changes in the shared version.

Fortunately I stumbled upon a solution that has been in front of me all the time: an option to turn that annoying message box off. In the menu for Office 2003 and prior versions go to Edit ► Links…, click on Startup Prompt… and change the default setting to “Don’t display the alert and don’t update automatic links”. This way Excel will retain the last used values in cells linked to other files. Or, if you prefer to take the chance that all the required files and sheets and cells are where they’re supposed to, choose “Don’t display the alert and update links” for a silent update.

I didn’t work with Office 2007 or 2010 yet, but from the information on the web, this settings should be located on the Data tab, in the Connections group.

If you make this change, keep in mind it only applies to the current workbook, not the entire program, so you should change it in every single file. Excel has another, more general setting, that can be found in the program options (Tools ► Options… ► Edit for the 2003 version or Office button ► Excel Options ► Advanced ► General for 2007).  Look for “Ask to update automatic links” there; if unchecked, this means Excel will update all the links in all the files the user opens without prompting him first.

If you need more information about this topic, search it online. I have found interesting stuff this way, including two books!