Calculate a Running Balance

Suppose you maintain an Excel workbook to record your income and expenses, and you want to know the current balance of cash in hand or bank balance at any given date. With a combination of absolute and relative references, Excel’s SUM function does a great job to provide a fine solution to the problem. I will show you four different scenarios where we can use this function cleverly.

1. When positive and negative values are in one column

In cell E3, enter the following formula and copy it down by double-clicking the fill handle.

=SUM(D$3:D3)

The $ sign locks the reference to row 3, at the start of the sum, so it doesn’t change when the formula is copied down to cell E10.

2. When positive and negative values are in two different columns

In cell F3, enter the following formula and copy it down by double-clicking the fill handle.

=SUM(D$3:D3)-SUM(E$3:E3)

3. Running total in an Excel Table where positive and negative values are in one column.

In cell E3, enter the following formula and press Enter. Excel will automatically populate other rows in the column with formla.