How to Calculate Running Balance in Google Sheets

How to Calculate Running Balance in Google Sheets
Reading Time: 4 minutes

What is Running Balance in Google Sheets?

Running balance is a total of transactions already made, or in other words, a cumulative total. So let’s say, you go to a shopping mall to buy some groceries. You purchase a list of items such as soaps, shampoo, etc. Now you go to the billing counter to purchase the selected items. As soon as the billing person scans any item, a running balance is generated. 

How to Calculate Running Balance in Google Sheets

Let’s say that the first item scanned is shampoo, whose price is Rs.100. So the running balance right now is Rs.100. Now, he scans soaps priced at Rs.50. Now the running balance is Rs.150 ( Rs.100 + Rs.50 ). Thereafter, he scans Tissue Roll priced at Rs.30. Now the running balance is Rs.180 ( Rs.100 + Rs.50 + Rs.30 ). So, to summarise, the running balance is the total of all the transactions already made.

Why use Running Balance in Google Sheets?

Running balance is the simplest way to manage individual accounts. One significant benefit of using the Running balance in Google Sheets is that you stay up to date with the balance of that account. Running balance in Google Sheets helps businesses track accounts based on individual entry-level. Therefore, if there is any discrepancy in the final total, one can navigate to the running balance and look for outliers or incorrect entries.

How to Calculate Running Balance in Google Sheets?

Calculate Running Balance in Google Sheets using SUM Formula and Cell Referencing 

  • Let’s say you have a dataset of items from a shopping mall, which looks something like this:
Dataset of interest
Figure 1: Dataset of interest
  • You want to calculate the running balance of these items.
  • The syntax here is:

=SUM($Column$Row:ColumnRow)

  • Here, the “$” sign is used for referencing the cell. 
  • So, for the first cell, the formula is =SUM($B$2:B2). 
  • Now drag down the selection till the last cell of items.
  • If you copy the formula down to other cells, the formula will always start with cell B2 as we have applied Absolute cell reference to cell B2.
  • This will help us calculate the running total till every new item is added. 
  • Since the other parameter (B2) is not referenced, it will change as soon as you drag down the selection. This is called relative referencing.
  • Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.
  • If you drag the selection to cell C4, the formula for cell C4 is =SUM($B$2:B4).
  • Here’s a closer look at the formula for every cell for calculating running balance.
Using cell referencing to calculate Running Balance in Google Sheets
Figure 2: Using cell referencing to calculate Running Balance in Google Sheets

Calculate Running Balance in Google Sheets using ARRAYFORMULA

  • Let’s say you have income and expenditure data for a month, which looks like this:
Dataset of interest
Figure 4: Dataset of interest
  • You want to calculate the running balance for the entire month.
  • To calculate the running balance, add income entries and subtract expenditure entries. Use:

=ArrayFormula(if(len(A11:A),(SUMIF(ROW(C11:C),”<=”&ROW(C11:C),C11:C)-SUMIF(ROW(B11:B),”<=”&ROW(B11:B),B11:B)),))

  • Here, (SUMIF(ROW(C11:C),”<=”&ROW(C11:C),C11:C) calculates the running balance by adding the income values.
  • SUMIF(ROW(B11:B),”<=”&ROW(B11:B),B11:B)) adds the expenditure values.
  • If you are unaware of the ARRAYFORMULA, you can click here.
  • If you are unaware of the SUMIF formula, you can click here.
  • The final output looks like this:
Using ARRAYFORMULA to calculate running total in Google Sheets
Figure 5: Using ARRAYFORMULA to calculate running total in Google Sheets

When to Use Running Balance using Cell Referencing vs When to Use ARRAYFORMULA

If you want to perform simple calculations, as explained in the first example, it is advisable to use cell referencing as it is quick and easy to perform. You can also use ARRAYFORMULA for simple calculations, but the formula will be a bit more complicated than the cell referencing method.

However, if you want to perform calculate the running balance using multiple columns, it is advisable to use the ARRAYFORMULA method. Let’s say you have a column of debit entry and a column of credit entry. You want to calculate the running balance for every entry. In this case, it is advisable to use the ARRAYFORMULA method as you can use functions such as SUMIF and MMULT to find running balance. In this case, if you use cell references, it would make the referencing very complicated as multiple columns are involved.


So based on your use case, you can use either one of the above-mentioned methods.

Conclusion

You can calculate the Running Balance in Google Sheets using Cell Referencing or by using ARRAYFORMULA. Running balance helps businesses track individual entries and look out for incorrect ones. 

See Also

Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.

You can checkout other equally good articles on Google Sheets here.