Add Running Total in a Pivot Table

Running total in a pivot table is 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. (Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS).

Running Total in Pivot Table

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

  1. Once you create a pivot table, select any of the cells from data column.
    select-pivot-table-to-add-running-total-in-pivot-table
  2. Right click on it and click “Value Field Setting”.
    click-on-value-field-setting-to-add-running-total-in-pivot-table
  3. Now, you have “Value Field Setting” window.
  4. Go to “Show Values As” tab.
  5. From “Show Values As” drop-down list, select “Running Total In”.
    select-running-total-from-tab-to-add-running-total-in-pivot-table
  6. 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 the pivot table sorted in the right way. If we want to add it from Jan to Dec then the values must have sorted from Jan to Dec.

Start Running Total from Zero

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

  1. 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-dumy-entry-in-source-data
  2. Here our data is starting from Jan’15, and that is why I have used Dec’15 for the dummy entry.
  3. Now, go to the pivot table and refresh it. And once we do that it give a running total starting from zero.
    add-running-total-in-table-starting-from-zero

Percentage Running Total

We can also use the percentage of running total instead of values.

  • 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

Running Total with Grouped Dates

When we add a running total where we have grouped dates there could be a problem, let me show you. In the below pivot table, we have grouped dates and create two-row labels from them “Quarters” and “Months”.

And, we have also applied running total in the data field. Now if you look, after a change in the quarter, the running total is starting over again. But we can fix it using a helper column and here are the steps.

  1. First of all, go to your source data and enter a new column after the date column.
  2. After that, name that column “Q-M” and enter the below formula:
    ="Qtr"&ROUNDUP(MONTH(A2)/3,0)&"-"&TEXT(A2,"MM")
    appy-running-total-in-pivot-table-using-helper-column-in-source-data-1
  3. Now, refresh your pivot table and remove both the “Quarters” and “Months” column fields and add column “Q-M”.
    appy-running-total-in-pivot-table-when-dates-are-grouped-add-helper-column-1

Running Total in a Pivot Chart

With a running total, we 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 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

download

More on Pivot Tables

  1. Group Dates in a Pivot Table
  2. Pivot Table Timeline
  3. Pivot Table using Multiple Files
  4. Ranks in a Pivot Table
  5. Conditional Formatting to a Pivot Table

11 thoughts

Leave a Comment

Your email address will not be published.

  1. I have spent the better part of several hours trying to understand why my running total was not working. One thing that every article failed to mention is that running total must use a date for the base. I was trying to setup a pivot table for YTD payroll. Instead of using a Date column, I was using a Check Num column. All my checks are generated separately and sometimes I can get two checks on the same date – a regular check and then a benefit check (for phone) or a bonus check. Each check has it own YTD totals, so I was trying to use running total for YTD income and deductions and use the check number as the Base. It doesn’t work. Why can’t the base be any sequential series (date, check number, etc) for running total?

    Reply
  2. Can you please send the sample file for”Different Ways to Add a Running Total in a Pivot Table”

    Reply
  3. Hi! Any way to show, in one column the monthly total and, in another column, the cumulative value?

    Reply
  4. 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?

    Reply
  5. 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?

    Reply