How to Calculate Running Balance Using Excel Formula (4 Ways)

Keeping a running balance of your checking and credit accounts is one of the most critical chores in personal finance, and spreadsheets are a popular tool for doing so. When recording a running total or a running balance, the ancients may have committed certain math mistakes. As we allow Excel formulas do the math for us, we may believe our current spreadsheets are vastly superior, yet we can run across other forms of errors instead. Today, in this article, I’ll show you four suitable ways by which you can get to learn how to calculate running balance by using the Excel running balance formula effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Suitable Ways to Calculate Running Balance in Excel

Consider the following scenario, you have an account in a  bank. You deposit the balance and withdraw the balance corresponding to your account from that bank which has been given in column C and column D. We’ll calculate the running balance by using the Excel formulas named SUM function, OFFSET function, INDEX function, and SUMIF function. Here’s an overview of the dataset for our today’s task.


1. Use of the SUM Function to Calculate Running Balance in Excel

In this method, from our dataset, we can calculate the running balance by using the SUM function. Please follow the instructions below to learn!

Step 1:

  • First of all, select cell E5.

Use of the SUM Function to Calculate Running Balance in Excel

  • After selecting cell E5, type the SUM function in the Formula Bar. The formula in the Formula Bar is,
=SUM(C5-D5)

Use of the SUM Function to Calculate Running Balance in Excel

  • Now, press Enter on your keyboard and you’ll get $15,828.00 as the return of the SUM function in cell E5.

  • Further, again, select cell E6 to calculate the running balance by using the SUM function.

Step 2:

  • In the Formula Bar, type the SUM function to calculate the running balance. The SUM function in the Formula Bar is,
=SUM(C6-D6+E5)

Use of the SUM Function to Calculate Running Balance in Excel

  • After that, again, press Enter on your keyboard and you’ll get $43,054.00 as the output of the SUM function in cell E6.

Use of the SUM Function to Calculate Running Balance in Excel

  • After learning the above process, place your cursor on the Bottom-Right of cell E6, and instantly an AutoFill Sign will appear in front of you, and drag the AutoFill Sign downward.

Use of the SUM Function to Calculate Running Balance in Excel

  • Finally, after dragging the AutoFill Sign downward, you will be able to calculate the running balance by using the SUM function from our dataset which has been given below screenshot.

Use of the SUM Function to Calculate Running Balance in Excel

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


2. Apply the OFFSET Function to Calculate Running Balance in Excel

The OFFSET function lets you generate a reference by giving the number of rows and columns that should be offset from a given reference. Here, we’ll apply the OFFSET function to calculate the running balance from our dataset. Let’s follow the steps.

Steps:

  • To apply the OFFSET function to calculate the running balance, select cell E6.

Apply the OFFSET Function to Calculate Running Balance in Excel

  • Now, type the SUM and OFFSET functions in the Formula Bar. You should type the OFFSET function inside the SUM function. The function in the Formula Bar is,
=SUM(C6,-D6,OFFSET(E6,-1,0))
  • Here, to refer to the past balance, we can use the current balance (E6) as a reference and offset rows and columns with -1 and 0.

Apply the OFFSET Function to Calculate Running Balance in Excel

  • After typing the functions in the Formula Bar, simply press Enter on your keyboard. After that, you will get $43,054.00 as the output of the functions in cell E6.

  • Now, place your cursor on the Bottom-Right of cell E6, and an AutoFill Sign pops up, and drag the AutoFill Sign downward.
  • After dragging the AutoFill Sign downward, you can get the running balance by using the OFFSET function from our dataset which has been given below screenshot.


3. Perform the SUM and INDEX Functions to Calculate Running Balance in Excel

In this method, we will apply the SUM and INDEX functions to calculate the running balance. This is the easiest way to calculate the running balance by using Excel formulas. Please follow the steps to learn!

Step 1:

  • For calculating the running balance by using Excel formulas, first select the cell array B4 to D13.

Perform the SUM and INDEX Functions to Calculate Running Balance in Excel

  • Now, from your Insert Tab, go to,

Insert → Tables → Table

Perform the SUM and INDEX Functions to Calculate Running Balance in Excel

  • By clicking on Table Menu, a window pops up titled Create Table.

  • Now, press the OK button from the Create Table

Step 2:

  • After creating the table, select cell D5.

  • Now, type the SUM and INDEX functions in cell D5. The functions are,
=SUM(INDEX([Sale],1):[@Sale])

Perform the SUM and INDEX Functions to Calculate Running Balance in Excel

  • After typing the functions in cell D5, just press Enter on your keyboard and you will be able to calculate the running balance to the entire column which has been given below screenshot.

Perform the SUM and INDEX Functions to Calculate Running Balance in Excel


4. Apply the SUMIF Function to Calculate Running Balance in Excel

So far We’ve seen situations where we calculated the running total for all of the values in a column. However, there may be cases when you need to calculate the running total for a specific set of records. For example, in the screenshot below, I have a data collection for which I want to calculate the running balance for PC and Mobile in two separate columns E and F. The SUMIF function is one of the Excel running balance formulas. Let’s follow the instruction below.

Step 1:

  • For inserting the SUMIF function in Excel to calculate the running balance, first, select cell E5.

Apply the SUMIF Function to Calculate Running Balance in Excel

  • Now, write down the formula in cell E5.
=SUMIF($C$5:C5, $E$4, $D$5:D5)

Apply the SUMIF Function to Calculate Running Balance in Excel

  • After typing the SUMIF function in cell E5, simply press Enter on your keyboard and you will be able to get $35,999.00 for the running balance of the PC as the return of the SUMIF function.

  • Now, place your cursor on the Bottom-Right on cell E5, and an AutoFill Sign pops up, and drag it downward.
  • After dragging the AutoFill Sign, you’ll be able to calculate the running balance for the PC.

Step 2:

  • After calculating the running balance for PC, now we want to calculate the running balance for Mobile. For that, select cell F5.

Apply the SUMIF Function to Calculate Running Balance in Excel

  • After selecting cell F5, type the SUMIF function in that cell. The formula is,
=SUMIF($C$5:C5, $F$4, $D$5:D5)

  • Now, press Enter on your keyboard and you will be able to get $- for the running balance of Mobile as the return of the SUMIF function. Look at the output of the function, the output is blank because there is no sale for the first date.

Apply the SUMIF Function to Calculate Running Balance in Excel

  • After pressing Enter, place your cursor on the Bottom-Right on cell F5, and instantly an AutoFill Sign will appear in front of you, and drag it downward.
  • While dragging the AutoFill Sign, you’ll be able to calculate the running balance for Mobile.

Apply the SUMIF Function to Calculate Running Balance in Excel


Things to Remember

👉 While applying the INDEX function, first create a table from the Insert Tab.


Conclusion

I hope all of the suitable methods mentioned above to calculate running balance by using the Excel running balance formula will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo