How to Add Grand Total to Stacked Column Pivot Chart: 4 Methods

Method 1 – Input Basic Particular

Demonstrate how to add the grand total to a pivot chart stacked column in Excel. Introduce our Excel dataset to give you a better idea of what we’re trying to accomplish in this article. The following dataset represents the quarterly sales for three regions of a company.

Input basic Particular to Add Grand Total to Stacked Column Pivot Chart


Method 2 – Insert Stacked Column Pivot Chart

  • Select any cell from the data range.
  • Go to the Insert tab and select Pivot Table.
  • Select From Table/Range from the drop-down list.

Insert Stacked column pivot chart to Add Grand Total

  • A dialog box will appear like the following image. Excel will automatically select the data for you. For the new pivot table, the default location will be a New Worksheet.
  • Click OK.

  • A new worksheet will open up with PivotTable Fields.
  • Mark the quarters and Region options as shown below.

getting the pivot table

  • See that a Pivot Table has been created as in the image below.

  • Iinsert a stacked column chart for the pivot table.
  • Select any cell from the pivot table.
  • In the Insert tab, click on the drop-down arrow of the Insert Column or Bar Chart from the Charts group.
  • Choose the Stacked Column Chart.

insert stacked column chart

  • The Stacked Column Pivot Chart is shown below. In each column, the sum for each quarter is displayed in different colors. This graphical representation makes it easy to understand the difference between the quarter sums at a glance.

  • Aadd graph elements. In Quick Elements, some elements are already added or removed. You can manually edit the graph to add or remove any elements from the chart using the Add Chart Element option.
  • After clicking the Add Chart Element, you will see a list of elements.
  • Click on them individually to add, remove, or edit them.
  • Find the list of chart elements by clicking the Plus (+) button from the right corner of the chart, as shown below.
  • Mark the elements to add or unmark the elements to remove.
  • Find an arrow on the element, where you will find other options to edit the element.

add chart element


Method 3 – Evaluate Grand Total

  • To calculate the Grand Total, we have to type the following formula.

="Grand Total :" & TEXT(GETPIVOTDATA("Sum of Q2",$A$3)+GETPIVOTDATA("Sum of Q1",$A$3)+GETPIVOTDATA("Sum of Q3",$A$3)+GETPIVOTDATA("Sum of Q4",$A$3),"$#,###")

  • Press Enter.
  • Get the following Grand Total for every region.

evaluate grand total to add in stacked column pivot chart

How Does the Formula Work?

  • GETPIVOTDATA(“Sum of Q2”,$A$3)+GETPIVOTDATA(“Sum of Q1”,$A$3)+GETPIVOTDATA(“Sum of Q3”,$A$3)+GETPIVOTDATA(“Sum of Q4”,$A$3)

This formula will take the grand total of the quarter data from the pivot table and sum those quarters to get the value of 389,000.

  • “Grand Total :” & TEXT(GETPIVOTDATA(“Sum of Q2”,$A$3)+GETPIVOTDATA(“Sum of Q1”,$A$3)+GETPIVOTDATA(“Sum of Q3”,$A$3)+GETPIVOTDATA(“Sum of Q4″,$A$3),”$#,###”)

The TEXT  function converts a value to a specified number format, and the format is “$#,###” which indicates the currency format in the dollar. The Ampersand operator joins the text string and returns the output as Grand Total: $389,000.


Method 4 – Add Grand Total to Stacked Column Pivot Chart

  • Select the chart.
  • Go to the Format tab and select Text Box from the Insert Shapes.

Add Grand Total to Stacked Column Pivot Chart

  • After selecting Text Box, draw it on the chart as shown below. Type the following into the text box.

=Sheet4!$G$3

  • Add the grand total to the stacked column pivot chart as shown below.

showing grand total in stacked column pivot chart

  • Modify the chart style, select Design, and select your desired Style 8 option from the Chart Styles group.
  • Or right-click on the chart, select the Chart Styles icon, and select your desired style as shown below.

  • You will get the following chart.

show the final chart

  • Add a slicer for customization purposes.
  • Go to the PivotTableAnalyze and select Insert Slicer.

insert slicer in chart

  • When the Insert Slicers dialog box appears, check the Region section.

  • Get the following output.
  • Customize our chart based on a slicer for our visualization analysis.

add grand total in stacked column pivot chart with slicer

  • Below is an example of the Grand Total amount we will receive if we select the Canada region.


Things to Remember

✎ Select anywhere in the pivot table before inserting a stacked column graph. Otherwise, rows and columns will need to be manually added.

✎ By default, the pivot table will always sort the information in alphabetically ascending order. To reorder the information, you must use the sort option.

✎ Whenever you are creating a pivot table, choose New Worksheet. If you choose Existing Worksheet, a pivot table containing the data will be created in your existing sheet. created in your existing sheet that contains the If we create the pivot table in our current worksheet, there is a significant chance that the data will be distorted.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


Related Articles


<< Go Back to Pivot Chart | Pivot Table in Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo