If you have ever used a Pie Chart in Excel, then you probably ran into this need.
The need to hide Pie Chart Legend entries that are blank or have a value of zero (0).
From this:
In this tutorial, you will see the quick and easy way that you can do this with Excel functionality.
The Breakdown
1) Highlight Chart Data and Insert a Excel Table
2) Insert Pie Chart from Excel Table
3) Filter Pivot Table Values
Step-by-Step
1) Highlight Chart Data and Insert a Excel Table
You Pie Chart data will most likely be set in a vertical fashion in a table like format. Like this:
However, it may not be in an Excel Table. So we need to transform the data into an Excel Table. First, highlight your data. Then go to the Insert Ribbon and Select Table from the Tables Group. Alternately you can press CTRL+T to insert the table.
Adjust your Table settings as needed and press OK
Your Excel Data Table will now look like this:
2) Insert Pie Chart from Excel Table
Now that we have created our Excel Table, we need to create our Pie Chart. This is very easy. First, click anywhere in the Excel Table. Then go to the Insert Ribbon and click on the Pie Chart button in the Charts group. Finally, select a Pie Chart style.
Your pie chart will look like this:
3) Filter Pivot Table Values
So now we have only one step left. To only show the pie chart slices with slice values not equal to zero (0) and not equal to blank, we just need to filter the Excel Table we created in Step 1 above. You can do this by selecting the Filter pick list from the Values header as you see here by unchecking the Zero (0) checkbox and unchecking the (Blanks) checkbox:
Excel makes it simple and easy if you know how to use the tools the provide. Your final chart will now only show the unfiltered values where they do not equal to blank or zero as you see in our final chart.
Simple and easy if you ask me.
Video Demonstration
This short 3 minute video tutorial will show you how quick you can use this technique in your own dashboard projects.
Free Sample Excel File – Pie Chart with Hidden Legend Items
How-to-Easily-Hide-Zero-and-Blank-Values-from-an-Excel-Pie-Chart-Legend.xlsx
Now that can also be done with formulas, but that is for another post in the future.
Steve=True