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.

**Table of Contents**Expand

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

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

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)`

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.

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

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

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

`='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**.

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

- 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`

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

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

- 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**’ is 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 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`

- 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`

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

`=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.

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

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

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