Removing zeros from data labels in Excel charts

When you add data labels to your chart, zeros will show up by default. This can look quite ugly in some cases, particularly on pie charts.

 

There are a couple of solutions to remove the zeros.

 

Option 1 Replace the zeros from the source data with blanks
If your source data is currently the formula =A1+B1 you might remove zeros with an IF statement:
=IF(A1+B1=0,””,A1+B1)
Now the source data is blank, the chart labels will be too e.g. on the below Gasquet and Tsonga have been replaced with blanks but Berdych, Ferrer and Nishikori haven’t.

 

ZeroDataLabels1

 

Option 2 Using custom number formats

Maybe your source data isn’t a formula, so it’s more convenient to remove zeros by using a custom number format.
In this example you want to remove the zero data label for the French Open.

ZeroDataPie1
Right-click on your data labels and select Format Data Labels.
Scroll to the Number section and choose Custom.
Type in the custom number format as 0;;; and click Add.

 

ZeroDataLabels3
The pie chart updates to exclude zeros.
ZeroDataPie2
If your source data is percentages rather than whole numbers:
ZeroDataPie3
Add the percentage sign to the custom number format 0%;;; and click Add. This is the result:

ZeroDataPie4

 

Do you know any alternative methods of removing zeros from the data labels in charts? If so, please leave a comment below.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *