6

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.

nixda
  • 27,268
Ben Aaronson
  • 171
  • 1
  • 1
  • 4
  • it's necessary to uncheck it before the workbook is ever saved. <- I cannot confirm this in my Excel 2013. The only thing that has influence is, whether the workbook is saved or not. See my screenshot: http://i.stack.imgur.com/YT44Q.png – nixda Oct 06 '13 at 11:30
  • Have a look at the last answer on this technet discussion – nixda Oct 06 '13 at 11:38
  • The last answer, suggesting using named ranges, doesn't work for me. Excel stil changes references to absolute. Ex: 'D:\Dropbox\Excel\Projects\Time Reporting\Time Reporting3-3.xlsx'!ExportTimeReporting – user1283776 Nov 26 '14 at 13:43

3 Answers3

6

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....

BobW
  • 61
  • 4
    Actually it's very confusing: in the Advanced options, there's a box to 'update links to other documents", and a box to 'save external link values', and in Web Options there's another box to 'update links on save'. How are users who are trying to use Excel to do actual work, supposed to figure out the nine different possible combinations of results that can occur from all these seemingly related settings? It's a mess. – BobW Aug 04 '14 at 12:59
2

To prevent Excel from changing hyperlinks from absolute to relative, go to File - Info, click Show all properties and fill Hyperlink base property with c:\. The actions are valid for Office 365 but there should be similar steps for other versions.

To make this behavior default for all new workbooks, change/create default workbook template. In general, you need to create book.xltx in Excel's startup folder (it varies between versions). More about default templates is here https://exceljet.net/how-to-set-a-default-template-in-excel

The workaround is from here https://www.excelforum.com/excel-general/1197648-sharing-absolute-path-hyperlinks-solved-stop-hyperlinks-from-changing-to-relative-path.html

montonero
  • 608
  • This is the most sketchy workaround ever but well done, it works! We had a shortcut in an Excel workbook to a page on SharePoint. It kept changing it to a relative like. This stopped this very annoying behaviour – munrobasher Dec 06 '21 at 13:06
0

To add more confusion to the possible solutions there has to be noticed that paths are displayed as absolute ones even though they behave as relative: this is evident when the linked worksheet is closed and the local data is "cached" to its last source location.

Just tried to move a couple of files with a link created with no particular care, and it stayed relative, as long as the linked file was actually opened, and as the file was closed it mutated into an absolute path.

If instead the file was not anymore found at the relative location (ie in my case the very same folder), the absolute path took precedence and if the file was actually found where it was pointing, became finalized as such.

I have done some testing moving a couple linked files across different locations, and then moving only the linking and leaving the linked one behind.

HTH

(using excel 2013)

user677617
  • 9