Stop Excel formula from changing when inserting/deleting rows

Solution 1:

What you need to understand is that the absoluteness of absolute references, as specified by the $, is not absolutely absolute ;-)

Now that that tongue-twister is out of the way, let me explain.

The absoluteness only applies when copy-pasting or filling the formula. Inserting rows above, or columns to the left, of an absolutely referenced range will "shift" the address of the range so that the data the range points to remains the same.

In addition, inserting rows or columns in the middle of the range will expand it to encompass the new rows/columns. Thus to "add" a row of data to a range (table) you need insert it after the first data row.

The simplest way to allow adding a data row above the current data range is to always have a header row, and include the header row in the actual range. This is exactly the solution proposed by cybernetic.nomad in this comment.

But, there's still one more issue left, and that's adding a row of data after the end of the table. Just typing the new data in the row after the last row of data won't work. Nor will inserting a row before the row after the last row.

The simplest solution for this is to use a special "last" row, include that row in the data range, and always append new rows by inserting before that special row.

I typically reduce the row height and fill the cells with an appropriate colour:

For your example, the full "simplest" formula would thus be:

=SUMIF(JUN!$G$1:$G$501,"Utilities",JUN!$H$1:$H$501)

Another way to achieve the same goal is to use a dynamic formula that auto adjusts to the amount of data in the table. There are a few different variations of this, depending on the exact circumstances and precisely what is to be allowed to be done to the table.

If, as is typically the case (your example, for instance), the table starts at the top of the worksheet, has a one row header, and the data is contiguous with no gaps, a simple dynamic formula would be:

=SUMIF(INDEX(JUN!$G:$G,2):INDEX(JUN!$G:$G,COUNTA(JUN!$G:$G)),"Utilities",INDEX(JUN!$H:$H,2):INDEX(JUN!$H:$H,COUNTA(JUN!$G:$G)))

This is a better solution than using INDIRECT() as

1. It is non-volatile and therefore the worksheet calculates faster, and
2. It won't break if you insert columns to the left of the table.

The dynamic formula technique can be further improved by using it in a Named Formula.

Of course, the best solution is to convert the table to a proper Table, and use structured references.

Solution 2:

So you’re saying that, if you insert a new Row 2 (between the current Row 1 and Row 2), you want the formula to look at the new Row 2?  Here are a couple of variations:

=SUMIF(INDIRECT("JUN!$G$2:$G$500"),"Utilities", INDIRECT("JUN!$D$2:$D$500"))

will always look at Rows 2 through 500, without regard to rows being renumbered by insertions (or deletions).  This means that, if you insert a row, the original Row 500 will be renumbered to 501 and will be bumped out of the range.  If you want to look at the current Row 2 through the original Row 500, use

=SUMIF(INDIRECT("JUN!$G$2"):JUN!$G$500,"Utilities", INDIRECT("JUN!$D$2"):JUN!$D$500)

In case it isn’t obvious, INDIRECT() takes a string (text) argument and interprets it as an address.  It lets you do invariant addressing, because the strings (that look like addresses) won’t get adjusted when other addresses get adjusted because of row/column insertion/deletion.

Note that the $ characters in the address strings are optional; they have no effect.

Solution 3:

Offset is tedious to set up, but worth it if you are collecting years worth of data a week or month at a time.

to calculate the address of the last row of data in column D:
=OFFSET(D$1,1,0)-Dcurrent-last-row+1
Dcurrent-last-row will increment when you insert, the offset from D$1 will not

you can use this formula for averages, ranges, etc:
R2: =OFFSET(D$1,1,0)-D3180+1
Average all the values in column N, except the zeros
=AVERAGEIF(OFFSET(N$1,1,0):OFFSET(N$1,R2,0),"<>0")