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

**Read More:** How to Tally a Balance Sheet in Excel

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

**Read More: **How to Calculate Running Balance Using Excel Formula

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

**Read More: **Calculate Debit Credit Running Balance Using Excel Formula

### 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**D****ecimal 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.

- Drag the
**Date Field**to the**Area**of**Rows**

- Select
**Daily Balance**and**ƒx Total Daily Balance**from**Pivot Table Fields**.

- You can see the
**T****otal Daily Balance**by using the**Pivot Table**and**DAX**.

**Read More:** How to Make Stock Balance Sheet in Excel

## Practice Section

We provided a practice section alongside our sample dataset.

**Download the Practice Workbook**

**Related Articles**

- How to Prepare Balance Sheet from Trial Balance in Excel
- How to Make Projected Balance Sheet in Excel
- Debit Credit Balance Sheet with Excel Formula
- How to Make Profit and Loss Account and Balance Sheet in Excel
- How to Make Trial Balance in Excel

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