Microsoft Excel facilitates us with various types of charts. Among them, the Pie Chart, also known as a circular graph, is an excellent way to show the audience how much individual amounts contribute to the total. Users can easily visualize the scenario through Pie Chart. In this article, I will demonstrate every step of how you can show the total in an Excel Pie Chart.
In the following image, you can see the overview to show grand total in Excel PieChart. Let’s get into the main part of this article!
How to Show Total in Excel Pie Chart: 2 Effective Ways
To show you how you can easily show the total in your Excel Pie Chart, we have taken a dataset of 9 rows and 3 columns which are Rep Name, Item, and Units. In this article, we are going to use Excel 365 version. You can use any other version of Excel as well.
1. Using Text Box in Pie Chart to Show Total
First, I will show you how you simply create a Pie Chart in Excel according to our taken dataset. To do so follow the below steps.
Step 1: Inserting Pie Chart
In this step, we are going to insert a PieChart in our worksheet according to our preferred data range.
- First of all, select the preferred range for which you want to create the Pie Chart. In this dataset, C5:D12 is the preferred range that we are creating the Pie Chart for.
- Then, go to the Insert tab and click on Pie Chart drop-down. The drop-down list has 3 sections which are 2-D pie, 3-D pie, and Doughnut.
- At this point, click on 2-D Pie.
- After that, a Pie Chart will be generated in your worksheet.
- At this point, click on the chart and then click on the plus symbol which is Chart Elements.
- Then click on the arrow sign (>) beside Data Labels.
- Next, click on More Options.
- After clicking More Options, you will get the Format Data Labels dialog box.
- Next, select Category Name, Value, and Show Leader Lines from Label Options.
- Then, close the Format Data Labels dialog box.
- Afterward, you can edit the Chart Title and give your preferred Chart Title. In our case, it is the Item and Units.
- At this point, you can format the font size, color as your wish. I have formatted mine at my convenience.
Read More: How to Show Percentage and Value in Excel Pie Chart
Step 2: Calculating Total Units
After creating the Pie Chart, I am going to calculate the Total Units.
- In the first place, select your preferred cell to get the output. We have selected cell D15 in this case.
- Select cell D15 and then type the following formula:
=SUM(D5:D12)Here, the SUM function will return the sum of the cell values in the defined range. It performs the mathematical operation of addition. In this case, D5:D12 is the range of values.
- Then, press ENTER key and the result will be shown in cell D15.
Read More: How to Show Percentage in Excel Pie Chart
Step 3: Employing Text Box to Show Total Units
Now that we have our Total Units value, I will explain how you can show this total in your Pie Chart. To do so, we will use a Text Box.
- First, you have to insert a text box where you want to display the total. To do so, click on your pie chart and go to the Insert menu.
- Next, select the Text Box.
- After that, draw a Text Box on the chart at the point where you want the total to be displayed.
- Now with the Text Box still open/active, type an Equal sign (=) in the Formula Bar.
- Then in the worksheet, select the cell that contains the data that you want to display in the Text Box on the chart. In our case, it is cell D15.
- Press the ENTER key.
- Finally format the font size, color, and theme color as you wish. I have done mine as per choice.
Now let’s move to the next method.
Read More: How to Show Percentage in Legend in Excel Pie Chart
2. Showing Grand Total Using Pivot Table in Excel
In this method, I will demonstrate how you can show Total by using Pie Chart in Excel. To do so, first, we have to create a Pivot Table. Then, we will insert a Pie chart. Let’s follow the below steps one by one.
Step 1: Inserting Pivot Table
In this step, I will show you how to insert the PivotTable based on your preferred data range.
- First of all, select the preferred range for which you want to create the PivotTable. In this dataset, B4:D12 is the preferred range that we are creating the PivotTable for.
- Then go to the Insert tab.
- After that, click on the PivotTable dropdown.
- Next, select From Table/Range.
- After selecting From Table/Range, a new window named PivotTable from table or range will popped up.
- Next, select New Worksheet and click OK.
- As a consequence, Excel will take you to a new worksheet and you will get the PivotTable Fields dialog box.
- Now, select Item and Units and drag them to Rows and Values We have ignored Rep Name at this point to keep it short and simple for better understanding.
- At this point, you will see a Pivot Table created in your worksheet with column names Row Labels, and Sum of Units.
- Then adjust this PivotTable to your dataset’s column name. In our case, we have named Row Labels to Item and adjusted font, heading, etc.
Read More: How to Create Pie Chart for Sum by Category in Excel
Step 2: Creating a Pie Chart
In this part, I will demonstrate the steps to insert a PieChart based on our created PivotTable.
- In the first place, select the range of the PivotTable and go to the Insert tab.
- Then click on the Pie Chart drop-down and select 2-D Pie.
- As a result, a Pie Chart will be generated in your same worksheet.
- After that, I followed Step 1 of Method 1 to add Legend and Data Labels to the Pie chart.
- Therefore, you can see the Pie chart with Legend and Data Labels.
Read More: How to Group Small Values in Excel Pie Chart
Step 3: Employing Total to Pie Chart
Now, in this section, I am going to apply some Excel functions to show the total in our PieChart.
- First of all, select cell B15 and type an Equal sign (=) in cell B15.
- Then in the worksheet, select the cell that contains the data and it will generate the following formula. In our case it is cell C13.
=GETPIVOTDATA("Units",$B$4)Here, the GETPIVOTDATA function returns visible data from a PivotTable. In this case, GETPIVOTDATA(“Units”,$B$4) returns the total of Units. $B$4 is the reference to the starting cell of PivotTable.
- Next, press E.
- NTER Key and the output will be shown in cell B15.
- At this point, adjust the formula like below
="Grand Total: "&TEXT(GETPIVOTDATA("Units",$B$4),"###")Here, the TEXT function enables you to modify the appearance of a number by applying formatting to it with format codes. It is useful when you want to combine a text or symbol with a number. To show “Grand Total:” in the PieChart we have adjusted the formula with the Text Function.
- After that press ENTER key and the output will be shown in cell B15.
Now I will explain how you can show this Grand Total in your Pie Chart.
- First, you have to insert a text box where you want to display the total. To do so, click on your pie chart and go to the Insert
- Next, select the Text Box.
- After that, draw a Text Box on the chart at the point where you want the total to be displayed.
- Now with the Text Box still open/active, type an Equal sign (=) in the Formula Bar.
- Then in the worksheet, select the cell that contains the data that you want to display in the Text Box on the chart. In our case, it is cell B15.
- Finally, press the ENTER key and you will see your Grand Total in your PieChart like below.
- At this point, adjust the font size, color, and theme color as you wish. You can also edit the Chart Title. We have given Item and Units as our Chart Title and changed the theme as well.
So, at last, we have got our final Excel Pie Chart showing the total.
Read More: [Solved]: Excel Pie Chart Not Grouping Data
Practice Section
You can use the following dataset to practice by yourself. Hope it will help you to learn more.
Download Practice Workbook
Conclusion
So, I have tried my best to demonstrate every step clearly so that you can solve these types of issues by yourself effectively. I hope you have found this article helpful and constructive. If you have any suggestions or queries please leave a comment below.
