Excel – How does Excel decide to use an absolute path for external links rather than relative path


There seems to be a difference in the behavior of relative links in Excel 2013 from Excel 2010.

What I'm experiencing in Excel 2013 is the following:

  • If the option 'Save external link values' is checked, then links in formulas are always saved as absolute, rather than relative links.
  • To get a relative link, it is not only necessary to uncheck this option, it's necessary to uncheck it before the workbook is ever saved.
  • When a new workbook is created, the option is checked by default

So what I'd like to know is the following:

  • Is all of what I've just described expected behavior for Excel 2013?
  • Is any of it dependent on version of Excel 2013 or environment?
  • Is there any way to change the default value of 'Save external link values' for new workbooks? (It'd be useful to get an answer to this, even if it's 'no').

I'd also be grateful for anybody who has Excel 2013 installed and just wants to confirm whether they see the same behavior on their own machine, even if they don't know the answers to those questions.

Best Answer

I see the corruption of external hyperlinks happen even with Excel 2010, for spreadsheets stored on a SharePoint server. ON save, all absolute hyperlinks are turned into relative hyperlinks, which ruins them. Going to file\Options\Advanced\General\Web Options\"files" tab and unchecking the box named, "Update links on save", stops this behavior.

However, when another user opens the same spreadsheet, if he/she does not also uncheck that box, before he/she saves, the absolute hyperlinks will all be destroyed, too.

This means every single user who might ever access the spreadsheet, has to be warned to change this setting, because there does not seem to be any way to make that change 'stick' with the file itself.

That's especially bad for SharePoint users, since SharePoint is designed to simplify file sharing.

This is all I've been able to determine on this 'problem' - I'd like to have a week to research it, but I have actual work to do. Besides, it seems to me that Microsoft is the entity that should be researching it and fixing it.

Hope this helps....