If you are looking for some special tricks to know how to add grand total to a pivot chart stacked column in Excel, you’ve come to the right place. There is one way to add grand total to a pivot chart stacked column in Excel. This article will discuss every step of this method to add the grand total to a pivot chart stacked column. Let’s follow the complete guide to learn all of this.
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.
Step-by-Step Procedure to Add Grand Total to Stacked Column Pivot Chart in Excel
In the following section, we will use one effective and tricky method to add the grand total to a pivot chart stacked column in Excel. As a first step, we create a pivot table for our dataset, then create a stacked column chart, and finally, display the grand total. This section provides extensive details on this method. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
Step 1: Input Basic Particular
Here, we are going to demonstrate how to add the grand total to a pivot chart stacked column in Excel. Our Excel dataset will be introduced 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.
Read More: How to Use Excel Formula to Calculate Percentage of Grand Total
Step 2: Insert Stacked Column Pivot Chart
In this step, we are going to insert a stacked column pivot chart. To do this, first, we have to insert a pivot table. After that, we will insert a stacked column pivot chart. Let’s walk through the following steps to insert a stacked column pivot chart.
- First of all, select any cell from the data range.
- Then, go to the Insert tab and select Pivot Table.
- Now, select From Table/Range from the drop-down list.
- Soon after, 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.
- Next, click on OK.
- After a while, a new worksheet will open up with PivotTable Fields.
- Next, mark the quarters and Region options as shown below.
- Soon you will see that a Pivot Table has been created as in the image below.
- Now, we are going to insert a stacked column chart for the pivot table.
- First of all, select any cell from the pivot table.
- Now, in the Insert tab, click on the drop-down arrow of the Insert Column or Bar Chart from the Charts group.
- Then, choose the Stacked Column Chart.
- The Stacked Column Pivot Chart is shown below. In each column, there is a sum for each quarter displayed in different colors. This graphical representation makes it easy to understand the difference between the sums of quarters at a glance.
- Now, we are going to add graph elements. In Quick Elements, some elements are already added or removed. But you can manually edit the graph to add or remove any elements from the chart by using the Add Chart Element option.
- After clicking on the Add Chart Element, you will see a list of elements.
- Next, you have to click on them one by one to add, remove, or edit them.
- Alternatively, you can find the list of chart elements by clicking the Plus (+) button from the right corner of the chart as shown below.
- Here, you have to mark the elements to add or unmark the elements to remove.
- You will find an arrow on the element, where you will find other options to edit the element.
Read More: How to Show Grand Total with Secondary Axis in Pivot Chart
Step 3: Evaluate Grand Total
In this step, we will evaluate the grand total to be added in a stacked column pivot chart. Here, we will use the TEXT function. To do this, follow the following process.
- First of all, 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),"$#,###")
- Then, press Enter.
- Consequently, you will get the following Grand Total for every region.
🔎 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)
From the pivot table, this formula will get the grand total of the quarter data 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),”$#,###”)
In this formula, the TEXT function converts a value to a specified number format, and the format is “$#,###” which indicates the currency format in the dollar. Then, the Ampersand operator joins the string of text and returns the output as Grand Total: $389,000.
Read More: How to Make Subtotal and Grand Total in Excel (4 Methods)
Step 4: Add Grand Total to Stacked Column Pivot Chart
Now, we are going to show how to add the grand total to the stacked column pivot chart. Let’s walk through the following steps to add the grand total to the stacked column pivot chart.
- First of all, select the chart.
- Then, go to the Format tab and select Text Box from the Insert Shapes.
- After selecting Text Box, draw it on the chart as shown below. Type the following into the text box.
- Finally, you will be able to add the grand total to the stacked column pivot chart as shown below.
- Next, to modify the chart style, select Design and then select your desired Style 8 option from the Chart Styles group.
- Or you can right-click on the chart, select the Chart Styles icon, and select your desired style as shown below.
- Therefore, you will get the following chart.
- Now, are going to add a slicer for customization purposes.
- To do this, go to the PivotTableAnalyze and select Insert Slicer.
- When the Insert Slicers dialog box appears, check the Region section.
- Therefore, you will get the following output.
- Now, we will easily customize our chart based on a slicer for our visualization analysis.
- Below is an example of the Grand Total amount we will receive if we select the Canada region.
Read More: How to Add Grand Total to Bar Chart in Excel (With Easy Steps)
💬 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. In order to reorder the information, you must use the sort option.
✎ Whenever you are creating a pivot table, choose New Worksheet. A pivot table containing the data will be created in your existing sheet if you choose Existing Worksheet. 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.
That’s the end of today’s session. I strongly believe that from now you may be able to add grand total to a pivot chart stacked column in Excel. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!