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

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

   

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.

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.

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

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.

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.

Video: % Running Total and Rank

In an Excel pivot table, you can use the % Running Total calculation, to show the current running total amount, divided by the grand total. For example, combine this calculation with the Rank calculation, to see what percentage of total sales come from the top 3 products.

Watch this video to see the steps, and there is another % Running Total video below.

To follow along with this video, in the download section, get the file, "Food Sales % Running Total"

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,

Get the Sample Files

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

Running Total: For the basic Running Total tutorial, get the pivotsalesrunningtotal.zip file, which is in xlsx format, and does not contain macros.

Year to Year: For the Year to Year Running Total tutorial, Click here to get the sample file. The zipped file is in xlsx file format, and does not contain macros.

Food Sales % Running Total: Click here to get the food sales data file, that was used in the Percent Running Total video. The zipped Excel file is in xlsx format, and does not contain any macros.