How to Add Running Total in a Pivot Table

Contents hide
What is a Running Total in a Pivot Table?
Steps to Add Running Total in Pivot Table in Excel
Start Running Total from Zero in Pivot Table
% Percentage Running Total in a Pivot Table in Excel
Running Total in Pivot Tables with Grouped Dates
Running Total in a Pivot Chart
Tips while adding Running Totals in a Pivot Table
Sample File
More Pivot Table Tutorials

What is a Running Total in a Pivot Table?

A running total in a pivot table is a cumulative total that includes the past total. For example, if we have month-wise data from Jan to Dec, a running total will show the YTD total every month. We can use a running total to measure complete growth against the target.

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

Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.

Steps to Add Running Total in Pivot Table in Excel

Adding a running/cumulative total requires simply a few clicks. 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.

We have a running total in the pivot table with a monthly cumulative total.

pivot table to add running total in pivot table

Note: While adding the running totals, ensure the pivot table is sorted correctly. If we want to add it from January to December, the values must be sorted from January to December.

After adding a Running Total, Pivot Table doesn’t change the source data.

Understanding Value Field Settings

The Value Field Settings option in the pivot table is an essential feature that helps you decide how to calculate data in a pivot table field. It offers functions like Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, and Varp.

Start Running Total from Zero in Pivot Table

Someone asked how we can start the total from zero. We need to make a small amendment to our source data to do this.

  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 dummy 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 in a Pivot Table in Excel

We can also use the percentage of the 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 in Pivot Tables with Grouped Dates

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

We have also applied the running total to the data field. Now, if you look, after a change in the quarter, the running total is starting again. But we can fix it using a helper column. 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")
    apply running total in pivot table using helper column in source data
  3. 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
="Qtr"&ROUNDUP(MONTH(A2)/3,0)&"-"&TEXT(A2,"MM")

Let’s break down this formula to understand how it operates:

  • MONTH(A2): The MONTH function in Excel returns the month of a specific date as a number between 1 (January) and 12 (December).
  • ROUNDUP(MONTH(A2)/3,0): This part of the formula takes the month’s numerical value, divides it by 3, and rounds up the result to the nearest whole number using the ROUNDUP. This process effectively converts the month number into a quarter number (1-4).
  • "Qtr"&ROUNDUP(MONTH(A2)/3,0): This generates a text string that begins with “Qtr” followed by the quarter number. The ampersand & is used here to concatenate, or join together, the “Qtr” string with the quarter number.
  • TEXT(A2,"MM"): The TEXT function in Excel converts a numerical value into a text string in a specified number format. Here, it converts the date in cell A2 into a text string representing the month as a two-digit number.

Running Total in a Pivot Chart

With a running total, we can also create a pivot chart to show cumulative values graphically.

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.

Tips while adding Running Totals in a Pivot Table

  1. Ensure the correct data selection: This is the first and fundamental step for running totals in a pivot table.
  2. Check the Calculations: It’s always a good practice to ensure you have selected the correct calculation for your running total.
  3. Refresh after Data Changes: If the data in your original table changes, you must refresh the pivot table to reflect these changes in your running total.
  4. Beware of Blank Cells: Blank cells in your data can interfere with running totals. Blank cells are often treated as zero in calculations, which can skew the results.
  5. Use “Show Values as Running Total”: To get the running total, use the “Show Values as Running Total” option in the pivot table settings, not the standard total.

Sample File

download

More Pivot Table Tutorials

⇠ Back to Pivot Table Tutorial

11 thoughts on “How to Add Running Total in a Pivot Table”

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

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

    Reply

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

    Reply

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

    Reply

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

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

Leave a Comment