While working with Excel, we often need to calculate the Horizontal Running Total or Cumulative Sum for large datasets in Excel. Here, we will talk about how we can create a Horizontal Running Total in different approaches and also look at how we can find the Vertical Running Total of a dataset as well.
📁 Download Excel File
Download the practice workbook below.
Overview of Running Total in Excel
Running Total indicates the sequence of sums of cumulative data within a dataset. It is an indication of how certain data grows over time. There are two purposes for a Running Total. First, it eliminates the need to sum the full sequence each time and enables the total to be expressed at any time. If the exact numbers are not significant on their own, it can also avoid having to record the sequence itself.
Learn to Estimate Horizontal Running Total in Excel with These 3 Approaches
To demonstrate things easily, we will consider a dataset where we have the sales amount per year for different years. We will use this dataset to calculate the Horizontal Running Total by the following methods. The dataset is shown below:
1. Using SUM Function
In this approach, we will be using the SUM Function to create a Horizontal Running Total in Excel. The SUM Function adds up values within a certain range specified as the argument. The steps for this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the cell C7.
- Then we write the following formula in the formula bar.
- Next, we select cell D7 and write the following formula in the formula bar.
- Now we drag the icon of Fill Handle at the bottom right of cell D7 and drag it horizontally upto the cell I7.
- The results will show the cumulative sum as the following image.
2. Applying Conventional Method
Basic addition approach involves the sum of the sales of a particular year with that of the previous year to get a cumulative sum in a sequence. This simple approach has the following steps:
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we select cell C7 and write the following formula.
- Right after that, we select cell D7 and write the following formula in the formula bar.
- After that, we drag the icon of Fill Handle at the bottom right of cell D7 and drag it horizontally upto the cell I7.
- The output results showing the cumulative sum will look like the following image.
3. Utilizing Mixed Cell References
Mixed reference is a form of absolute cell reference where either the row or the column is constrained with the dollar ($) sign. In this approach, we will take the help of mixed cell references to find out the horizontal running total. The steps are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, we select cell C7.
- After that, we put down the following formula in the formula bar.
- Now we select cell C7 and drag the icon of Fill Handle at the bottom right of this cell up to cell I7.
- The output results will be visible in the following image.
How to Create Vertical Running Total in Excel
We can also create a Vertical Running Total by using the same approaches. Only this particular time, we would like write the formulas across columns instead of rows. The steps for this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell D6.
- After that, go to the formula bar and write down the following formula, and press Enter.
- Next, we hold Fill Handle at the bottom right of cell D6 and drag it up to cell D12.
- The Vertical Running Total result will be visible like the image below.
📄 Important Notes
🖊️ We should take care while referencing a cell in formulas while calculating cumulative frequency.
🖊️ We can use the shortcut Ctrl+’ to view the formulas in the respective cells.
🖊️ We can use the shortcut key F4 for various cell references starting from absolute cell reference to relative cell reference and mixed cell references.
📝 Takeaways from This Article
You have taken the following summed-up inputs from the article:
📌 We can calculate a Horizontal Running Total using basic addition formula and referencing.
📌 Functions such as the SUM Function can also create a Horizontal Running Total for a range of data.
📌 Mixed cell references can be used in formulas to find the cumulative sum.
In conclusion, I hope that this article has provided you with insights about how we can calculate Horizontal Running Total in Excel. Along with this method of cumulative sum, we have also learned how to do so in case the data are arranged vertically. If you want to know more about Excel, please visit our site ExcelDen and if you have any queries regarding this topic, feel free to let us know down below in the comment section.