This tutorial demonstrates how to update links automatically in Excel.
Update Links Automatically
In Excel, you can link cells’ values to cells from another workbook. With both files open, values in the linked workbook change as you update values in the source workbook. Say you have the value File 2.xlsx with a value of 20 in cell A1 and File 1.xlsx, in which cell A1 is linked to cell A1 from File 2.xlsx.
When you change values in the source file and open the target file, you’ll be asked to confirm whether you want to update values. This is the default setting in Excel, which can be changed to automatically update all links when you open the file.
If you want to update the value automatically when you open the workbook File 1.xlsx, follow these steps:
1. Change the value in the source file (for example, change cell A1 from 11 to 20), so you can see the changes later in File 1.xlsx.
2. Now go to the target file (File 1.xlsx), and in the Ribbon, go to File > Options.
3. In the Excel Options window, go to the Trust Center tab, and choose Trust Center Settings…
4. In the Trust Center Settings window, (1) go to the External Content tab, (2) select Enable automatic update for all Workbook Links, and (3) click OK.
5. Now in the Ribbon, go to Data > Edit Links.
6. In the Edit Links window, go to Startup Prompt…
7. In the pop-up window, select Don’t display the alert and update links, and click OK.
8. You’re back in the Edit Links window, click Close to exit.
As a result, you’ve enabled automatic update of links and also disabled the prompt message on opening the file. So, if you now open again the linked workbook (File 1. xlsx), the value in cell A1 will automatically be updated with the value of cell A1 in File 2.xlsx (20).
Google Sheets files can only link to URLs externally, so links are updated in real time.