Trick To Show Excel Pivot Table Grand Total At Top - Contextures

Home > Pivot > Layout > Grand Totals Pivot Table Grand Totals Top or Left Workaround to show a grand total at the top of a pivot table, or at the left side. Or, use a workaround to show multiple grand totals in a pivot table. Also, see how to add, remove or change pivot table grand totals.

Show Grand Total at Top

Video: Grand Total at Top

Show Grand Totals at Left

Get the Sample Files

Pivot Table Tutorials

Show Grand Total at Top

There's no setting that allows you to display the grand total at the top of an Excel pivot table. However, with the quick trick in this tutorial, you can use another field that acts as the grand total, and display it at the top.

• Note: This quick trick does not work for pivot tables that use the Tabular Report Layout. That layout only allows subtotals at the bottom of the group.

This short video shows the steps for creating a grand total at the top of the pivot table, and the written instructions are below the video.

• ✅ To follow along, get the Excel sample file in the download section

Create New "Grand Total" field

Follow these steps, to add a new field to the source data for the pivot table.

1. In the source data, add a column with the heading GT, or use a space character as the column heading.
2. In the first data row, enter the following formula in the new column:
• ="Grand Total"
3. Press the Enter key, and the formula will automatically fill down to the rest of the rows in the source data.

Add New Field to Pivot Table

Follow these steps, to add the new field to the pivot table.

• Right-click on any cell in the pivot table, and click on the Refresh command in the pop-up menu.
• Next, in the PivotTable Field List, drag the new GT field to the Row area, above any existing fields in the row area
• In the pivot table, the Grand Total field will appear at the top

Change Field Settings

After you add the Grand Total (GT) field, change its settings so the total amounts show at the top.

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. Next, click the Subtotals command, and click Show all Subtotals at Top of Group

Hide original Grand Total

With the new Grand Total at the top, you can turn off the default grand total at the bottom.

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

Show Grand Totals at Left

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.

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.

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

Get the Sample Files

• Grand Totals at the Top:
  • Completed: Click this link to get the completed file, with the grand totals at the top. The zipped file is in xlsx file format, and does not contain macros.
  • Starter: Click this link to get the starter file, with the grand totals at the bottom. Use it to follow along with the instructions in the video. The zipped file is in xlsx file format, and does not contain macros.
• Multiple Grand Totals: Click this link to download the zipped sample file in xlsx file format. The file does not contain macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Pivot Table Grand Totals

FAQs - Pivot Tables

Multiple Grand Totals

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table