Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Pivot Table Grand Totals Show Grand Total at Top  Video: Grand Total at Top Show Multiple Grand Totals   Video: Show Multiple Grand Totals Download the Sample Files Pivot Table Tutorial List Show Grand Total at the Top of a Pivot Table There's no setting that allows you to display the grand total at the top of an Excel pivot table. However, with the technique in this tutorial you can use another field that acts as the grand total, and display it at the top. Create the fake "Grand Total" field In the source data, add a column with the heading GT, or use a space character as the column heading. Note: If your source data is in a named Excel table, you cannot enter a formula in the heading row. If you are not using a named table, use a formula to create a blank space, as shown in the screen shot below: =" " In every row of the source data, for the GT field, enter: Grand Total, or leave the column blank, except for the heading. Refresh the pivot table, and add the grand total field, as the first field in the Row area Change the Field Settings After you add the Grand Total (GT) field, change its settings so the amounts show at the top. In Excel 2007 and Excel 2010: Select a cell in the pivot table, and in the Excel Ribbon, under PivotTable Tools, click the Design tab. Click Report Layout, and select Compact Form or Outline Form. (In Tabular Form, subtotals are only shown at the bottom.) Click Subtotals, and click Show all Subtotals at Top of Group In Excel 2003: In the pivot table, right-click on the GT field button, and click Field Settings Click the Layout button, and click Show Items in Outline Form. (In Tabular Form, subtotals are only shown at the bottom.) Add a check mark to Display Subtotals at Top of Group Click OK, twice. Hide the original Grand Total With the new Grand Total at the top, you can turn off the default grand total at the bottom. In Excel 2007 and Excel 2010: Select a cell in the pivot table, and in the Excel Ribbon, under PivotTable Tools, click the Design tab. Click Grand Totals, and click On for Rows Only In Excel 2003: Right-click on a cell in the Pivot Table, and click Table Options Remove the check mark for Grand Totals for Columns Click OK Video: Grand Total at Top This short video shows the steps for creating a grand total at the top of the pivot table. Show Multiple Grand Totals In a pivot table, you can show multiple subtotals per field, but there's no setting that allows you to show multiple grand totals in a pivot table. As a workaround, you can use another field that acts as the grand total, and display multiple Grand Totals, such as the Sum and Average overall. To create this workaround, follow the steps below, to: add a new field to the source data, put it into the pivot table, and remove the existing Grand Total. Add a "Grand Total" field In this example, the source data is a formatted Excel table, on another sheet in the pivot table's workbook. In the source data, insert a new column, and change the column heading to "Grand Total" Leave the column blank, except for the heading. Refresh the pivot table, so the new field appears in the Field List Add the Grand Total field to the pivot table, as the first field in the Row area. The field label shows "(blank)", so select the label cell, and type a space character. Show Subtotals for New Field In the pivot table, right-click on the new field's label cell, and click Subtotal "Grand Totals" To show the subtotals at the bottom of the pivot table, click the Design tab on the Ribbon. Then, click the Subtotals button, and click Show all Subtotals at Bottom of Group Select Multiple Functions In the pivot table, right-click on the new field's label cell, and click Field Settings Under Subtotals, click Custom, and then select the summary functions that you want for the multiple subtotals, e.g. Sum and Average. Click OK Hide the original Grand Total Right-click on the Grand Total label cell at the bottom of the Pivot Table Click Remove Grand Total Video: Show Multiple Grand Totals Use a workaround to show multiple grand totals in a pivot table. Watch this video to see the steps, and written instructions are above. Download the Sample Files Download the zipped Excel 2003 Grand Totals at Top sample file Download the zipped Excel 2007/2010 Grand Totals at Top sample file Download the zipped Multiple Grand Totals sample file (xlsx file format)

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

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