Search Contextures Sites

Contextures
Excel news
by email

 

 

 

 

 

 

 

 

 

Excel Pivot Table Grand Totals

  1. Show Grand Total at Top 
  2. Video: Grand Total at Top
  3. Show Grand Totals at the Left of a Pivot Table
  4. Show Multiple Grand Totals  
  5. Video: Show Multiple Grand Totals
  6. Download the Sample Files
  7. 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.

grand total top

Create the fake "Grand Total" field

  1. 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: =" "
  2. In every row of the source data, for the GT field, enter: Grand Total, or leave the column blank, except for the heading.
  3. Refresh the pivot table, and add the grand total field, as the first field in the Row area

grand total top heading

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:

  1. Select a cell in the pivot table, and in the Excel Ribbon, under PivotTable Tools, click the Design tab.
  2. Click Report Layout, and select Compact Form or Outline Form. (In Tabular Form, subtotals are only shown at the bottom.)
  3. Click Subtotals, and click Show all Subtotals at Top of Group

grand total top subtotals

In Excel 2003:

  1. In the pivot table, right-click on the GT field button, and click Field Settings
  2. Click the Layout button, and click Show Items in Outline Form. (In Tabular Form, subtotals are only shown at the bottom.)
  3. Add a check mark to Display Subtotals at Top of Group
  4. Click OK, twice.

grand total top outline

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:

  1. Select a cell in the pivot table, and in the Excel Ribbon, under PivotTable Tools, click the Design tab.
  2. Click Grand Totals, and click On for Rows Only

grand total top rows only

In Excel 2003:

  1. Right-click on a cell in the Pivot Table, and click Table Options
  2. Remove the check mark for Grand Totals for Columns
  3. 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 Grand Totals at the Left of a Pivot Table

There's no setting that allows you to display the grand total at the left side of an Excel pivot table, before all the other totals. However, you could use the following workaround, if you:

  • use Slicers to filter the pivot table
  • do not need to change the pivot table layout after setting it up.

In this technique, you'll create another copy of the pivot table, then hide columns in both pivot tables, so simulate grand totals at the left of the data.

grand total at left hide columns

To set up the grand totals at the left:

  1. Add Slicers to the pivot table, to filter the fields that you want filtered.
  2. Add a few columns to the left of the existing pivot table (enough columns for all the row fields and grand totals)
  3. Copy the existing pivot table, and paste it onto a blank sheet
  4. In the copy, remove any column fields, and leave just the row fields and grand totals.
  5. Move the copy to the pivot table sheet, to the left of the current pivot table.
  6. In the current pivot table, remove the Grand totals, and hde the column with the Row items.
  7. Leave one very narrow blank column between the two pivot tables, and hide or delete any other blank columns.

    grand total at left hide columns

Then, when you filter with a Slicer, both pivot tables will change, and you will see the grand totals at the left.

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.

grand total column

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.

  1. In the source data, insert a new column, and change the column heading to "Grand Total"
  2. Leave the column blank, except for the heading.
  3. grand total column

  4. Refresh the pivot table, so the new field appears in the Field List
  5. Add the Grand Total field to the pivot table, as the first field in the Row area.
  6. The field label shows "(blank)", so select the label cell, and type a space character.
  7. grand total in row area

Show Subtotals for New Field

  1. In the pivot table, right-click on the new field's label cell, and click Subtotal "Grand Totals"
  2. To show the subtotals at the bottom of the pivot table, click the Design tab on the Ribbon.
  3. Then, click the Subtotals button, and click Show all Subtotals at Bottom of Group

    grand total in row area

Select Multiple Functions

  1. In the pivot table, right-click on the new field's label cell, and click Field Settings
  2. Under Subtotals, click Custom, and then select the summary functions that you want for the multiple subtotals, e.g. Sum and Average.
  3. Click OK

grand total in row area

Hide the original Grand Total

  1. Right-click on the Grand Total label cell at the bottom of the Pivot Table
  2. 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

Xtreme Pivot Table Course

Pivot table skills are essential, if you want to be an Excel master. To raise your skills to the expert level, I recommend the Xtreme Pivot Table course, from John Michaloudis, at My Excel Online.

This course has more than 200 videos -- beginner, intermediate and advanced level -- along with practice workbooks, finance business cases, and 12 months of personal support. Each short lesson is clear, and easy to follow. Work through the lessons at your own pace, and track your progress. The course is an excellent value, at a surprisingly low price.

See the course details, and watch sample videos here: Xtreme Pivot Table Course.

More Pivot Table Resources

Tutorials:

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

 

Learn how to create Excel dashboards.

 

Last updated: November 2, 2014 3:39 PM