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?
LVL 1
agwalshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Jan Karel PieterseExcel and VBA ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Jan Karel PieterseExcel and VBA ExpertCommented:
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
Jan Karel PieterseExcel and VBA ExpertCommented:
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
Jan Karel PieterseExcel and VBA ExpertCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.