Use the OFFSET Function for a Running Balance

If you have a spreadsheet that uses a running balance, the OFFSET function is a great way to allow you to easily insert and delete rows, without messing up the balance.
To see how this works, take a look at the image below which is a screenshot from a modified version of my Checkbook register template.


To start, let's talk about the formula in column B. In cell B7 let's say I used the formula =B6+1 to increment the number. If I were to delete row 6 I'd get a #REF! error and I'd have to fix the formula. To avoid getting the #REF! error, you can use the following formula where this_cell is the cell containing the formula:

=OFFSET(this_cell,-1,0,1,1)+1

In this example, the OFFSET function is referencing the cell immediately above this_cell. So if this_cell is B7, the formula is essentially the same as =B6+1. But now if you delete row 6, you don't get a #REF! error.

This same concept works for the running balance in column J. The formula for the balance in cell J7 would normally be =J6+I7-H7. However, just as in the previous example, if you were to delete row 6, you'd get a #REF! error. So instead you can use the OFFSET function:

=OFFSET(J7,-1,0,1,1)+I7-H7

I've used this technique in a number of my templates because it makes the spreadsheet easier to use. The problem with this approach is that it makes the formulas more difficult to debug, because it looks like the formula is referencing itself.