How to hide zero data labels in chart in Excel?
Sometimes, you may add data labels in chart for making the data value more clearly and directly in Excel. But in some cases, there are zero data labels in the chart, and you may want to hide these zero data labels. Here I will tell you a quick way to hide the zero data labels in Excel at once.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
If you want to hide zero data labels in chart, please do as follow:
1. Right click at one of the data labels, and select Format Data Labels from the context menu. See screenshot:
2. In the Format Data Labels dialog, Click Number in left pane, then select Custom from the Category list box, and type #"" into the Format Code text box, and click Add button to add it to Type list box. See screenshot:
3. Click Close button to close the dialog. Then you can see all zero data labels are hidden.
Tip: If you want to show the zero data labels, please go back to Format Data Labels dialog, and click Number > Custom, and select #,##0;-#,##0 in the Type list box.
Note: In Excel 2013, you can right click the any data label and select Format Data Labels to open the Format Data Labels pane; then click Number to expand its option; next click the Category box and select the Custom from the drop down list, and type #"" into the Format Code text box, and click the Add button.
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 months agotnx, it works
To post as a guest, your comment is unpublished.· 8 months agoHow can I hide Zero Data Labels when it is percentage (for example 0,00%, I do not want that label to be shown)?
To post as a guest, your comment is unpublished.· 9 months agoHi
Thank you for sharing the solution above. It was really great help. However, after hiding zero values, I am having difficulties with hiding their labels. Can you please give a solution for that problem, too? Thank in advance for your help.
To post as a guest, your comment is unpublished.· 1 years agoFound this solution absolutely useful. Thanks a ton.
To post as a guest, your comment is unpublished.· 3 years agoGreat solution! Can this be done retaining decimal points on the numbers that do appear?
To post as a guest, your comment is unpublished.· 2 years agoKelcey, use:
When using the second semicolon, you define what happens in case of zero. So, if you don't define anything, the value stays blank.
To post as a guest, your comment is unpublished.· 1 years agoI use
and it also does the trick, in terms of retaining decimal places. You'd just adjust to the # of sig figs you want to keep. You have to assign the custom value to each data label series, but it keeps that format each time you refresh the data.