[Fixed!] Excel Links Not Working Unless Source Workbook Is Open

To demonstrate the solutions to why Excel links may not be working unless the source workbook is open, we’ll use two workbooks named Workbook1 and Workbook2.

Solution 1 – Don’t Use Direct Formulas

If you use any functional formula directly on the links, then the Excel links won’t work unless the reference Workbook is open.

In the image below, cell D4 contains a link to a range in a sheet in Workbook1.xlsx. The OFFSET function has been applied directly to the link. With Workbook1 open the function works, and displays the correct result.

Excel Links Not Working Unless Source Workbook is Open

But when Workbook1 is closed, the function stops working and displays an error instead.


Don’t apply formulas directly to your links, or reference the cell values of the linked range only, and apply any functions to these values as a subsequent step. The links will then work even when the reference Workbook is not open.

With Workbook1 closed, the link in cell C5 is still working perfectly.

Solution for Excel links not working unless source Workbook is open

Now Excel functions can be applied to the linked cell values with Workbook1 closed. Here for example, using the IF function.

Solution 2 – Update the Name of the Linked File

If you mistype or change the file name of a linked Workbook, then the link won’t work. You will get the following warning from Microsoft Excel.

Use Exact Name of Linked File unless Excel Links Not Working even Source Workbook is Open


Check your inputs, and if you change any linked file’s name, update the Workbook name in the links accordingly.

Solution 3. Apply Update Values

If the previous fixes don’t work, or if any value in the linked workbook changes, update the values in your worksheet using the Edit Links feature.


  • From the Data tab >> click the Edit Links feature.

A dialog box named Edit Links will appear.

  • Choose Update Values >> click on Close.

In this window, you can also check the status of the link by using the Check Status option.

Solution 4 – Must Select Update Option

When you re-open an Excel file which has a link to another workbook, the following warning from Excel will be displayed. Always select Update to make the links work.

Solution 5 – Try to Not Use the Name of Reference Range

Sometimes, if you use a name of a reference range in your link, then the link may not work. Use direct cell references instead.

Solution 6 – Reopen Workbook

If you link to a workbook which has been exported from other file types, saving the linked workbook as a different Excel file type using the same name can solve problems with links. After renaming the file, close both workbooks and re-open them. The links should now be working normally, even after you close the linked Workbook.

Read More: [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel

Solution 7 – Set Calculation to Automatic

If the Calculation Options in Excel are set to Manual mode, then the Excel links may not work unless the source workbook is open.

The solution is to set the Excel Calculation Options to Automatic mode.


  • From the Formulas tab >> go to Calculation Options.
  • Observe which one is marked.
  • If the Automatic option remains unchecked, check this option.

Read More: [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe

Things to Remember

  • Avoid using Excel functions directly on your links.
  • Always use the correct name and location of the linked Workbook and Worksheets in your links.

Download Practice Workbook

Also, the reference file is given below.

Related Articles

<< Go Back To Linking Workbooks in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF