How to Keep a Running Balance in Excel (8 Methods)

Keeping track of our expenses and deposit or remaining balance is a very important task in our daily life. Because that’s how we know how much we should spend and where to spend it. And for that, we require a running balance. In this article, we will learn about how to keep a running balance in Excel.

To explain the methods, we described the daily earnings and expenses of a person in the first week of February 2022.

Download Practice Workbook

8 Ways to Keep a Running Balance in Excel

1. Subtracting Total Expenses from Total Earning to Keep a Running Balance in Excel

The easiest way to keep a running balance in Excel is to subtract the total expenses from the total earnings. To do this, we are going to simply use the SUM function.

Steps:

• Make a new column for the remaining balance and type the following formula in cell F5.

Here the SUM function adds all the earnings and expenses and then we just simply subtract the total expenses from the total earnings.

• Now hit ENTER and you will see the remaining balance for that week.

• If you want to use the whole C and D columns for earning and expense respectively, type the following formula in F5.

• Now hit ENTER and you will see the output in cell F5.

The advantage of using this formula is that if you want to put new entries in lower rows, they will be automatically updated in cell F5.

• Put a new entry in the 12th row for the 8th day of February and you will see updated savings in cell F5.

By following this simple approach, you can easily keep a running balance in Excel.

2. Applying Excel SUM Function to Keep a Running Balance

We can also use the SUM function in a different way to keep the running balance. Let’s see the process below.

Steps:

• Make a new column for the remaining balance and type the following formula in cell E5.

Here, we are adding the data in column C, the negative value of column D, and the remaining balance in column E together.

• After that, press the ENTER button to see the output in cell E5.

• Use the Fill Handle to AutoFill the lower cells.

This is a way you can keep track of the running balance of your daily life and also you can see your daily savings too.

Read More: Calculate Debit Credit Running Balance Using Excel Formula (3 Examples)

3. Utilizing SUM and OFFSET Functions to Keep a Running Balance Sheet in Excel

A very effective method to keep a running balance is to use SUM and OFFSET functions combined together. We are going to describe the process below.

Steps: 

• Make a new column for the remaining balance and type the following formula in cell E5.

Here, we add the data in the Earning column, negative values of data in the Expense column, and the resultant values in Remaining Balance together by using the SUM and OFFSET function. The OFFSET function returns the cell values in the Remaining Balance column.

• Press the ENTER key and you will see the output in cell E5.

• Use the Fill Handle to AutoFill the lower cells.

Thus you can keep a running balance of your own using Excel.

4. Using a Defined Name for Remaining Balance to Keep the Running Balance

We can also keep a running balance in Excel by defining a name for the remaining balance. Let’s see the process below.

Steps:

• Make a new column for the remaining balance.
• Select cell E5 and then go to Formulas >> Define Name.
• A dialog box will appear. Type Remaining_Balance in the name section and also type the following formula in Refers to section

• Click OK.

Thus we defined the name of the cells in column E. Here ‘defined name’ refers to the sheet name.

• Now type the following formula in cell E5.

The formula will subtract the expenses from the earnings and then add the remaining balance cumulatively.

• Press the ENTER button to see the output in cell E5.

• Use the Fill Handle to AutoFill the lower cells.

By following this approach, you can keep a running balance easily.

5. Keeping a Running Balance by Using Excel Named Range

Another way to keep a running balance is to use named ranges for Earning, Expense, and Remaining Balance columns. We are going to use them instead of cell references.

Steps:

• Make a new column for the remaining balance.
• Select cell C5 and go to Formulas >> Define Name
• A dialog box will show up. Type Earning in the Name section and also type the following formula in the Refers to

• Click OK.

Thus we defined a range for the Earning column. Here ‘name range’ refers to the sheet name.

Similarly, we can define a range for the Expense column too.

• Select cell D5 and go to Formulas >> Define Name
• A dialog box will show up. Type Expense in the Name section and also type the following formula in the Refers to

• Click OK.

To see the process of defining the Remaining Balance column, please go to Section 4.

