Microsoft excel formulation unstable

  JohnM. 05:50 01 Dec 03

I have a large spreadsheet with 30 columns and over 700 lines. One section has to be added up down 6 columns. I am making constant alterations by adding new lines. Problem this seems to affect the addition formula which is always losing the top range of the columns. ie x2:x700 becomes x25:x700 how do I stop this.


  VoG II 06:35 01 Dec 03

Use absolute references.


and copy down.

  VoG II 12:47 01 Dec 03

Sorry, that's not entirely clear is it!

Say your formula at present is


change it to


Then when you add extra rows the summation will still start with row 2.

A little tip when inserting rows in sum formulas is to always insert the new row above the formula row OR at row 2 of the range.

For example -

If you had a range of data from A1 to A9 and in A10 you had the formula =sum(A1:A9) then to insert a new row you would either click on the number 2 or 10, this way the formulas integrity would be maintained.

If it is important that the data that is in the new row should be at the top of the data block then modify the above by having a blank row at row 1, data in rows 2 to 9 and the formula in row 10 BUT the formula includes the blank row and the top insertion is still done on Row 2.

As VoG points out Absolute references should be used wherever practical, they will automatically update as you add in rows.


This thread is now locked and can not be replied to.

Samsung Galaxy S8 review

1995-2015: How technology has changed the world in 20 years

What’s happening in VR right now: From psychedelic art to combat therapy

Siri not working? Try these troubleshooting fixes