How to Add Running Total in a Pivot Table

A pivot table is a master tool for data analysis, it’s that flexible and powerful.

One of the things which I like about a pivot is we can easily add running total into it, which can further help us in an analysis.

Today, in this post, I’d like to share a simple step process to add a running total in a pivot table.

What’s a Running Total?

It’s a cumulative total which includes the past total as well.

For example, if we have month wise data from Jan to Dec, a running total will show you YTD total every month.

by adding running total in pivot table you will get same value for last and grand total

We can use a running total to measure complete growth against the target.

Quick Navigation

Steps to Add a Running Total in Pivot Table

Adding a running/cumulative total requires simply few clicks and here are the steps to do it.

select pivot table to add running total in pivot table
  • Right click on it and click “Value Field Setting”.
click on value field setting to add running total in pivot table
  • Now, you have “Value Field Setting” window.
  • Go to “Show Values As” tab.
  • From “Show Values As” drop-down list, select “Running Total In”.
select running total from tab to add running total in pivot table
  • Click Okay.

Now we have a running total in the pivot table with a cumulative total in every month.

pivot table to add running total in pivot table

Note: While adding running total make sure that pivot table sorted in a right way. If we want to add it from Jan to Dec then you the values must have sorted from Jan to Dec.

Different Ways to Add a Running Total in a Pivot Table

Here are some examples of adding a running total in different ways.

1. Start Running Total from Zero

Someone asked that how can we start total from a zero. Well, for this we just need to make a small amendment in our source data.

  • First of all, in the source data we need to enter a dummy entry with zero value.
apply running total in pivot table with starting from zero with dummy entry in source data
add running total in table starting from zero

2. Percentage Running Total

We can also use the percentage of running total instead values and here are the steps.

  • First of all, right click on total column and open "Value Field Setting".
  • After that, go to “Show Values As” tab and select select “% Running Total In” from the drop down.
add running total in pivot table with percentage
  • Click OK.
add running total in pivot table as cumulative percentage

3. Running Total with Grouped Dates

But we can fix it using a helper column and here are the steps.

  • First of all, go to your source data and enter a new column after the date column.
  • After that, name that column “Q-M” and enter the below formula:

=”Qtr”&ROUNDUP(MONTH(A2)/3,0)&”-“&TEXT(A2,”MM”)

apply running total in pivot table using helper column in source data
  • Now, refresh your pivot table and remove both the “Quarters” and “Months” column fields and add column "Q-M".
apply running total in pivot table when dates are grouped add helper column 1

4. Running Total in a Pivot Chart

add running total in pivot table with pivot chart

In the above example, we have a pivot chart along with the pivot table to show the trend of values increasing month by month.

Sample File

Conclusion

By adding running total to a pivot table we can track amount/values cumulatively, which is quite useful while tracking sales, budgets, interest values, etc.

One final thing which I wanted to share with you is that running total changes when sort the pivot table.

I hope this tip will help you use pivot tables with full power and now tell me one thing.

Have you ever tried to apply a running total before?

Share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.


Must Read Pivot Table Tutorials


Content Protection by DMCA.com
2018-11-16T06:13:36+00:00

4 Comments

  1. Jay 11 Sep, 18 at 1:41 pm - Reply

    Thanks for the tip on the pivot chart running total. That one’s useful to me!

  2. Tracy Moore 10 Sep, 18 at 4:16 pm - Reply

    This does not appear to be a running total; it is a cumulative total. What’s the difference? The cumulative total always starts in the same spot; the number of items in the total will increase every month. In a running total, however, the starting point will constantly shift, assuring that the total always includes the same number of items. In my field, the running total is much more useful. Would you please show how to create one?

  3. Peter 10 Sep, 18 at 6:53 am - Reply

    Good idea for running totals for this, thanks. Would like to know if a pareto chart can be made using pivot table and pivot chart?

Leave A Comment