excel 2013 question, pivots, absolute v relative

[pete-]

Excel 2013 was recently installed on my machine and although I love many features, there is something that is frankly maddening.

When I create a simple pivot table from a simple set of data, then either move the file or rename it, the pivot fails… it’s looking for the data in the old location or in the old filename. Obviously it’s doing absolute referencing for the data by default when it should be relative, moving with the file, unless I specify otherwise.

I ‘have’ looked up the issue and found some solutions such as “you just have to ‘save as’ a different file name or location” which I find absurd. There is also the solution that says “you must add the data to the data-model,” which I find equally absurd because I may not want to do that.

Is there any way to ‘default’ set the pivots to relative?

Thanks in advance,

 

[NARAYANK991]

Hi ,

Have you gone through this link ?

http://social.technet.microsoft.com...-and-relative-links-problem?forum=officeitpro

Narayan

 

[pete-]

Hello Narayan,
Thank you so much for the assist. Yes I have gone through that page, and some other Microsoft pages, but all the solutions seem to relate to creating a new file. I have not seen any that actually fix the issue if I want to create a new pivot in an existing file. For example the ‘uncheck save-external-values’ only works with newly created files, not an existing file. And the ‘save as’ function is not only ludicrous, it doesn’t work if I send the file to someone. The ‘extend data range’ solution again may work with newly created files, but not old files.

What’s maddening is, I cannot fathom why a developer at Microsoft would even CONCEIVE of making something like this a default setting, and not have an easy way to just shut it off for all files old or new… it’s truly diabolical. I have been in software/hardware development environments for over 20 years, and I cannot imagine a product-planning meeting where someone said “let’s make all the links in all pivots absolute, by default” and people actually said “yes, good idea.” It’s mind-boggling.