Excel – Stop Excel formula from changing when inserting/deleting rows

microsoft excelmicrosoft-excel-2016worksheet-function

I'm trying to set up a budget workbook for my personal budget using 13 sheets, 1 for the totals and the other 12 for each month. I cannot for the life of me figure out why the formulas I have change when I insert a row into one of the monthly sheets. Here's an example of one of the formulas I have:

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

If I insert a row at the top of a sheet, it will increment the twos to threes, throwing off the calculations. Is there any way I can lock the formula from changing at all? It's incredibly frustrating.

Best Answer

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:

Worksheet Screenshot

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.

Related Question