Shares

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.

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.

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

• Right click on it & click “Value Field Setting”.

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

• Click Okay.

Now you have a running total in your pivot table showing the cumulative total in every month.

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.

• 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.

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”.

• Click OK.

Here is your percentage running total in pivot table.

3. Running Total With Grouped Dates

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.

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.

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

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.

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.

• 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.

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.