Excel 2013 constanly recalculating workbook for no good reason

[masplin]

I have a data model I have successfully converted from Excel 2010. It is driving me nuts, as everytime I touch the data model like inserting a new column, editing the column title or even changing the date format it forces a complete refesh of the workbook and all the pivot tables. This does not happen in Excel 2010 where you have to actually request the refresh. It is obviously insane to refresh the pivot table if you change the format in data model from dd-mmm-yy to dd/mm/yy!!!! The only way to stop it is to select the workbook and hit escape 20 or 30 times to kill all the refreshes.


This is wasting so much time there must be a way to turn off automatic refresh and only refresh the workbook on command? I tried changing the whole workbook to manaul calulate, but this doesn't make any differnce ot this behaviour and just messes up the workbook. Please can someone tell me how to stop this as sure it can't be the right behaviour.



Thanks

Mike

 

[sijpie]

some threads which may help. I don't have 2013, so can't solve it for you

Update (refresh) data in a PivotTable - Excel - Office.com
The Many Ways to Refresh an Excel Pivot Table | Excellent Tips

 

[masplin]

Thanks but I'm trying to not refresh it. Doesn't seem to be any option anywhere

 

[sijpie]

I realise that, but thought that if there is an option to switch on automatic refresh, there would possibly be your answer to switch it off

 

[JoShmoe]

Hi, I unfortunately do not have Excel 2013 yet either. According to this page (https://office.microsoft.com/en-us/excel-help/update-refresh-data-in-a-pivottable-HA102840043.aspx) there appears to be an option to turn off auto refresh:

• Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.
• Click Analyze > Options.
• In the PivotTable Options dialog box, on the Data tab, un-check the Refresh data when opening the file box.

If this does not work for you, since you mentioned that this occurred once you converted the file from 2010 to 2013, two ideas would be:

• Try & re-convert the original file to 2013 format - maybe there was a glitch?
• Try & convert the 2013 file back to 2010 format & then back to 2013? Let us know if this works!

 

[masplin]

Thanks for that but that option is just ofr refreshing when you open the file.

I have this issue with another file created fresh in Excel 20103 so its 2013 functionality

You cant convert back

 

[miguel.escobar]

Mike, you sure that you don't have the refresh every 'x' enabled? is it actually refreshing only the connections or recalculating the whole workbook (like if you were pressing F9)?

 

[masplin]

it recalculates all the pivot tables so like F9. it's triggered if you make any change to the powerpivot data model. The most ludicruous is you change the date format which is just visual and doesn't change any values.

 

[miguel.escobar]

the pivot table is a different story than just the F9 BUT!! I'd need to check the file in order to tell whats really going on. Any way that could happen?

 

[masplin]

I think I can just make a tiny one and it will hapen. The ones i'm working on is massive. i'll make a smal lfile and check.