Excel Running Total (Table of Contents)
Running Total in Excel
Running Total is a form of Cumulative Sum process in Excel which is used to match the total sum obtained by using traditional SUM function or addition process with the previous cell value sum with current cell value. For example, we have 5 number whose sum is 100. Now to confirm whether all the values are considered in addition process, we will sum 2nd Value with 1st value. Then after, keep on summing the previously obtained sum and previous cells value. If the summation of Cumulative Sum and traditional sum come equally then there is no cell left or there is no discrepancy in the selected data.
Methods to Find Running Total in Excel
In this article, I will cover the ways of finding running totals in excel. There are several ways we can find the running total in excel. Follow this article explore each one them and add skill to your CV.
Running Total by SUM Function in Excel – Method #1
We can find the running total by using the SUM function. I have a sales data day-wise for one month i.e. Jan 2018 and few days’ data from Feb as well.
I have a grand total at the end. This gives me an overall picture for the month. But if I want to know which day actually made the difference I cannot tell with the overall sum. So I need running total or cumulative total to tell the exact impact date.
By applying SUM function we can find out running total.
- Open the SUM function in the C2 cell and select the B2 cell.
- Now press colon ( : ) symbol, and again select cell B2.
- Now select the first B2 value and press F4 key to make it as an absolute reference.
- The result will be as given below.
So now first B2 cell with dollar symbol becomes an absolute reference, when we copied down the formula first B2 cell remains constant and second B2 cell keeps changing with B2, B4, and B5 and so on.
- Drag the formula to the remaining cells to get the running total.
- Now the grand total and last running total both are same.
Like this, we can get the running total by using the SUM function. In order to find the exact impact lets apply a cumulative graph to the table.
Select the data, under the Insert tab insert Column chart.
- As soon as you insert the chart, it will look like this.
- Now select the grand total bar and select Change Series Chart Type.
- Change the chart type to LINE chart and then click Ok.
- Now the grand total is represented by a line graph and daily sales are presented by a bar graph.
On 16th Jan 2018 we can see the impact. On that date, revenue is increased by 21926.
Running Total by Pivot Table in Excel – Method #2
We can find the running total by using Pivot Table as well. For this example, I am using slightly different data of daily sales tracker. Data is ranging from Jan to Dec.
Let’s apply the pivot table to this data. Apply the pivot table date-wise as I have shown in the below image.
- Go to Insert Tab and then click on the Pivot Table.
- Drag Date Field to Rows section and Amt to value section.
- Now group all the dates into months. Right click on the date and select GROUP.
- Under Grouping select Months as the option. Starting date and Ending date is automatically selected by the excel pivot table itself. Click on OK to complete the process.
- Now we have grouped all the dates into respective months and we have monthly total instead of day-wise total.
- Now right click on the column total and select Value Field Settings.
- Now under Value Filed Settings select Show Values As.
- From the drop-down list select Running Total in and select Date as the Base Field and then click on Ok to complete the process.
- We have a running total now.
- The problem is we do not have a total sale column here. In order to show both running total and month-wise total add sales amount one more time to the VALUES.
Ok, now we have both total sums as well as Running total in place.
Add Percentage Running Total in excel
Excel does not stop there only. We can add a running total percentage as well. In order to add % running total add one more time amt column to VALUES.
Now right click on the newly inserted column and select Value Field Settings
Now under this Value Field Settings go to Show Values As. Under this select Running Total %.
Finally, name this as % Running Total.
Click on Ok to complete the process. Now we have Running Total and % Running Total along with the monthly sales amount.
Things to Remember
- Running total is dynamic in the pivot table. If there is any change in the main data running total changes accordingly.
- In order to show running total and monthly total together, we need the sales amount column to the VALUES twice. One will be for monthly sales and another one is for Running Total.
- By adding graph we see the impactful changes visually.
Recommended Articles
This has been a guide to Running Total in Excel. Here we discuss the methods to find running total in Excel along with examples and downloadable excel template. You can also go through our other suggested articles –
