How Do I Update Links in Excel?
Ordinarily, Excel 2010 and 2013 prompt you to update links between workbooks every time you open a file that contains links. The first time you open a workbook, you might need to click "Enable Content" in the security warning, but thereafter the process works automatically. If you've moved or renamed your source workbook, however, you need to update the link manually by directing Excel to the source's new location.
Open the destination workbook, which contains the non-working links, and click "Edit Links" in the Connections section of the Data tab.
Select the source of the broken link and press "Change Source."
Browse to the source workbook's current location, choose it and press "OK." Provided you pick the correct workbook, the source listed in the Edit Links window will read "OK" in the Status column, and the data in all linked cells will update.
Press "Startup Prompt" if you want to set Excel to always update or never update working links when you open the current workbook. This setting does not automatically fix broken links -- it only updates links with an "OK" status. The default option, "Let users choose..." uses the current user's setting, which by default displays an update prompt each time you open the workbook.
Close all open dialog boxes and select a linked cell to verify that its formula points to the updated source workbook. If you need to manually update the data in linked cells again while the book is open, press "Refresh All" on the Data tab.
Tips & Warnings
- Click "Break Link" in the Edit Links window to remove all links from the selected source and replace the cells with a static copy of the current data.
- To turn off the link update prompt on workbooks set to "Let users choose..." open the "File" menu, click "Options" and then "Advanced" and uncheck "Ask to Update Automatic Links."
- To stop a particular source workbook from updating links in other workbooks automatically, open the source workbook, open Excel's options to the Advanced tab and uncheck "Update Links to Other Documents."
- Links within a workbook always update automatically in real time unless you set the "Calculation Options" to "Manual" on the Formulas tab. If you use manual recalculation, press "F9" to update these links.
- Selecting a new source in the Edit Links window updates the source for every linked cell that uses that source. If you'd rather update an individual cell and leave others untouched, select the cell and edit its formula by hand to point to the new source workbook.