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

We’ve put daily Earnings and Expenses in the first week of February 2022 in our sample dataset. We’ll use it to track expenses.

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

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

Steps:

Make a new column F for the Remaining Balance and use the following formula in the 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.

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, use the following formula in F5.

=SUM(C:C)-SUM(D:D)

Hit Enter.

The advantage of using this formula is that new entries will be automatically updated in cell F5.

Put a new entry in the 12th row and you will see updated savings in cell F5.

Method 2 – Applying the Excel SUM Function to Keep a Running Balance

Steps:

Make a new column F for the Remaining Balance and use the following formula in cell E5.

=SUM(C5,-D5,E4)

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

Press the Enter button.

Use the Fill Handle to AutoFill the lower cells.

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

Steps:

Make a new column F for the Remaining Balance and use 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.

Use the Fill Handle to AutoFill the lower cells.

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

Steps:

Make a new column E for the remaining balance.
Select cell E5 and go to Formulas, then select Define Name.
A dialog box will appear. Type Remaining_Balance in the name section and use the following formula in Refers to section:

='defined name'!E4

Click OK.

Use 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 Enter.

Use the Fill Handle to AutoFill the lower cells.

Method 5 – Keeping a Running Balance by Using an Excel Named Range

Steps:

Make a new column E for the Remaining Balance.
Select cell C5 and go to Formulas, then select Define Name
A dialog box will show up. Type Earning in the Name section and use the following formula in Refers to:

='name range'!$C5

Click OK.

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

='name range'!$D5

Click OK.

Define the Remaining Balance column as in Method 4.
Use the following formula in cell E5.

=SUM(Earning,-Expense,Remaining_Balance)

Hit Enter to see the output in cell E5

Use the Fill Handle to AutoFill the lower cells.

Method 6 – Inserting a Pivot Table to Keep a Running Balance in Excel

Steps:

Create a new column E for Daily Balance.
Use the following formula in cell E5.

=C5-D5

Press the Enter button.

Use the Fill Handle to AutoFill the lower cells.

Select the range B4:E11, go to Insert, and choose Pivot Table

A dialog box will appear, so click OK.

You will see the PivotTable Fields and areas on the right side of the Excel sheet.

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 Value Field Settings dialogue box.

Select Currency and click OK.

You will see the Daily Balance and total Remaining Balance (Sum of Daily Balance) with corresponding dates in the Pivot Table.

Method 7 – Using an Excel Table to Keep a Running Balance

Steps:

Select the range B4:D11 and go to Insert, then choose Table
A dialog box will show up. Click OK, but make sure that ‘My table has headers’ is selected.

Select the cell C12 and go to Formulas and AutoSum

You will see the total Earning in cell C12.

Select cell D12 and click on AutoSum. You will see the total Expense in cell D12.

Make a row for Remaining Balance (14th row) and use the following formula in cell D14.

=C12-D12

Hit Enter.

Method 8 – Using a Pivot Table and DAX to Keep a Running Balance

Steps:

Create a new column E for Daily Balance.
Use the following formula in cell E5.

=C5-D5

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

Use the Fill Handle to AutoFill the lower cells.

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

A dialog box will appear. Select Add this data to Data Model and 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 and select Add Measure.

A window will appear. Provide a name in the Measure Name section (In this case it’s Total Daily Balance)
Use the following code for 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.

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.