Calculate a running balance

Running balances are useful for many things; cashbooks, credit card logs or any type of statement which has a running total or balance. Doing this in Excel is very simple and no special formula is needed.

A running total in Excel is no different to a manual calculation on a printed ledger. Usually you have two columns, one for entries that add and another for entries that subtract. For each row the running balance subtracts the negative column from the positive column and then adds the balance from the previous row.

Your spreadsheet needs three columns, one each for “deposits” and “withdrawals” and a third one for the running total. The first formula will be different from the rest because it doesn’t have a total to bring forward from the row before. The formula for that would be =C2-D2. C2 is the cell with the deposits, and D2 is the cell with the withdrawals.

Although the cell references may be different for your sheet, this formula simply subtracts the withdrawal cell from the deposits cell. For the second row, the formula starts off the same way, but this time adds the balance calculated in the previous row: =C3-D3+E2. The cell references subtract withdrawals from deposits in row 3 and add the balance from row 2.

Copy this formula down as many rows of data as you want, and as you enter deposits and withdrawals, this formula will automatically show an updated running balance for whatever you are tracking. To copy it down click the cell with the formula, then click the cell handle in the bottom right hand corner and drag it down using your mouse.

If you decide to delete or insert a row, the running balance formula will display an error. To fix this, select the first cell that contains the error and press the “CTRL” and D keys together. This will copy the formula from the cell above it and replaces the formula generating the error.