SUM function doesn't copy properly from one cell to another in Excel

Question

When I copy a SUM formula from one cell to an adjoining cell in the next column, Excel appears to copy the formula correctly (changing the relative column reference but retaining the absolute row references), but shows the sum (i.e., the value of the total) from the original column rather than the new column.  What causes this, and how do I fix it?

Answer 1

2chihuahuas wrote:

When I copy a SUM formula from one cell to an adjoining cell in the next column, Excel appears to copy the formula correctly (changing the relative column reference but retaining the absolute row references), but shows the sum (i.e., the value of the total) from the original column rather than the new column.  What causes this, and how do I fix it?

Sounds like you might have Manual calculation mode set.  Click on Tools, Options, Calculation, and select Automatic.

If this happens repeatedly, the root cause might be a macro that sets Manual calculation mode and fails to set the calculation mode back to the original state (presumably Automatic).

Answer 2

2chihuahuas wrote:

the problem developed in a document I received earlier today from a business associate ... and I suppose he might have put a macro in there that set the calculation mode to "Manual" for the entire document.

I always set macro security to medium (Tools, Macro, Security), which causes Excel to always ask whether to enable or disable macros.  I usually click on Disable, then I use alt+F11 to look for any macros and inspect them.

Answer 3

Thanks; yes, actually I kind of stumbled into that very solution not too long after submitting my question.  Really was just looking all around the Tools/Options dialog box and got to wondering if the "Manual" calculation setting might be the problem.  Bingo!  Your suggestion that a macro might be the root cause could make sense.  I haven't used any macros recently, but the problem developed in a document I received earlier today from a business associate ... and I suppose he might have put a macro in there that set the calculation mode to "Manual" for the entire document.