Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

With "Automatic" or "Automatic Except for Data tables" options: When I change anything, even a cell with no dependable formulas, Excel recalculates everything.

With "Manual": no problem of course.

Just to make sure, even when I open a new sheet and type "A" in a random cell, Excel recalculates the workbook. It freezes a bit and I see "Calculating: (8 processors) xx%" message.

I am forced to work on manual now, but it's not really comfortable.

Remark: I don't know if this is useful but : there is one VBA code I use on one of the sheets, which is activated manually (a button activating the code)

share|improve this question

1 Answer 1

up vote 2 down vote accepted

If any volatile function is used in the sheet, Excel will calculate the whole workbook when ANY cell changes, not just the ones with the volatile formula.

For an overview of volatile functions see http://www.decisionmodels.com/calcsecretsi.htm

Excerpt:

Excel’s Volatile Functions.
Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()

Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()

Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.

A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:

INDEX(), ROWS(), COLUMNS(), AREAS()

and CELL("Filename") IS volatile although a MSKBN article says its not.
share|improve this answer
    
Thank you very much. This is clear & complete! I would probably find it if I knew what to search. –  Noazerty Aug 7 '13 at 9:17
    
Well, you could use the Find functionality to search formulas for the functions listed above. The site's author, Charles Williams, has a lot of tools and wisdom on his web site to help speed up slow workbooks. Many things can be a factor. You just need to get started and find what the problem is in your case. –  teylyn Aug 7 '13 at 9:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.