How to Show Grand Total in Pivot Table (3 Easy Methods)

If you want to show Grand Total in Pivot Table, you have come to the right place. Here, we will walk you through 3 easy methods to do the task smoothly.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


3 Methods to Show Grand Total in Pivot Table

The following dataset has the Product, Sales, and Profit columns. Using this dataset we will insert a Pivot Table. After that, we will go through 3 methods to show grand total in Pivot Table.

Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.

How to Show Grand Total in Pivot Table


1. Using Grand Totals Feature in Pivot Table

In this method, we will use the Grand Total feature to show Grand Total in Pivot Table. Here, we add a Year column in the dataset. The Year column contains 2 types of years. Along with that, the Product column has 3 types of products.

Using Grand Totals Feature in Pivot Table

Let’s go through the following steps to do the task.

Step-1: Inserting Pivot Table

In this step, we will insert a Pivot Table.

  • First of all, we will select the entire dataset.

Here, you can select the entire dataset by clicking on cell B4 and pressing the CTRL+SHIFT+Down arrow.

  • After that, go to the Insert tab.
  • Then, from the PivotTable group >> select From Table/Range.

Inserting Pivot Table to Show Grand Total in Excel

At this point, a PivotTable from table or range dialog box will pop up.

  • Afterward, select New Worksheet >> click OK.

As a result, you can see PivotTable Fields in a different worksheet.

  • Furthermore, we will mark the Product >> drag it to the Rows group.
  • Along with that, we will mark the Sales >> drag it to the Values group.
  • In addition, we mark the Year >> drag it to the Column group.

Here, one thing must be noted, we must drag the Year to the Column group.

As a result, you can see the created Pivot Table.

Step-2: Use of Grand Totals Feature

In the above Pivot Table, the Grand Total has been created automatically.

However, if the Pivot Table looks like the following picture where the Grand Total for rows and columns is missing, we have to use the Grand Totals feature.

Use of Grand Totals Feature in Excel Pivot Table

  • In the beginning, we will click on any of the cells of the Pivot Table.
  • After that, from the Design tab >> select Grand Totals.
  • Afterward, select the on for Rows and Columns option.

Applying Grand Total Feature to Show Grand Total in Pivot Table

Therefore, you can see the Pivot Table is showing Grand Total for rows and columns.

Read More: How to Use Excel Formula to Calculate Percentage of Grand Total


2. Showing Grand Total on Top of Pivot Table

The following dataset has the Product, Sales, and Profit columns. Using this dataset we will insert a Pivot Table. After that, we will show grand total on top of the Pivot Table.

Showing Grand Total on Top of Pivot Table

Let’s go through the following steps to do the task.

Step-1: Inserting Pivot Table

In this step, we will insert a Pivot Table.

  • Here, we have created the Pivot Table by following  Step-1 of Method-1.
  • One thing must be noted, in the PivotTable Fields, we mark the Product >> drag it to the Rows group.
  • we select the Sales and Profit >> drag them to the Values group.

As a  result, you can see the Pivot Table.

In the Pivot Table, you can easily notice that the Grand Total is at the bottom of the Pivot Table.

Next, we will show you how you can show the Grand Total on the top of the Pivot Table.

Step-2: Adding Grand Total Column in Source Data

In this step, we will add a column in the source data of the Pivot Table.

  • In the beginning, we will select Column C >> right-click on it.
  • After that, we will select Insert from the Context Menu.

Therefore, you can see a new column in the Dataset.

  • Furthermore, we will name the column as the Grand Total.

Here, we will leave the column Grand Total empty.

Adding Grand Total Column to Show Grand Total in Pivot Table

Step-3: Showing Grand Total on Top of Pivot Table

In this step, we will show grand total on top of the Pivot Table.

  • First, we will go back to our Pivot Table.
  • Later, we will right-click on any cell of the Pivot Table >> select Refresh from the Context Menu.

