How-to Easily Make a Dynamic PivotTable Pie Chart for the Top X Values

I recently had a request from a fan of the site on how they could make a dynamic pie chart with the Top 5 values.  The user had to create this pie chart regularly from a larger data set and wanted to know how they could do this easily.

 

The user thought that they would need to use a combination of Index and Match or some other formula to find the Top 5 values and was concerned if there were ties and how it might change if there were less than 5 top values.

 

Well there is an easy solution for this and Excel had done the heavy lifting for us.  The other great thing is that this technique will also work for many other chart types like Column and Bar charts in Excel.

 

So lets show you how you can do this in just a few short steps.

 

The Breakdown

1) Create Pivot Table

2) Set Pivot Table Filter for Top or Bottom Values

3) Create Pivot Chart

4) Refresh Pivot Table Data

 

 

Step-by-Step

1) Create Pivot Table

First we need to create a Pivot Table.  You can do this by highlighting your data set and then click on the Insert Ribbon and then click on the Pivot Table button in the Tables Group:

image

Note: I have highlighted the data in the Top 5 Positions so that I can check the final Pie Chart results.  I have done that quickly with Conditional Formatting.  If you want to learn more about Conditional Formatting, check out these posts:

The Tricks to Writing a Conditional Formatting Rule Formula

How-to Make a Non-Chart Excel Dashboard Chart (Heat Index)

 

Now lets finish creating our Pivot Table by adding some Rows and Values.  You can do that by selecting the pivot table:

imageTthen drag and drop your row categories to the rows section of the PivotTable Fields dialog box and also dragging your values to the values section:

image Your PivotTable will now have Values grouped in Rows.

 

2) Set Pivot Table Filter for Top or Bottom Values

Now that we have data in our PivotTable, we can now set up our Value Filters to only show the Top 5 Values.  To do this, select the Row Labels Drop Down Filter and then choose the “Value Filters” from the Drop Down List and then finally choose the “Top 10…” menu item.

image

Now this may seem strange since we only want the Top 5, but don’t worry, you get to change that next.  After clicking on the Top 10… menu item, you can change the value from 10 to 5 and then press OK button.

image Your PivotTable will now only display the top 5 results as you see here:

image

3) Create Pivot Chart

Now that we have our data set, you can create a Pivot Chart from the PivotTable and it will only display the filtered values.

You can do this by selecting any cell in the PivotTable and then Click on the Insert Ribbon and then choose the Pie Chart button from the Chart Group:

image You will then see your Dynamic Pivot Chart created for the Top 5 values only.

image You are not limited to the Top X.  You can also create another PivotTable and show the Bottom X as well back in step 2 and then change what you show in the Top 10 Filter:

image

 

4) Refresh Pivot Table Data

Now anytime you get a new data set or change the data set, to get an updated Pie Chart, all you have to do is Refresh your PivotTable and your chart will dynamically change.

image

You can refresh your PivotTable and Pivot Chart by selecting the Data Ribbon and then Choose Refresh All from the Connections Group.

Also, Excel will take care of any ties in the data, so if you have more than 5 in your top 5 due to ties, Excel will display the correct amount of items in your Pivot Table and Pivot Chart.

In this example, i have 6 values that are in the Top 5 due to tiesimage

Rank 1=99

Rank 2=85

Rank 3=84,84

Rank 5=78,78

So when I refresh my data, I will get six Pie Slices instead of 5 as you see here:image

Hopefully that helped the person in need and you when you want to create a Top X or Bottom X chart.  Simply put it into a Pivot Table and change the Value Filter.

 

Video Demonstration

Here is a quick video tutorial on the step-by-step process:

Free Sample Pie Chart Template Downloadable File

Here is a file that you can use to try it out for yourself.  Simply open up the file and change some of the data points and then Refresh the PivotTable and see the results.

Dynamic Pivot Table Pie Chart Top X Values

 

Do you use the Top and Bottom Values filter in Excel Pivot Tables and Charts?  Leave me your best use case in the comments below.

 

Steve=True