• Now, type the following formula in cell E5.

• Hit ENTER to see the output in cell E5

• Use the Fill Handle to AutoFill the lower cells.

This way, you can make a running balance by defining named ranges.

6. Inserting a Pivot Table to Keep a Running Balance in Excel

Using the Pivot Table can also be helpful for keeping a running balance. Let’s see the process below.

Steps:

• Create a new column for daily balance.
• Type the following formula in cell E5.

• Press the ENTER button and you will see the output in cell E5.

• Use the Fill Handle to AutoFill the lower cells.

This operation returns the daily balances of the week. To see the total remaining balance in a Pivot Table, follow the procedure below.

• Select the range B4:E11 and go to Insert >> Pivot Table

• A dialog box will appear, just click OK.

• After that, you will see the Pivot Table fields and areas at the right side of the excel sheet.

• As we want to know the total remaining balance, click on Date and Daily Balance.
• Click on Sum of Daily Balance and select Value Field Settings…

• Choose Number Format and click OK in the dialog box that appeared.

• Select Currency and click OK.

After that, you will see the daily balance and total remaining balance (Sum of Daily Balance) with corresponding dates in the Pivot Table.

Thus you can create a running balance and see the savings via Pivot Table.

7. Using Excel Table to Keep a Running Balance

We can also use an Excel Table to keep a running balance. The following steps can be utilized for this purpose.

Steps:

• Select the range B4:D11 and go to Insert >> Table
• A dialog box will show up, just click Ok. But make sure that ‘My table has headers’ selected.

• After that, you will be able to see your data converted to a table.
• Now select the cell C12 and go to Formulas >> AutoSum

You will see the total earning in cell C12.

• Now select the cell D12 and click on AutoSum You will see the total expense in cell D12.

• Make a row for Remaining Balance and type the following formula in cell D14.

• After that, hit the ENTER button and you will see the remaining balance of the week.

By this way, you can keep a running balance using an Excel Table.

8. Using Pivot Table and DAX to Keep a Running Balance

Using Pivot Table and DAX can be efficient to keep running balance. Let’s discuss the steps below.

Steps:

• Create a new column for daily balance.
• Type the following formula in cell E5.

• Press the ENTER button and you will see the output in cell E5.

• Use the Fill Handle to AutoFill the lower cells.

This operation returns the daily balances of the week. To see the total remaining balance in a Pivot Table, follow the procedure below.

• Select the range B4:E11 and go to Insert >> Pivot Table

• A dialog box will appear, select Add this data to Data Model and then click OK.

• You will see Pivot Table fields and areas at the right side of the sheet.
• Here the Table name is Range. Right click on it. You will then select Add Measure.

• A window will appear. Give a name in the Measure Name section (In this case its Total Daily Balance)
• Type the following code in the Formula

• Set the Number Format to Currency and choose as many decimal points as you want.
• Click OK.

Here we calculate the total daily balance by comparing the dates and their corresponding daily balance. We use the FILTER function to filter the dates. 

• Now drag the Date Field to the Area of Rows

• Select Daily Balance and fx Total Daily Balance from Pivot Table Fields.

You can see the total daily balance by using the Pivot Table and DAX. Thus you can make a running balance in Excel.

Practice Section

In this section, I gave you the dataset that we used to explain these methods so that you can practice on your own.

Conclusion

The article explains how to keep a running balance in Excel in the best possible ways. If you have any better methods or ideas or any feedback, please leave them in the comment box. This will help me enrich my upcoming articles.

Related Articles

• How to Calculate Running Total in One Cell in Excel (5 Ways)
• Quick Analysis Tool: Calculation of Running Total in Excel (4 Ways)
• How to Use Pivot Table to Calculate Running Total by Date in Excel
• Calculate Horizontal Running Total in Excel (3 Ways)
• How to Calculate Running Total by Group Using Excel Power Query
• Cumulative Sum in Excel If Condition Applied (6 Methods)
• How to Calculate Running Total in Excel (9 Ways)