Refreshing Pivot Table to Update Grand Total in Excel

 As a result, you can see Grand Total in the PivotTable Fields.

  • Afterward, we will select Grand Total >> drag it in the Rows group above the Product.

Therefore, you can see the blank in cell A4.

Along with that, the grand total of Sales is present in cell B4 and grand total of Profit is present in cell C4.

  • Furthermore, we will click on cell A4 and press the Space bar of the Keyboard.

  • Next, we will type Grand Total in cell A4.

Therefore, Grand Total is now showing at the top of the Pivot Table.

Moreover, we do not want Grand Total at the bottom of the Pivot Table.

  • Therefore, we will right-click on the Grand Total of cell A11.
  • Then, we will select Remove Grand Total from the Context Menu.

Removing Grand Total from Bottom to Show Grand Total

Hence, you can see the Grand Total at the Top of the Pivot Table.

Read More: How to Remove Grand Total from Pivot Table (4 Quick Ways)


Similar Readings


3. Showing Grand Totals in Pivot Table Chart

In this method, using the following dataset, we will insert a Pivot Table. After that, we will insert a Column chart using the Pivot Table.

Next, we will show Grand Total in the chart created from Pivot Table.

Showing Grand Totals in Pivot Table Chart

Let’s go through the following steps to do the task.

Step-1: Inserting Pivot Table

In this step, we will insert a Pivot Table.

  • Here, we have created the Pivot Table by following  Step-1 of Method-1.
  • One thing must be noted, in the PivotTable Fields, we mark the Product >> drag it to the Rows group.
  • Along with that, we select the Profit >> drag it to the Values group.

As a  result, you can see the Pivot Table.

Step-2: Inserting Column Chart

In this step, we will insert a Column chart.

  • First of all, we will select cells A4:B9.
  • Then, go to the Insert tab.
  • Next, from the Insert Column or Bar Chart group >> we will select 2D Clustered Column chart.

As a result, you can see the Column chart.

  • Furthermore, we edited the Chart Title to Product and Sales.

Step-3: Adding Grand Total to Chart

In this step, we will add Grand Total to the chart.

  • First of all, in cell D4, we will type the following formula.
="Grand Total: "&TEXT(GETPIVOTDATA("Sales",$A$3),"$#,###")

Formula Breakdown

  • TEXT(GETPIVOTDATA(“Sales”,$A$3),”$#,###”) → the TEXT function is used to add $ sign before Grand Total.
    • Output: $80,000
  • “Grand Total: “&TEXT(GETPIVOTDATA(“Sales”,$A$3),”$#,###”) → The ampersand & is used to join “Grand Total: “ with $80,000.
    • Output: Grand Total: $80,000
  • Next, Press ENTER.

Therefore, you can see the result in cell D4.

Furthermore, we will add the Grand Total to the Chart.

  • To do so, we will click on the Chart >> go to the Insert tab.
  • After that, from the Illustration group >> select Shapes.

Calculating Grand Total in Excel Pivot Chart

At this point, a huge number of Shapes will appear.

  • Then, we will select Text Box.

  • Furthermore, we will insert the Text Box in the chart under the Chart Title.
  • Moreover, in the Formula Bar, we will type the following formula.
='Pivot Table Chart'!$D$4

  •  Then, press ENTER.

As a result, you can see the Grand Total in the chart.

After that, we will remove some products from the Grand Total column.

  • To do so, we will click on the drop-down arrow of the Row Labels column.
  • Afterward, we will unmark the Printer and Mouse.
  • Then, click OK.

Using Filter to Show Grand Total

At this point, you can easily notice that the Grand Total has changed in the Chart.

Read More: [Fixed!] Pivot Table Grand Total Column Not Showing (6 Solutions)


Practice Section

You can download the above Excel file to practice the explained methods.


Conclusion

Here, we tried to show you 3 easy methods to show grand total in Pivot Table. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo