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.

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.

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

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.

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.

- Right-click one of the cells in the Values area, and click Show Values as
- Click
*Running Total in* - From the
*Base field*list, choose*Date* - 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..

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.

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.

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.

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:

- Add a column to the source data, with the heading YearMonth.
- 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")
- Refresh the pivot table, add the YearMonth field to the Row Labels area, and then remove the Date and Year fields.
- Create a running total with YearMonth as the base field.

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.

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,

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

- For the basic Running Total tutorial, download the pivotsalesrunningtotal.zip file, which is in xlsx format, and does not contain macros.
- For the Year to Year Running Total tutorial, Click here to download the sample file. The zipped file is in xlsx file format, and does not contain macros.

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

- copy the formatting from one pivot table, and apply it to another pivot table.
- change all the values from Count to Sum
- remove the "Sum of" from all the headings

and much more!

Last updated: November 14, 2017 12:34 PM