In many cases, you might need to calculate the register balance and it would much easier for you to use Excel to track and check the balance.
The formula which calculates running balance is used frequently in a check register. In Excel, you can use the SUM function or simple addition and subtraction for this formula.
= running balance = previous balance – withdrawals + deposits
Considering the following example, the running balance formula in E3 is:
Figure 1.Check register balance 1
The value in E3 will be:
Figure 2.Check register balance 2
If you are familiar with SUM function, a formula in E3 will be like this:
=SUM(E2,-D3,C3). This will produce the same results.
When the formula is extended into the next rows, it will continue to calculate a running balance in each row.
There are no worries if either deposits or withdrawals values are empty. The formula counts them as zero if they are empty. These formulas work well when both deposits and withdrawals values are filled. For example, I will add a withdrawals value in row 4 and delete the next row. The new table will be like below:
Figure 3.Check register balance 3
In this case, deleting a row caused a
#REF! Error because there was no reference to the old balance in the deleted row. You can fix the balance formula in current row 5 by simply re-copying the E4 formula in E5.