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!
Download Practice Workbook
You can download the practice workbook from here:
2 Effective Ways to Show Total in an Excel Pie Chart
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, Item and Units.
- At this point, you can format the font size, color as your wish. I have formatted mine as per my convenience.
Read More: How to Format a Pie Chart in Excel
Step 2: Calculating Total Units
After creating the Pie Chart, now 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 Create Pie Chart for Sum by Category in Excel (2 Quick Methods)
Similar Readings
- How to Make a Pie Chart in Excel with One Column of Data
- Make Pie Chart by Count of Values in Excel
- How to Edit Pie Chart in Excel (All Possible Modifications)
- Make a Gender Pie Chart in Excel (2 Easy Ways)
- How to Make Two Pie Charts with One Legend in Excel
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 your wish. I have done mine as per choice.
Now let’s move to the next method.
Read More: How to Change Pie Chart Colors in Excel (4 Easy Ways)
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 be popped up.
- Next, select New Worksheet and click OK.
- As a consequence, Excel will take you to a new worksheet and you will get PivotTable Fields dialog box.
- Now, select Item and Units and drag them to Rows and Values We have ignored Rep Name in 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 name 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 a Pie Chart in Excel from Pivot Table (2 Quick Ways)
Similar Readings
- Add Labels with Lines in an Excel Pie Chart (with Easy Steps)
- How to Show Pie Chart Data Labels in Percentage in Excel
- [Fixed] Excel Pie Chart Leader Lines Not Showing
- Excel Pie Chart Labels on Slices: Add, Show & Modify Factors
- How to Make Pie Chart in Excel with Subcategories (2 Quick Methods)
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 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 Edit Legend of a Pie Chart in Excel (3 Easy Methods)
Step 3: Employing Total to Pie Chart
Now, in this section, I am going to apply some Excel functions to show 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 ENTER 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 your 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 total.
Read More: How to Make a Pie Chart in Excel [Video Tutorial]
Practice Section
You can use the following dataset to practice by yourself. Hope it will help you to learn more.
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. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
Related Articles
- How to Create A Doughnut, Bubble and Pie of Pie Chart in Excel
- Show Percentage in Excel Pie Chart (3 Ways)
- How to Make Pie of Pie Chart in Excel (with Easy Steps)
- Make a Pie Chart in Excel with Words (with Easy Steps)
- How to Make a Pie Chart with Multiple Data in Excel (2 Ways)
- Group Small Values in Excel Pie Chart (2 Suitable Examples)
- How to Explode Pie Chart in Excel (2 Easy Methods)