Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Running Total in a Pivot Table

You can use a running total to measure complete growth against the target. It’s a cumulative total which includes the past data as well.

For Example: If you have month wise data from Jan to Dec, a running total will show you YTD total every month.

So, today, I will show you a step by step process to add a running total in a pivot table.

A Pivot with a Running Total

Let’s say, you have a pivot table with each month’s sales quantity from January to December.

Add Running Total In Pivot Table With Month Quantities

Now by using this pivot table you can only able to show that how much sales quantity you have sold in each month.

But using a running total, you can present cumulative total at the end of each month.

By Adding Running Total In Pivot Table You Will Get Same Value For Last And Grand Total

On the other side, running total will help you to show the progress of your sales quantity every month throughout the year.

Steps to Add a Running Total In Pivot Table

Select Pivot Table To Add Running Total In Pivot Table

  • Right click on it & 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 you have a running total in your pivot table showing the cumulative total in every month.

Pivot Table To Add Running Total In Pivot Table

While adding running total make sure that your pivot table sorted in a right way.

For example: if you want to add a running total from January to December then you have to sort values from January to December.

Examples

Here, I have listed some examples related to some specific situation in which you can add running total in pivot table.

1. Start Running Total From Zero

Someone asked that how can we start total from a zero. So I decided to create a pivot table with running total which is starting from zero. Please follow these simple steps.

  • Go to your source data and insert a dummy row with zero values.

Apply Running Total In Pivot Table With Starting From Zero With Dumy Entry In Source Data

  • In my data table, I have data from Jan’15, and that is why I have used Dec’15 for dummy entry.
  • Go to your pivot table and refresh it.

Add Running Total In Table Starting From Zero

Now you have a starting pivot in your running total in a pivot table.

2. Percentage for Running Total In Pivot Table

We can also use the percentage of running total instead values. Please follow these simple steps to insert it.

  • In “Value Field Setting” window.
  • Go to “Show Values As” tab.
  • From “Show Values As” drop-down list, select “% Running Total In”.

Add Running Total In Pivot Table With Percentage

  • Click OK.

Here is your percentage running total in pivot table.

Add Running Total In Pivot Table As Cumulative Percentage

3. Running Total With Grouped Dates

As you can group dates in a pivot table.

But when you do that there will be a little problem you have to face.

In below pivot table, I have grouped dates and create two-row labels from it “Quarters” and “Months”.

And, I have also applied running total in the data field. Now, If you that, after a change in the quarter, the running total is starting over again.

Add Running Total In Pivot Table With Grouped Dates

Here I want to add a running total using the same two-row labels.

So let me show you how can we do this.

  • Go to your source data and enter a new column after the columns with dates.
  • Name the column “Q-M”.
  • Enter the formula =”Qtr”&ROUNDUP(MONTH(A2)/3,0)&”-“&TEXT(A2,”MM”) and drag it to the end.

Appy Running Total In Pivot Table Using Helper Column In Source Data

  • Now refresh your pivot table.
  • Remove both row labels “Quarters” and “Months”. Add your new row label “Year-Month”

Appy-Running-Total-In-Pivot-Table-When-Dates-Are-Grouped-Add-Helper-Column

The only drawback of this method is you cannot get the subtotal for quarters.

4. Running Total In Pivot Chart

With a running total, you can also create a pivot chart to show that cumulative values in a graphical way.

Add Running Total In Pivot Table With Pivot Chart

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

All you have to do for creating a pivot chart, insert a pivot table first with a running total. Then

  • Select any cell from your pivot table.
  • Go to Insert Tab -> Chart Pivot Chart.

Add Running Total In Pivot Table And With Pivot Chart

Select-Chart-Type-Add-Running-Total-In-Pivot-Table

  • Click Ok.

The other best thing about using pivot chart that it dynamic and every time you apply any filters it which change its values.

Samples File

download sample file to learn about how to add a running total in a pivot table

Conclusion

So here is the story about how can you add a running total in a pivot table. You can also use it when you are creating a dashboard you show trends.

I hope you find this tip useful.

Have you ever come across any situation where you have to add a running total to your pivot table? Share your words in the comment box.

Related Tips