0

I have a weird error which caught me out recently. I want to understand if it is expected Excel behavior and if so, is there a way to prevent it or turn it off?

enter image description here

You can recreate the issue by creating a table like the image above. Depending on how I enter the data I get different results.

If I start in column B and go across, putting in 1 along the row then the formula for the total of Col 1-3 changes (in column N).

On Row 9 I went across putting in 1 in each cell and the formula in N10 changed from SUM(B10:D10) to SUM(B10:M10).

However, the formulas in O10 and P10 did not change. Why?

In Row 5 and Row 1 I put in the numbers in a random order and the total formulas stayed the same.

  • Are the cells formatted as text? Is Automatic Calculation turned on? – Eric F Apr 10 '18 at 14:31
  • No, it's a fresh workbook so the formatting is general. Workbook calculation is automatic – user3665624 Apr 10 '18 at 14:35
  • "If I start in column B and go across putting in 1 along the row then the formula for the total of Col 1-3 changes (in column N).". Do you mean the actual formula changes or the result of the formula does? What is the formula in N2? – cybernetic.nomad Apr 10 '18 at 15:08
  • The actual formula changes. It changes from SUM(B10:D10) to SUM(B10:M10). – user3665624 Apr 10 '18 at 15:19
  • In N2 it is SUM(B2:D2) and that is copied down the column. This is a replica of a scoring grid I use. I got caught out by this so I tested it in a fresh workbook to make sure it wasn't a corrupted file or anything. – user3665624 Apr 10 '18 at 15:21
2

The behaviour you are seeing is as designed. It is related to editing options. Excel sees that you are entering data in cells that are adjacent to, but not included in the range of the formula, and expands the range in the formula as an aid for you.

You can turn off this feature by unticking Extend data formats and formulas in Excel options, Advanced options, Editing options.

Also, if you write the formula with locked columns ( =SUM($B10:$D10) ), they will stay unchanged.

  • Thanks for the information. Why though does the formula in column N change but not in O or P? The behavior is the same for each of them if I keep going across. In the future I will lock the cells. – user3665624 Apr 11 '18 at 13:24
  • Sorry, @user , I can't really explain why the ranges in O or P don't change. Perhaps, but really just speculation, the feature is limited to the closest formula (I can imagine total disaster, if the change would propagate further). In my opinion, unticking the option is a better solution. – Tom Brunberg Apr 11 '18 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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