0% Complete
0/10 Steps

[202.4] Making a pivot pie chart in Excel

Step-by-step instructions on how to make a pivot pie chart in Excel

  1. The data

    Our data set contains population by country and region in the EU28 (you can find it in sheet PopCountry).

  2. Create a pivot table

    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]

  3. Sort at region level

    Right-click the data, choose Sort from the popup menu and Sort Largest to Smallest.

  4. (Optional) Calculate percentages

    Right-click the data again and choose Show Values By and then % of Column Total.

  5. Insert the chart

    Select any cell inside the pivot table and insert the pie chart.

  6. Color encoding

    Choose all slices and apply the same fill color. Then select each slice and use a fill color for each region.

  7. Collapse the field

    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).

  8. Adjust labels at the lower level

    Click the plus sign at the bottom right corner to get back to the country level. Remove the labels identifying small countries.

  9. Remove fields

    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.

  10. Final chart

    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)")