Excel -- Pivot Tables -- Running Totals

Search Contextures Sites

Excel -- Pivot Tables -- Running Totals

Set up the Pivot Table

Add a Custom Calculation

Change the Base Field

Running Totals with Multiple Row Fields

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.

To view the steps in a short video, click here

Amazon.com

Amazon.com

Set up the Pivot Table

At the right 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.

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

Add a Custom Calculation

To calculate a running total of units sold, for each Product, over the three months, we'll change the Units to a custom calculation.

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.

Change the Base Field

Because you chose Date as the base field, each Product column shows a running total for the year, by month.

If you select Product as the base field, the running total accumulates across the pivot table, in each month row, as shown in the pivot table at the right.

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.

Running Totals with Multiple Row Fields

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 our original pivot table, we could move the Product field to the row area, as you can see in the pivot table at the right. All the original amounts are still shown, but they're all in the same column.

At right is the pivot table as it looks before we add the running totals.

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 at the right.

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.

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 at the right.

In the February section, you can see that 326 units of the first product were sold. In the next row, you can see that 345 units were sold, which includes the B306 units.

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.

1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing
10. Pivot Tables -- Custom Calculations
11. Pivot Tables -- Pivot Cache
12. Pivot Tables -- Protection
13. Pivot Tables -- Grand Totals
14. Pivot Tables -- Running Totals

       Home     Excel Tips     Excel Files      Blog    Contact

The Excel Store

Debra Dalgleish

Last updated: November 25, 2008 8:52 PM

Set up the Pivot Table At the right 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. Currently, there are only Normal calculations in the pivot table, no custom calculations.
Add a Custom Calculation To calculate a running total of units sold, for each Product, over the three months, we'll change the Units to a custom calculation. 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.
Change the Base Field Because you chose Date as the base field, each Product column shows a running total for the year, by month. If you select Product as the base field, the running total accumulates across the pivot table, in each month row, as shown in the pivot table at the right. 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.
Running Totals with Multiple Row Fields 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 our original pivot table, we could move the Product field to the row area, as you can see in the pivot table at the right. All the original amounts are still shown, but they're all in the same column. At right is the pivot table as it looks before we add the running totals.
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 at the right. 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.
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 at the right. In the February section, you can see that 326 units of the first product were sold. In the next row, you can see that 345 units were sold, which includes the B306 units. 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.