Search Contextures Sites

Excel Pivot Table Tutorial -- Running Totals
Set up the Pivot Table Add a Custom Calculation Change the Base Field Running Totals with Multiple Row Fields 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. To view the steps in a short pivot table tutorial 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 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 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. To view the steps in a short pivot table tutorial video, click here

 

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2010 All rights reserved.	Debra Dalgleish