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.

Hide zero data labels in chart

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

Hide zero data labels in chart

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

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:

doc-hide-zero-data-label-1

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:

doc-hide-zero-data-label-2

3. Click Close button to close the dialog. Then you can see all zero data labels are hidden.

doc-hide-zero-data-label-3

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.

doc-hide-zero-data-label-4

Relative Articles:

Hide zero in chart axis in Excel

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

Increases your productivity by 50% when viewing and editing multiple documents.
Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
Open and create documents in new tabs of same window, rather than in new windows.
Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!

You are guest ( Sign Up? )

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
sumeet · 4 months ago
tnx, it works
- Reply
- Share
To post as a guest, your comment is unpublished.
Vesna · 8 months ago
How can I hide Zero Data Labels when it is percentage (for example 0,00%, I do not want that label to be shown)?
- Reply
- Share
To post as a guest, your comment is unpublished.
Shukrullo · 9 months ago
Hi
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.
- Reply
- Share
To post as a guest, your comment is unpublished.
Jose · 1 years ago
Found this solution absolutely useful. Thanks a ton.
- Reply
- Share
To post as a guest, your comment is unpublished.
Kelcey · 3 years ago
Great solution! Can this be done retaining decimal points on the numbers that do appear?
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Daniel · 2 years ago
  
  Kelcey, 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.
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  KW9 · 1 years ago
  
  I use
  
  [b]0.0;;; [/b]
  
  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.
  
  Reply
  
  Share

Login to your account

Password Reset

Reminder