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.

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.

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.

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.

• 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.

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.

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.

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.

 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.

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

• Show Grand Total with Secondary Axis in Pivot Chart
• Use Excel TEXT Formula (4 Suitable Methods)
• How to Collapse the Table to Show the Grand Totals Only (5 Ways)
• Auto Generate Number Sequence in Excel (9 Examples)
• How to Use Format Function in Excel (with Suitable Examples)

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.

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.

• 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.

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.

•  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.

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

• How to Combine Text and Numbers in Excel and Keep Formatting
• Make Subtotal and Grand Total in Excel (4 Methods)
• How to Show Percentage in Legend in Excel Pie Chart (with Easy Steps)
• Add Grand Total to Stacked Column Pivot Chart
• How to Use FIXED Function in Excel (6 Suitable Examples)
• Add Grand Total to Bar Chart in Excel (With Easy Steps)
• How to Use TEXT Function to Format Codes in Excel