Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy     Amazon.com	Pivot Table Running Totals 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. Set up the Pivot Table 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. 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. 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. Change the Base Field 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. 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 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. % Running Total In 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. Video: Create Running Totals 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 Tutorials Pivot Table Introduction  Clear Old Items in Pivot Table Create a Pivot Table in Excel 2007  Custom Calculations  Data Field Layout Dynamic Data Source FAQs - Pivot Tables Field Settings Filter Source Data   Filters, Top 10  Filters, Report Filters GetPivotData Function Grand Totals Grouping Data Layout, Excel 2007 Multiple Consolidation Ranges Pivot Cache    PivotTable Style Printing a Pivot Table    Protection   Running Totals   Show and Hide Items  Sorting Subtotals  Summary Functions Unique Item Count

Pivot Table Books Beginning Pivot Tables (Excel 2007)  Pivot Tables Recipe Book (Excel 2003)  Pivot Tables Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Power  Pivot Play PLUS  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 GetPivotData Group Data Layout, Excel 2007 Report Filters, Add Running Totals Select Sections Subtotals, Create Multiple Top 10 Filters

 

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