A Pie Chart is a great way to show the comparison between multiple variables. In many cases, you might need to create a Pie Chart for sum by category in Excel. Although you can’t do it directly for a Pie Chart, there are a few tricks that you can apply. This article demonstrates how to create Pie Chart for sum by category in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
2 Methods to Create Pie Chart for Sum by Category in Excel
Let’s assume, you have a dataset with a list of products that fall into three categories and their Sales Quantity. The categories are Laptop, Headphone, and Smartwatch. At this point, you want to create a Pie Chart for the sum by category. In the following stages of the article, we will show you two methods to do so.
1. Using SUMIF Function to Create Pie Chart for Sum by Category
In this method, we will use the SUMIF function to find out the sum by category and then create a Pie Chart. Now, follow the steps below to do so.
Steps:
- First, select cell G5 and insert the following formula.
=SUMIF(D5:D13,"Laptop",C5:C13)
In this case, cell G5 is the cell indicating the Sales Quantity of the category Laptop. Also, we used the SUMIF function which has a syntax of =SUMIF (range, criteria, [sum_range]).
- Then, select cell G6 and insert the following formula.
=SUMIF(D5:D13,"Headphone",C5:C13)
Here, cell G6 is the cell indicating the Sales Quantity of the category Headphone.
- After that, select cell G7 and insert the following formula.
=SUMIF(D5:D13,"Smartwatch",C5:C13)
In this case, cell G7 is the cell indicating the Sales Quantity of the category Smartwatch.
- At this point, select range F4:G7.
- Then, go to the Insert tab.
- After that, select Insert Pie or Doughnut Chart.
- Next, click on Pie to insert a Pie Chart.
After inserting the Pie Chart, now we will format the chart and add elements to make the chart more understandable and visually more appealing.
- Now, double-click on the data series to format the data series according to your preference.
- Also, click on Chart Element > Data Labels > Center to add Sales Quantity value to the center of each proportion.
Consequently, you will have your output as shown in the below screenshot.
Similar Readings
- How to Make a Budget Pie Chart in Excel (with Easy Steps)
- How to Edit Pie Chart in Excel (All Possible Modifications)
- [Fixed] Excel Pie Chart Leader Lines Not Showing
- How to Rotate Pie Chart in Excel (4 Useful Cases)
- [Solved]: Excel Pie Chart Not Grouping Data (with Easy Fix)
2. Utilizing Pivot Table to Make Pie Chart for Sum by Category
Pivot Table is a very handy tool in Excel. In this case, to create a Pie Chart for sum by category we will use a Pivot Table. Now, follow the below steps to do so.
Steps:
- First, select the whole dataset.
- Then, go to the Insert tab.
- After that, select Pivot Table.
- Next, click on From Table/Range.
- At this point, select Existing Worksheet first or the worksheet you want to keep your Pivot Table in.
- Then, from Location select the cell you want your Pivot Table to start. In this case, we choose cell F4.
- Next, click on OK.
- Now, from Pivot Table Fields select Sales Quantity and Category.
- Or, drag Category to Rows and Sales Quantity to Values field.
- At this point, you will have your Pivot Table as shown in the screenshot below.
- Now, select any cell on the Pivot Table.
- Then, go to the tab PivotTable Analyze.
- Next, click on PivotChart.
- Subsequently, go to Pie from the All Charts options.
- Then, select Pie and click on OK.
- Now, format and add elements to the chart as mentioned in the above method.
Eventually, you will have your output as shown in the below screenshot.
Note: This method is dynamic. That means if you add a new entry with its category and update the PivotTable range, you’ll get an updated chart automatically.
Read More: How to Create a Pie Chart in Excel from Pivot Table (2 Quick Ways)
How to Sum Data and Create a Pie Chart in Excel
In this section, we will sum data using the excel Consolidate feature and then create a Pie Chart for the consolidated data.
Suppose you have a dataset where you have the Sales Quantity of three different products for two different weeks as shown in the screenshot below.
At this point, you want to sum the Sales Quantity for each of the products over the weeks and create a Pie Chart. Now, follow the steps below to do so.
Steps:
- First, select the cell you want to keep your consolidated data.
- Then, go to the Data tab.
- Next, from the Data Tools select Consolidate.
- Now, from the Function select Sum.
- After that, in Reference select the range B5 to F8.
- At this point, from the Use labels in check the boxes for Top Row and Left Column.
- Subsequently, click on OK.
- Right now, select the range B11:C14 which represents the range for consolidated data.
- Then, go to the Insert tab.
- After that, select Insert Pie or Doughnut Chart.
- Next, click on Pie to insert a Pie Chart.
- Now, format and add elements to the chart as mentioned in the above method.
Consequently, you will have your output as shown in the below screenshot.
Read More: How to Make a Pie Chart in Excel [Video Tutorial]
💡 Things to Remember
- In Excel, you can not directly insert a Pie Chart for sum by category.
- In order to create a Pie Chart for sum by category, you need to categorize the data using other functions or features first.
- You can use Bar Chart in excel which will automatically give you a chart for sum by category if it fits your preference.
Conclusion
Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
Related Articles
- How to Make a Pie Chart in Excel without Numbers (2 Effective Ways)
- Group Small Values in Excel Pie Chart (2 Suitable Examples)
- How to Explode Pie Chart in Excel (2 Easy Methods)
- Make Pie Chart with Breakout in Excel (Step by Step)
- How to Make a Pie Chart in Excel with One Column of Data
- How to Make Pie Chart by Count of Values in Excel