Search Contextures Sites

Excel Pivot Table Tutorial -- Grand Totals

Show Grand Total at Top  Show Multiple Grand Totals   Pivot Table Tutorial List

Amazon.com

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 pivot table tutorial you can use another field that acts as the grand total, and display it at the top. Note: The instructions and sample file are for Excel 2003 and earlier. Download the zipped sample file for this pivot table tutorial 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 In every row of the source data, for the GT field, enter: Grand Total or leave the column blank, except for the heading. Add this field to the pivot table, as the first field in the Row area Change the Field Settings In the pivot table, right-click on the GT field button, and click Field Settings Click the Layout button Click Show Items in Outline Form Add a check mark to Display Subtotals at Top of Group Click OK, twice.   Hide the original Grand Total Right-click on a cell in the Pivot Table Click Table Options Remove the check mark for Grand Totals for Columns Click OK

Show Multiple Grand Totals There's no setting that allows you to display multiple grand totals in a pivot table. However, with the technique in this pivot table tutorial you can use another field that acts as the grand total, and display multiple Grand Totals, such as the Sum and Average overall. Note: The instructions and sample file are for Excel 2003 and earlier. Download the zipped sample file for this pivot table tutorial
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 In every row of the source data, for the GT field, enter: Grand Total or leave the column blank, except for the heading. Add this field to the pivot table, as the first field in the Row area.
Change the Field Settings In the pivot table, right-click on the GT field button, and click Field Settings Click the Layout button Click Show Items in Tabular Form Click OK.
Select Multiple Functions Under Subtotals, 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 a cell in the Pivot Table Click Table Options Remove the check mark for Grand Totals for Columns Click OK Download the zipped sample file for this pivot table tutorial

 

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

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