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

Get FREE Advanced Excel Exercises with Solutions!

Keeping track of our expenses and deposit or remaining balance is a very important task in our daily lives. 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.

how to keep a running balance in excel


How to Keep a Running Balance in Excel: 8 Suitable Ways

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.
=SUM(C5:C11)-SUM(D5:D11)

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.

how to keep a running balance in excel

  • If you want to use the whole C and D columns for Earning and Expense respectively, type the following formula in F5.
=SUM(C:C)-SUM(D:D)

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

how to keep a running balance in excel

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.

Read More: How to Tally a Balance Sheet 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.
=SUM(C5,-D5,E4)

how to keep a running balance in excel

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.

how to keep a running balance in excel

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.


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.
=SUM(C5,-D5,OFFSET(E5,-1,0))

Here, we add the data in the Earning column, the negative values of data in the Expense column, and the resultant values in Remaining Balance together by using the SUM and OFFSET functions. 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.

how to keep a running balance in excel

  • Use the Fill Handle to AutoFill the lower cells.

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

Read More: How to Calculate Running Balance Using Excel Formula


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
='defined name'!E4
  • 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.
=SUM(C5,-D5,Remaining_Balance)

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.

how to keep a running balance in excel

  • Use the Fill Handle to AutoFill the lower cells.

how to keep a running balance in excel

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
='name range'!$C5
  • 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
='name range'!$D5
  • Click OK.

how to keep a running balance in excel

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

  • Now, type the following formula in cell E5.
=SUM(Earning,-Expense,Remaining_Balance)

  • Hit ENTER to see the output in cell E5

how to keep a running balance in excel

  • Use the Fill Handle to AutoFill the lower cells.

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

Read More: Calculate Debit Credit Running Balance Using Excel Formula


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.
=C5-D5

how to keep a running balance in excel

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

  • Use the Fill Handle to AutoFill the lower cells.

how to keep a running balance in excel

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

how to keep a running balance in excel

  • A dialog box will appear, just click OK.

  • After that, you will see the PivotTable Fields and areas on the right side of the Excel sheet.

  • We want to know the total Remaining Balance. So, click on Date and Daily Balance.
  • Click on Sum of Daily Balance and select Value Field Settings…

how to keep a running balance in excel

  • Choose Number Format and click OK in the Value Field Settings dialogue box.

  • Select Currency and click OK.

how to keep a running balance in excel

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’ is selected.

how to keep a running balance in excel

  • 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.

how to keep a running balance in excel

  • Now select 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.
=C12-D12

how to keep a running balance in excel

  • After that, hit the ENTER button and you will see the Remaining Balance of the week.

In 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 in keeping a running balance. Let’s discuss the steps below.

Steps:

  • Create a new column for Daily Balance.
  • Type the following formula in cell E5.
=C5-D5

how to keep a running balance in excel

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

  • Use the Fill Handle to AutoFill the lower cells.

how to keep a running balance in excel

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

how to keep a running balance in excel

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

how to keep a running balance in excel

  • 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
=CALCULATE (SUM (Range [Daily Balance]),FILTER ( ALL (Range[Date] ),Range[Date] <= MAX (Range[Date])))
  • Set the Number Format to Currency and choose as many Decimal Places as you want.
  • Click OK.

how to keep a running balance in excel

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 ƒx Total Daily Balance from Pivot Table Fields.

how to keep a running balance in excel

You can see the Total Daily Balance by using the Pivot Table and DAX. Thus you can make a running balance in Excel.

Read More: How to Make Stock Balance Sheet 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.

how to keep a running balance in excel


Download Practice Workbook


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


<< Go Back To How to Make Balance Sheet in Excel |Excel For Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo