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.
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.
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:
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.
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
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:
and much more!
Last updated: November 14, 2017 12:34 PM