Excel 2013 workbook constantly recalculating

[masplin]

I have been fighting Excel 2013 for about a year and asked this question when I first got it, but not many people were using it. I can't believe it is only me so I am desperate for some help.

I have large workbook with 6 or so linked excel tables and several hundred measures that was converted from Excel 2010. However I have exactly the same issue with one created in Excle 2013 and using a mix of SQL and linked tables. The workbook has maybe 50 pivot tables

If i go to the powerpivot window and change the date format of a date I see calculating in the bottom right, but then when it is finished, the whole workbook starts reclaculating every pivot table showing "Reading DATA" in the bottom of the excel window. This is clearly insane as changing the format of the date is a visual thing and doesn't affect any calculations. Also if I create a new measure in powerpivot window it does the same thing, then again when I name the column. Its a new measure that is NOT in any of the existing pivot tables. My only way out is to hit escape 50 times which has become extremely wearing.

In Excel 2010 none of this happens as you do all your work creating measures etc and then hit refresh to get the pivot tables to refresh. I am about to rebuild my monster worbook to change the tables to SQL and unless there is a solution I am going to built it in 2010, because it wastes a lot less of my life!

Previously microsft told me this was intended behaviour and we tried things like turning the calc in the workbook and powerpivot window to manaul but to no avail. So what my questions are:

1. Does anyone else have issues with the workbook recalculating every time you change anything?
2. Is there any way to stop this so pivot tables only refresh on demand?
3. What am I losing going back to Excel 2010?

Really appreciate any feedback

Mike

 

[scottsen]

It's not just you; it's horrible. And, if you don't care about Power View, I certainly prefer 2010.

But first, I demand you go try this, and let me know how it works. I haven't tried it yet...
OLAP PivotTable Extensions - Home

 

[masplin]

ah ha looks like genius. Brilliant I'll give it a go and report back.

 

[masplin]

Unfortunately installed and got an error as soon as i tried to use it. I'm asking if the add-in writer knows what the problem is

 

[masplin]

Could you give the add-in a try as tried 2 different workbooks and got this error when clicking on the disable auto refresh

Exception HRESULT 0x800A03EC

at system.runtimetype.forwardscal<wbr>ltoinvokemembers9string membername, bindingflags flags, object target, int32[] a wrapper types, messagedata&mgdata etc etc.

just like to know it isn't just me! just as I installed the add in I got an automatic office 365 update so maybe that has knackered it.

Mike

 

[scottsen]

I finally got off my butt and tried it... and it's kind of fantastic. no problems here, and worked just like I hoped. Hope you get your issues worked out!

 

[masplin]

ah that is so annoying as means its somethnig specfic to my computer. Thanks for pointing me in the right direction and confirming I'm not alone!!! i can't understand why Microsoft haven't fixed this themsleves as amkes a mature workbook almost imossible to work with.