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.

=SUM(INDEX([Amount],1):[@Amount])

The Structured Referencing in Excel Table along with SUM and INDEX functions  is used to calculate the running balance here.

This formula has two parts

 INDEX([Amount],1)

The INDEX function returns the first row of the columns in a  reference form as we have used within the SUM function.

[@Amount]

This part returns value in the current row of the Amount column so when the formula copes down, it always looks at the current row.

---

 4. Running total in an Excel Table where positive and negative values are in two different columns.

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

=SUM(INDEX([Deposits],1):[@Deposits])-SUM(INDEX([Withdrawals],1):[@Withdrawals])