Monday, December 10, 2012

How to remove zero values from Office Excel graph / chart?

 

No matter in version of MS Office you are working with, this simple trick will always work. Lets get to the topic straight.

Here is a table I have for which I need to draw a graph. We have zero values for two cities - Narnia and Atlantis (Assuming that everyone is wiped out in zombie apocalypse).

City

Population in Millions

Chennai 243
Bangalore 527
Narnia 0
Atlantis 0
Mumbai 457
Delhi 23
Kolkata 10

Here is the typical graph we generated in excel for the above table. One thing that looks un-presentable in this chart is the zero values at the bottom of the pie chart.

How to remove zero values from Office Excel graph or chart - With Zero Values

Here are the steps to remove the zero values.

  • Right-click on any of the data labels (the numbers representing the population here) and select Format Data Labels.
  • A Pop-Up for Format Data Labels will open. In that navigate to Number tab.

    How to remove zero values from Office Excel graph or chart - Format Data Labels
  • In Number tab under Category, select Custom and enter the Format Code as below

0;;;

How to remove zero values from Office Excel graph or chart - Removing Zero Values

  • Click Add and Close the window. Now your graph will look like this.

How to remove zero values from Office Excel graph or chart - Without Zero Values

The format code we had seen is nothing but of the Syntax as below

Positive Value;Negative Value;Zero Value;Text

By making the negative value, zero value and text as blank we are telling the excel to ignore those type of values.

Hope this post comes handy when you are in urgent need to find the above said solution. If you are in a rush don't forget to bookmark this page and add a comment later. Thanks.