How-to Easily Hide Zero and Blank Values from an Excel Pie Chart Legend

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:

Excel Pie Chart with Zeros and Blanks

To this:Final Excel Pie Chart with Hidden Zeros and Blanks





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:





Highlight Excel Pie Chart Data

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.

Insert Excel Table

Adjust your Table settings as needed and press OK

Excel Table Options



Your Excel Data Table will now look like this:

Excel Data Table

 

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.





Insert Excel Pie Chart from Excel Table

Your pie chart will look like this:

Excel Pie Chart with Zeros and Blanks

 

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:

Filter Table Values and Uncheck Zero and Blanks

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.Excel Pie Chart with Hidden Zeros and Blanks

 

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





2 COMMENTS

  1. Hi,

    thank you for this tutorial, and I have one question.
    how can I make the colors on pie chart as a fixed color, for Example:
    I made a pie chart for a Survey form with value 1,2,3,4 .
    1= Red
    2= Orange
    3= blue
    4= purple
    which I need to keep these as defaulted colors for each value.

LEAVE A REPLY

Please enter your comment!
Please enter your name here