Create a Running Balance in Excel that Allows you to Insert, Delete, and Move Rows

One of the most important tasks in personal finance is to keep a running balance of your checking and credit accounts, and spreadsheets are a common tool for doing that. In fact, tracking accounts is one of the original uses for the spreadsheet dating as far back as when the ancients etched ledgers on clay tablets.

The ancients may have occasionally made math errors when they recorded a running total or a running balance. We may think our modern spreadsheets are far superior because we let Excel formulas do the math for us, but we can run into other types of errors instead. The ability that Excel gives us to insert rows, delete rows, and move rows via cut and paste, can introduce hard-to-detect errors.

In this article, I'll explain the problems with the basic running balance formula and provide two robust solutions.

To see the examples in action, download the Excel file below.

Download the Example File (RunningBalance.xlsx)

Problems with the Basic Running Balance

Consider the following very simple example showing deposits and withdrawals and a running balance. The basic running balance would be a formula that adds deposits and subtracts withdrawals from the previous balance using a formula like this: =SUM(D15,-E15,F14).

Problem #1: Deleting a Row Causes a #REF! Error

In this example, row 2 was deleted. The balance formula in row 3 now contains a #REF! error because the reference to the balance in row 2 was deleted. All dependent formulas also contain #REF! errors.

This error is very obvious, and can be fixed fairly easily by fixing the balance formula in row 3. While annoying to fix, it's not as scary as the errors that are harder to detect.

Problem #2: Inserting a Row Causes an Incorrect Running Balance

In this example, a new row was inserted above row 3 and the balance formula was then copied down. The problem is that the balance formula in row 3 is still referencing the balance from row 2.

The error checking feature in Excel was designed to help identify these types of errors. In this case, a little green triangle indicates an inconsistent formula. It isn't wise to rely on the error checking feature because it doesn't catch all errors.

Problem #3: Moving a Row Causes Hard-to-detect Errors

In this example, row 3 was cut and inserted above row 2. Are there any errors?

The scary thing about this example is that there is no obvious indicator of the errors. In fact, the running balance is wrong in both rows 2 and 3, because the formulas are not referencing the correct previous balance.

If I have an error in a spreadsheet, I would much rather see a glaring #VALUE! or #REF! error than have errors I can't see.

Design for Robustness to Common Usage

If you are designing a spreadsheet with a running balance, it's likely that you or somebody else may want to insert, delete, or move rows. So, you need to use formulas that don't cause hidden or hard-to-detect errors.

There are two fairly simple solutions for creating a robust running balance that don't break when you insert, delete or move rows.

Solution #1: Create a Running Balance using the OFFSET Function

The OFFSET function allows you to create a reference by specifying the number of rows and columns offset from a particular reference.

Syntax: =OFFSET(reference,rows,columns,[height],[width])

To refer to the previous balance, we can use the current balance (F15) as the reference and use -1 for the offset rows and 0 for the offset columns like this: =OFFSET(F15,-1,0). Nice and simple.

The OFFSET function does not directly reference cell F14, so if you delete row 14, no problem. You can insert, delete and move rows and the balance formula will always reference the cell above the current balance.

To see the OFFSET function used within functioning templates, take a look at the Checkbook Register and Credit Account Register, both of which include a running balance.

Pros:

OFFSET is compatible with Google Sheets and OpenOffice. The need for compatibility is one of the main reasons I use OFFSET in many of my templates. However, if you only use Excel, you might try Solution #2.

Cons:

OFFSET can make formulas difficult to understand. Notice that in the image above, the OFFSET formula highlights the reference (cell F15) rather than the cell that OFFSET refers to (cell F14).

OFFSET is a volatile function, which means it re-evaluates every time the worksheet is recalculated. This particular use is very efficient, so it likely won't cause a problem unless you have a lot of other inefficient formulas that depend on the results of the OFFSET formula.

Solution #2 - Create a running balance using a Relative Named Range

When you create a named range via Formulas > Define Name, you can use a relative reference like =F14 (no dollar signs) instead of an absolute reference like =$F$14. In this example, I've created prev_balance as a named range that refers to the cell immediately above the current balance.

The named range was created using a relative reference, so when the running balance formula is copied down, prev_balance will always refer to the cell immediately above the current cell, even if you insert, delete, or move the rows.

Not familiar with named ranges? Check out the references listed at the end of this article to learn more.

Pros:

Named ranges can make formulas easier to understand. Notice that unlike OFFSET, Excel highlights cell F14 (the cell that prev_balance is referring to).

Cons:

Most Excel users do not know how to use or create named ranges. Even advanced users may not know about relative named ranges. So, even though the formula may be easy to read, most people are not going to know why or how the formula works.

Named ranges have their own set of complexities and problems, especially when it comes to duplicating or moving worksheets.

Relative named ranges are not compatible with Google Sheets. Currently, Google Sheets treats all named ranges as absolute references (even though the references don't show dollar signs).

How to Create the Relative Named Range

This can be tricky. The most important step is the very first one: selecting the correct cell prior to defining the name.

1. Select cell A2 or B2 or F2 or ZZ2 (any cell in row 2)
2. Go to Formulas > Define Name
3. Enter prev_balance in the Name field
4. Set the Scope to the current worksheet
5. In the Refers To field, enter =$F1 (no dollar sign before the 1)

In our example, the Balance is in column F, so we want prev_balance to always refer to column F. That is why we place a dollar sign in front of the F. It is also why it doesn't matter what cell in row 2 we choose in step 1.

Take it a Step Further: Create relative named ranges for deposit and withdrawal.

Although not necessary for making a robust running balance, you might be interested in making your formula even more readable, as shown in the image below.

The relative named ranges for deposit and withdrawal refer to columns D and E in the current row. To create these named ranges, follow similar steps as above, except enter =$D2 and =$E2 in the Refers to field for deposit and withdrawal.

Bonus Trick: Create a range named cell_above that works everywhere in the workbook

For the sake of being tricky, you could create a range named cell_above that always returns the cell in the previous row, anywhere you use it in the workbook.

1. Select cell A2
2. Go to Formulas > Define Name
3. Enter cell_above in the Name field
4. Set the Scope to Workbook
5. In the Refers To field, enter =!A1 (no dollar signs, don't forget the exclamation mark)

A discussion of Scope is beyond the scope of this article, but the articles listed in the references below cover the topic well. Including the exclamation mark without the name of the worksheet is a trick I learned from Mynda Treacy that makes the relative named range work throughout the workbook.

Questions and Comments

If you have questions about how to apply these techniques or know of other ways they can be applied, feel free to comment below.

An example of another application would be to use OFFSET in the # column for sequential numbering that allows rows to be deleted without #REF! errors, like this: =1+OFFSET(current_cell,-1,0).

References