Last updated: March 10, 2013 10:26 AM
Search Contextures Sites
- Set up the Pivot Table
- Add a Custom Calculation
- Change the Base Field
- Running Totals with Multiple Row Fields
- % Running Total In
- Video: Create Running Totals
- Pivot Table Tutorial List
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.
Below is a pivot table which contains monthly sales figures for several products. In the pivot table, Date is in the row area, grouped by month. Product is in the column area, and Units sold, shown as Sum of Units, is in the data area.
We can see the Grand Total for each month, and for each product code.
To calculate a running total of units sold, for each Product, over the three months, we'll change the Units to a custom calculation.
We'll base the running total on the Date field, so the totals accumulate down the list of dates.
- Right-click one of the cells in the Data area, and select Field Settings...
- In the Field Settings dialog box, type a name for the field, e.g. Sales
- Click the Options button, to expand the dialog box
- From the Show data as dropdown list, select 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 621 units of the A703 product sold by the end of February. The Grand Total column shows that 1355 units, of all products, were sold by the end of March.
In the previous pivot table layout, we chose Date as the base field, and each Product column shows a running total for the year, by month.
If you change the Base field, and select Product instead, the running total accumulates across the product columns in the pivot table. There is a separate running total in each month row, as shown in the pivot table below.
In the February row, you can see that 326 units of the first product were sold. In the next column, you can see that 345 units were sold, which includes the B306 units.
For pivot tables with multiple fields in the row area, the running totals work the same way, but may be harder to follow as the layout becomes more complex.
For example, in the original pivot table in this pivot table tutorial, we could move the Product field to the row area, as you can see in the pivot table below. All the original amounts are still shown, but they're all in the same column.
Below is the pivot table as it looks before we add the running totals.
Running Total by Date
When we add the Running Total custom calculation, with Product moved to the row area, the running total amounts are the same but are arranged vertically, as shown below.
In the February section you can see that 621 units of the A703 product sold by the end of that month. The February total shows the running total for all products, at the end of that month.
Running Total by Product
If you change the Base Field to Product, instead of Date, the units are totalled for each month. Because the Running Total is by Product, the month totals are blank. The last product in each month shows that month's total units sold.
It's very difficult to understand the data in this layout, so be sure to label the pivot table headings, or add a title to explain it.
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 Units sold on each date.
- In column B, the sum is shown, with no calculation. This is the number of units sold on each date listed.
- In column C, the sum is shown, as a Running Total for Date. This is the total units sold, up to and including each date.
- In column D, 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 November 1st, a running total of 399 units have been sold, and the % Running Total is 18.8% of the 2121 overall total units sold.
To show running totals, you'll u se the Custom Calculation feature in Excel's pivot tables, as shown in this video tutorial. This video shows the steps in Excel 2003.
Pivot Table Introduction
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Filter Source Data
Filters, Top 10
Filters, Report Filters
Layout, Excel 2007
Multiple Consolidation Ranges
Printing a Pivot Table
Show and Hide Items
Unique Item Count
Pivot Table Books
Pivot Table Add-Ins
Pivot Table Videos
Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
Layout, Excel 2007
Report Filters, Add
Subtotals, Create Multiple
Top 10 Filters
Contextures Inc., Copyright ©2013
All rights reserved.