Hi,

I have 2010 and 2013 office installed on my PC and I have noticed the disappointing difference in the way how Excel 2013 handle relative links in pivot table.

If excel file is saved in Excel 2013 relative links (or "named range" links) to the pivot table's source data are constantly changes to absolute links after any operation with file (copy/move or rename).

For example:

I have excel file test.xlsx on desktop containing 2 sheets - 1st with data named as "range" and 2nd sheet with pivot table. I set link to the data as "range" and it works just perfect. After that I save and close file. If I rename, copy or move file to another place relative link to the named range will be repalced by absolute \path\desktop\test.xlsx!range or 'D:\..\..\test.xlsx '!range (if file located not on desktop). 

How is it possible to set .relative. link to the source of pivot table in new Excel 2013 and avoid further problems with file handling operations?

• Moved by Jaynet ZhangMicrosoft contingent staff, Moderator Friday, November 09, 2012 9:58 AM (From:Excel IT Pro Discussions)

HI,

I am not very clear about when you moved the file to another place, then the relative link to the named range changed to absolute link. You mean that you see the link in the Pivot Table? If it is, then how you see the link in the Pivot Table?

Based on my test in Excel 2013, I created a Excel file followed the steps you posted, and after I move the file into another place, the Pivot Table still works well, and I didnt see any absolute links.

I have noticed this behavior first in Excel 2013 preview version. Tried just now on another PC with newly installed Office 2013 Plus (x86) and Excel again changes relative link to pivot table data source to absolute on file move/copy/rename.

Maybe it is because two offices on one PC, but I do not like the idea to deinstall office 2010 to test it.

Hi,

Could you help me where I can find the absolute/relative link? In Change Data Source?

Correct. The link changes in "Change PivotTable Data Source" dialog window.

link to screenshot _http://trueimages.ru/view/q4nQa

• Edited by gr2y Friday, November 09, 2012 12:41 AM

Hello,

If you don't want to embed your file path in the data source, you will need to go to "FILE--Options--Advanced" and then uncheck "Save external link values" checkbox. This has to be done before saving the file.

Hope it helps.

Hi Luis,

Thank you for good catch. This option was checked at my by default on newly installed Excel 2013.

However unchecking it does not result in any change in bad office behavior. Document saved in Excel 2013 converts link to absolute anyway, Excel 2010 works fine.

Option "Update links to other documents" also has no influence on this issue in both offices. 

I'm just wondering is it only I who experienced that problem or somebody can share my sadness? 

I've discovered that after checking that option, all new files work perfectly and Excel is storing the relative path so I can move the file to other folder with no problems.

However, if the Excel file was created when that option was checked, even if I uncheck that option now, it appears that Excel 2013 is still storing the absolute path and I'm not able to remove it. This seems like a bug to me.

• Proposed as answer by MichaelMagistro Saturday, June 07, 2014 5:06 AM

Hello everyone.

It is a very problematic and disturbing behavior only affecting files created using Excel 2013.

Here is what I found:

Sadly, the solution that Luis suggested does not work for me.

What is seems to be working is using File --> Save as.. when copying/moving/transferring/renaming files.

When I try copying/moving/transferring/renaming the through the Windows Explorer without opening it via Right Click & Copy / Ctrl+C / Drag & Drop etc, then the links change to absolute, thus reading from initial file. 

I still haven't been able to find a safe solution for this and I must say I am not very happy. Luckily, it doesn't seem to be affecting files created in earlier versions.

• Proposed as answer by Jawzack Sunday, September 22, 2013 7:09 PM
• Unproposed as answer by Jawzack Sunday, September 22, 2013 7:09 PM
• Proposed as answer by Jawzack Sunday, September 22, 2013 7:13 PM

I'm having the same problem with a spreadsheet I need to share with co-workers.  If you use any file system method to copy the file (including drag and drop into an email) you get the behaviour outlined in this thread.

I have to provide instructions on how to correct to my co-workers - not very convenient or acceptable

• Proposed as answer by thaeds Tuesday, February 25, 2014 9:12 PM
• Unproposed as answer by thaeds Tuesday, February 25, 2014 9:12 PM

• Uncheck "Extend data range formats and formulas" in Options -> Advanced
  

To clarify this post, what i did to make it work for me was to:

1. Open the file.

2. Make sure both of these are checked: Options >> Advanced >> "Save external link values" & "Update links  to other documents"

3. Once those have been checked, press "ok".

4. Right click one of the sheets and "select all sheets"

5. Copy all sheets to a NEW workbook and Save As.. the file to a new name.

This worked for me. Please confirm by following these steps and then putting the file in a dropbox folder or sending the file to a new computer via email. I tested this using dropbox.

• Proposed as answer by MichaelMagistro Saturday, June 07, 2014 5:12 AM
• Edited by MichaelMagistro Saturday, June 07, 2014 5:14 AM

I don't think this is the solution, as this will not enable you to share the file with other computers... unless you can "Save As.." into their computer through a shared drive. However, I tried this solution, and it didn't work for me. Check the solution I wrote above - test it and see if you can get it to work.

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.

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.

 You can fix this issue by changing the XML files.

1. Open the files using a Zip file manager (I used 7-Zip and it stops the files from being corrupted).  If you convert from .xlsx to .zip, edit it, then convert back, it corrupts the files.

2. Navagate to /xl/pivotCache

3. In each of the pivotCacheDefinitionX.xml files, you need to change the following XML:
- if you have a refreshedBy attribute in your <pivotCacheDefiniton> tag, the value should be "Reference"
- in <worksheetSource> the tag should be only: <worksheetSource name="export" sheet="NAME_OF_DATASHEET"/>.  remove any ref or id attributes.  Those are the ones that cause it to be absolutelinks.  Make sure that you insert the name of your own datasheet in the sheet attribute value.

4. To test that this has succeeded.  Once you've saved and closed your xml files, open the xlsx file you were editing and go to your pivot tab.  Under Pivot Table > Analyze, click Change Data Source.  If this navigates you to the source data sheet and does not have your file name and path, then it succeeded.

You should be able to create a program to do this automatically using the TrueZip package and an XML editing package.  Make sure to configure your true zip to be able to edit .xlsx files and not just .zip files.

Hope this helps!

I have noticed this problem as well and tried the solutions listed in this thread.  None of them fixed the issue.  I found that if I used a Named Range for the pivot table instead of a range reference Excel would not create an absolute link. 

Hopefully that helps.

There does not seem to be resolution to this yet?

I have the same issue with Excel 2013 pivot tables.  Mine happens on "save as", and Excel updated to an "absolute" data path.  All I am doing is saving as a new name in the same file, v2, v3, etc.

I have tried all the suggestions, but it seems Excel 2013 has something that resets.  Some of the solutions work once or twice, but then it reverts back to an absolute file name.  I have not been able to figure out the combination of switches I have to set to get it to work.

Excel 2010 worked fie.

https://social.technet.microsoft.com/Forums/office/en-US/43bf5110-dfad-40e5-a71c-e9736da6fbc2/data-source-path-in-pivot-table-changes-to-absolute-on-its-own?forum=excel

https://social.technet.microsoft.com/Forums/en-US/0cedfc28-aeba-469c-9a91-0389fc3c6937/excel-2013-not-updating-pivot-table-data-ranges-correctly-please-advise-how-to-fix?forum=excel