Grouping data with Excel’s PivotTable makes it easy to summarize your data. Now, wouldn’t it be great to visualize this summary of your data in a chart? You’re in luck because this article demonstrates how to create a Pie Chart in Excel from Pivot Table.
Download Practice Workbook
You can download the practice workbook from the link below.
2 Ways to Create a Pie Chart from Pivot Table in Excel
First, let’s dwell a little upon what is a Pie Chart?
A Pie Chart is a circular graph where each slice of the pie represents the proportionate part of the entire data.
Generally speaking, in Microsoft Excel’s terminology, making a chart from a PivotTable is called a PivotChart, although it is the same as a normal chart.
Throughout this article, we’ll consider the following dataset shown in B4:D14 cells. Here, the first column shows the Item name, followed by the column for Category, and lastly, the Sales column in USD.
1. Creating Pie Chart from Pivot Table
Excel makes it simple to make a PivotTable and insert charts. So, without further delay, let’s see the process step-by-step.
📌 Step 01: Insert a Pivot Table
- Firstly, select the dataset as shown below and go to the Insert > PivotTable.
- Next, a dialog box appears in which you have to check the New Worksheet option and press OK.
- Then, on the PivotTable Fields pane drag the Category and Sales fields into the Axis (Categories) and Values fields respectively.
Just like that, a table is generated, it’s that easy.
- In turn, you can format the numeric values by right-clicking the mouse and selecting the Field Value Settings.
- Following, click the Number Format button.
- Now, choose the Currency option. In this case, we chose 0 decimal places for the Sales value.
📌 Step 02: Create Pie Chart
- Secondly, select any cell in the PivotTable.
- Then, go to the PivotChart > Pie as shown in the image below.
📄 Note: You can also insert Pie Chart from Insert > Insert Pie or Doughnut Chart > Pie.
- Next, you can format the chart by clicking on the Chart Elements option.
The results should look like the screenshot shown below.
2. Applying VBA to Insert Pie Chart from Pivot Table
While making a Pie Chart from PivotTable is easy, however, if you need to do it often, then you may consider the VBA code below. Just follow along.
📌 Step 01: Open the Visual Basic Editor
- Firstly, go to the Developer > Visual Basic.
📌Step 02: Insert the VBA Code
- Secondly, navigate to Insert > Module where you’ll paste the VBA code.
For your ease of reference, you can copy and paste the code from here.
Option Explicit Sub CreatePivotTable() Dim pt As PivotTable Dim pc As PivotCache Sheet3.Activate Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("B4").CurrentRegion) Sheets.Add , Sheets(Sheets.Count) Set pt = ActiveSheet.PivotTables.Add(pc, Range("B4"), "Sales_Pivot") pt.PivotFields("Category").Orientation = xlRowField pt.PivotFields("Sales").Orientation = xlDataField End Sub Sub Piechart() ActiveSheet.Shapes.AddChart2(251, xlPie).Select ActiveChart.SetSourceData Source:=Range("$B$4:$D$14") End Sub
💡 Code Breakdown:
Now, I will explain the VBA code used to generate Pie Chart. In this case, the code is divided into two sections.
Section 1: Explanation of CreatePivotTable () sub-routine
The explanation of the VBA code is provided below.
- 1- Firstly, assign a name for the sub-routine.
- 2- Secondly, define the variables.
- 3- Thirdly Sheet3 is activated using the Activate method and the memory cache is assigned using the PivotCache object.
- Additionally, we insert the PivotTable in a new sheet with the Add method.
- 4- Finally, position the PivotTable in the B4 cell and give the name Sales_Pivot.
- Moreover, we add the Pivot Fields i.e. the Category in the RowField and Sales in the DataField.
Section 2: Description of Piechart () sub-routine
In a similar fashion, the VBA code is explained below.
- 1- In this section, give a name to the sub-routine.
- 2- Next, the ActiveSheet property inserts the Pie Chart using the Shapes.AddChart2 method.
- 3- Lastly, the SourceData property selects the data range for the Pie Chart.
📌 Step 03: Running the VBA Code
- Thirdly, press the F5 key to run the CreatePivotTable () sub-routine.
- Next, execute the Piechart () sub-routine.
Finally, the result should look like the picture below.
I hope this article helped you understand how to create a Pie Chart in Excel from Pivot Table. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.