• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 28
  • Last Modified:

Automatic calculation turning off randomly in Excel 2016

Automatic calculation in Excel changing to Manual - all by itself apparently. Using Excel 2016. Any ideas or workarounds?
0
agwalsh
Asked:
agwalsh
  • 5
  • 4
  • 3
2 Solutions
 
AlanConsultantCommented:
Hi,

It will, potentiality, change each time you open a new workbook, depending on the last saved status of that workbook.

It then changes across all open workbooks.

If you then save any of those workbooks, the calculation status is saved with it.

This is likely what gives the appearance of being random, but actually isn't.

Alan.
0
 
jkpieterseCommented:
Calculation in Excel is determined by the first workbook you open in an empty Excel window. If you open more workbooks in that same instance those all inherit the current calculation state. And it gets saved with them as well(!).
If you close all workbooks and then open a workbook, the calc state of that workbook sets Excel's calc state again.

In addition, Even the calculation state of add-ins (or the hidden peronal macro workbook!) can drive the calc state o f Excel, because those are often the first workbooks Excel opens. So if Excel happens to open an add-in first which has calc set to manual, all of Excel remains in manual calc.
0
 
agwalshAuthor Commented:
OK, so the user should make sure that when they save a file, the calculation is set to Automatic and then when it opens that workbook and following opened workbooks should be on Automatic? Is that it?  I don't think they have Add-ins...but I can check.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AlanConsultantCommented:
Yes - if they set it to auto before saving, that is likely safest.

Most advanced users tend to turn it off as there are significant advantages when working with complex models, but it tends to confuse 'basic' users who think their formulae are giving incorrect results.

Alan.
0
 
jkpieterseCommented:
I'd say if an 'advanced' user turns calc to manual he should be returning to the drawing board for a redesign of the file, it aparently has some inefficient calculations.
0
 
AlanConsultantCommented:
Hi,

Yes - that would be one reason I guess.  In my experience, more common would be to give greater visibility on complex models, especially when testing perhaps, but it will be a personal preference as to when.

Nice site by the way.

Alan.
0
 
jkpieterseCommented:
Thanks :-)
0
 
agwalshAuthor Commented:
Thanks to both of you. What you are all saying is basically, make sure your file is set to Automatic Calculation before saving and make sure you have no add-ins that set calculation to manual. Is there anything new in Office 365 that would trigger this as it seems to be a new thing for them...? Thanks
0
 
jkpieterseCommented:
No, Excel has worked like this for as long as I remember (I started using Excel in 1994).
0
 
agwalshAuthor Commented:
OK. It's just the users I had described it as a new thing for them...and they have been using Excel for a while..although not as long as you @jkpieterse :-)
0
 
AlanConsultantCommented:
Beats me - I started in 1995 when Excel 95 came out.

I was using Lotus 123 for a few years before that though :-)

I still have my Excel4 macro reference guide embedded in my personal.xls though!

Alan.
0
 
AlanConsultantCommented:
Question answered.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now