Step-by-step instructions on how to make a pivot pie chart in Excel
Our data set contains population by country and region in the EU28 (you can find it in sheet PopCountry).
Create a pivot table and add [Region] an [Country] to the Rows field and [Population] to Values. Then click the down arrow next o Row Labels, choose More Sort Options from the menu and sort by descending sum of [Population]
Right-click the data, choose Sort from the popup menu and Sort Largest to Smallest.
Right-click the data again and choose Show Values By and then % of Column Total.
Select any cell inside the pivot table and insert the pie chart.
Choose all slices and apply the same fill color. Then select each slice and use a fill color for each region.
A nice thing about pivot pie charts is that you can easily move between levels. Click the minus sign on the bottom right and the chart will display regions only. Add labels (see the lesson on regular charts for details, if needed).
Click the plus sign at the bottom right corner to get back to the country level. Remove the labels identifying small countries.
If the fields are not needed, they can be removed. Right-click the Value field (top left corner) and select “Hide Value Field Buttons on Chart”. Right-click the legend and delete it. You may want to have a copy of the chart before removing the buttons.
Here is how the final chart should look like after adding the title. Since you need to change the title dynamically, depending on the level, add this formula to the cell where you’re storing it:
="PROPORTION OF EU28 POPULATION BY "&IF(COUNTA(A5:C5)=2,"REGION","COUNTRY (WEST, SOUTH, EAST AND NORTH REGIONS)")