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.

Pivot Tables -- Introduction  Pivot Tables -- Create a Pivot Table in Excel 2007  Pivot Tables -- Data Field Layout Pivot Tables -- Show and Hide Items Pivot Tables -- Clear Old Items Pivot Tables -- Field Settings Pivot Tables -- GetPivotData Pivot Tables -- Grouping Data Pivot Tables -- Multiple Consolidation Ranges Pivot Tables -- Printing    Pivot Tables -- Custom Calculations  Pivot Tables -- Pivot Cache      Pivot Tables -- Protection   Pivot Tables -- Grand Totals Pivot Tables -- Running Totals   Pivot Tables -- Filter Source Data

Home     Excel Tips     Excel Files      Blog    Contact
Privacy Policy		The Excel Store	Debra Dalgleish