Microsoft excel formulation unstable

  JohnM. 05:50 01 Dec 2003

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 2003

Use absolute references.


and copy down.

  VoG II 12:47 01 Dec 2003

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.

Elsewhere on IDG sites

Moto G6 Play Review: First Look

iPad 9.7in (2018) review

Comment utiliser Live Photos ?