Contextures

Pivot Table Running Totals

With a running total in a pivot table, you can see how amounts accumulate over a period of time, or through a range of products.



Create Running Totals in Pivot Table

To create a running total, use the Custom Calculation feature in a pivot table. In this pivot table tutorial, we'll focus on the Running Total custom calculation.

In Excel 2010 and later versions, you can also use the % Running Total calculation, to show the current running total amount, divided by the grand total.

Video: Create Running Totals

Watch this video to see the steps for creating a pivot table in Excel 2013, then changing the values to show a running total.

Set up the Pivot Table

Below is a pivot table which contains monthly sales figures for 3 customers. In the pivot table, Date has been grouped by year and month.

Year and Customer are in the Row area, Month is in the Column area, and Quantity (Qty) is in the Values area..

pivot table without running totals

We can see the Grand Total for each month, and for each Customer.

Currently, there are only Normal calculations in the pivot table, no custom calculations.

Add a Custom Calculation

To calculate a running total of quantity, for each Customer, over the three months, we'll change the Qty to a custom calculation.

We'll base the running total on the Date field, so the totals accumulate across the Month columns.

  1. Right-click one of the cells in the Values area, and click Show Values as
  2. Click Running Total in
  3. From the Base field list, choose Date
  4. Click the OK button

    pivot table without running totals

Note: If you select a base field that isn't in the row or column area, all the results will show an #N/A error. Also, if there's an error in any month's results, it will carry down through the remaining months.

running total by month

You can now see that there were 2,595 units sold to Corner Cabin, by the end of March, in 2014.

The Grand Total for Columns has been turned on, and Grand Total for Rows is turned off.

The Grand Total shows that 17,764 units were sold by the end of April, for all Customers, over both years..

Change the Base Field

In the previous pivot table layout, we chose Date as the base field, and each Month column shows a running total for the year and customer, by month.

If you change the Base field, and select Customer instead, the running total accumulates down the Month columns in the pivot table. There is a separate running total for each month, and it restarts at the change in year, as shown in the pivot table below.

The results are correct, but this base field is not as easily understod as the Date base field. It is more common to see totals accrue over time.

The Grand Total for Rows has been turned on, and Grand Total for Columns is turned off.

running total by customer

In the April column, you can see that 1,979 units were sold in 2014, after Food Franchise ordered.

Running Totals with Multiple Row Fields

You can use running totals in pivot tables with a single field in the Row area, or multiple fields. The base field can be in the Column area, so the running total goes across, or the base field can be in the Row area, so the running total goes down.

You can experiment with the field arrangement, to find the layout that is easiest to understand.

In the previous example, the Year and Customer fields were in the row area.

In the pivot table below, Year has been moved to the Report Filter area, and a single year is selected. Customer is in the Row area, and Month is in the Column area.

The Qty field shows a running total, based on Month, and the report is compact and easy to read

running total by date across

In the next pivot table, Month has been moved to the Row area, and the Qty field still shows a running total, based on Month.

This layout takes more room, and it is more difficult to compare the customers and months.

running total by date down

Video: Running Total Year to Year

To create a running total that continues from one year to the next, you can add a new field in the source data, and use it as the base field. Watch this video to see the steps, and the written instructions are below the video.

Running Total Year to Year

If dates in a pivot table are grouped by year and month, the running total will stop at the end of each year, and then begin again at the start of the next year. There is no setting you can adjust to change this behavior.

To create a running total that continues from one year to the next, you can follow these steps to create a new field in the source data, and then use it as the base for the running total:

  1. Add a column to the source data, with the heading YearMonth.
  2. Enter a formula to return the year and month of the date in each row. For example, use this formula if the date is in Column A: =TEXT(A2, "yyyy-mm")
  3. Refresh the pivot table, add the YearMonth field to the Row Labels area, and then remove the Date and Year fields.
  4. Create a running total with YearMonth as the base field.

running totals in pivot table

Video: % Running Total In

In Excel 2010 and later versions, you can use the % Running Total calculation, to show the current running total amount, divided by the grand total. Watch this video to see the steps, and the written instructions are below the video.

% Running Total In

In Excel 2010 and later versions, you can use the % Running Total calculation, to show the current running total amount, divided by the grand total.

In the pivot table shown below, there are three Value fields in the pivot table, showing the Sum of Qty sold in each month.

  • In column C, the sum is shown, with no calculation. This is the number of units sold on each date listed.
  • In column D, the sum is shown, as a Running Total for Date. This is the total units sold, up to and including each date.
  • In column E, the sum is shown, as % Running Total for Date (new in Excel 2010). This is the total units sold, up to and including each date, divided by the grand total of units sold.

By June, a running total of 11426 units have been sold, and the % Running Total is 44% of the 26044 overall total units sold. To check the calculation, a formula is entered in cell G9,

running totals in pivot table

Download the Sample Files

To follow along with the Running Totals tutorial, you can download the sample files

 

 

 

 

 

 

 

 

 

 

 

 

 


Last updated: May 14, 2016 1:43 PM