With a little knowledge of number formats and a healthy dose of creativity, you can work around apparent shortcomings in Excel’s charting mechanism, neaten up your charts, and produce effects that are otherwise difficult.
In the Microsoft newsgroup, someone asked how to hide the data labels in his stacked column chart if the values are zero.
The problem is, a stacked column data point with a zero value has height of zero, and the label sits on the boundary between the two points on either side. In the example below, the labels for series C are fine, but the labels for series B in the Beta stack and for series A in the Alpha stack have no points to label.
The trick is to use the value option for the data labels, rather than the series name option. The series names have been replaced by values, and zeros appear where the unwanted series name labels are in the chart above.
Then apply custom number formats to show only the appropriate labels. In Number Formats in Excel I show how the number format provides formats for positive, negative, and zero values, and for text, with the individual formats separated by semicolons:
<positive>;<negative>;<zero>;<text>
Apply the following three number formats to the three sets of value data labels:
"A";;; "B";;; "C";;;
What these formats do is use the characters in quotes in place of any positive numbers, and use “” (from between the semicolons) for negatives, zeros, and text. The undesired labels are now gone. The labels in the number format strings can be longer than a single character, of course; A, B, and C were easy labels to use for this illustration.
DMurphy says
It never ceases to amaze me how Excel can be twisted to do the (near) impossible! I have always said that programming (and I include spreadsheet manipulation in this definition) is more of an art than a science.
That was well worth a coffee! Enjoy with my thanks as this solves yet another of my users’ complaints!
Jon Peltier says
Thanks, David!
I can usually get Excel to do whatever I want. Sometimes it takes unexpected measures, and sometimes a few off-color words.
ron says
in a similar vein, i’m using Excel data in a Powerpoint BAR chart (not stacked). however if the value of a series is ZERO i would like bar chart to ignore it. no label, no bar, no empty space where bar would be. can i do that?
Jon Peltier says
It’s a bit complicated. If the data is in columns, you could hide the rows with zero or blank data and it won’t plot, because by default Excel doesn’t plot data in hidden cells. If it’s not as easy, it takes more involved manipulation of the data. I believe Andy Pope has some example on his web site, at http://andypope.info.
Paul Makulski says
I’ve been doing the same battle with a donut chart. The inner ring includes all of the outer ring labels stacked on top of each other at the zero position, and the outer ring has all of the inner ring labels stacked up. Really gross.
Your solution would work for this, except I’d need a custom number format for each cell (and there are going to be a lot of them).
There’s no way to set these formats using a formula, ist there? Or a conditional format (which for some reason doesn’t include number formatting.)
I also tried diddling the series formula, but it seems the “x” range is the same for all series, even when you specificly change them in the series formula.
I’m boxed.
Jon Peltier says
Paul –
Why do you need so many custom formats? Select all cells and apply the same format.
Could you use any better chart type than a donut?
Paul Makulski says
Hi Jon,
I have never used a donut before, but it was exactly what the user wanted.
To apply your number format solution, you need one custom format for each value cell to make it look like a label. There will be about 80 data points.
I have written a macro to do the custom formats for me. It works, but it really says “kludge”. (email me if you’d like to see what I’m doing.)
It would certainly be easier if Excel just provided for an option for a label range for each series. Do you know if that is a feature in Excel 7? (I’m using 2003)
Jon Peltier says
Paul –
Excel does not provide a label range for its chart series (although Lotus 1-2-3 had it long ago).
Chandoo explains how to manually link up labels to worksheet cells in How to Change Excel Chart Data Labels to Custom Values.
And a clever Excel developer named Rob Bovey wrote an add-in which does the trick for you. it’s a free add-in available from his web site. Go to http://appspro.com and look for the Chart Labeler.
Jon Peltier says
I questioned Paul about his use of a donut chart. He says it’s what the customer wanted to use to show relationships qualitatively:
This use of a donut chart is probably fine, since we don’t care about the areas of the arcs.
Rishu says
Hi,
I am showing both the label and the value in my chart. For example, in my chart it would appear (A,3), (B,0) & (C,3) for Beta.
However, I want only (A,3) & (C,3) to appear.
Is it possible to do that? Basically for ZERO value, I neither want the label nor the value to appear.
Please help
Rishu
Jon Peltier says
You can hide the zero value using a custom number format that suppresses zeros. However, the category will still appear, and it will look like ‘B,’.
Rishu says
Hi Jon,
Yea currently I am doing that but because I have a lot of series and for a particular data points, only a few have values. This causes all the labels with zero values to overlap each other and hence the figure looks clumsy.
Is there any way to get rid of the label also as in I dont want the “B” to appear?
Thanks
Rishu
Jon Peltier says
Rishu –
You could set up custom labels in another range of cells, with formulas that show “B” if the value is not zero, and “” if the value is zero. Link the labels to these cells manually (select the set of labels with one click and the individual label with another click, type = in the formula bar, click on the cell with the label, and press Enter) or using the Chart Labeler by Rob Bovey of AppsPro.com.
Amber Criner says
I put in the custom number format as #;;; and it worked like a charm to get rid of the zeros.
Leo Franklin says
hi its not complicated ,
just go to .. data labels in charts ….select format data labels … in that select the option numbers … select custom .. give the format as “#,###;-#,###” then click add .. all the zeros will be ignored in the barchart……..It Works ….
Juan Carlos says
Hi Jon, I have learned a lot of things from your page, since I was school, and never had a question about the procedures you do, but a few days ago, in my work (civil engineering) I did a worksheet to calculate the bearing capacity of the soil, and use the tip you gave about using check boxes for graphing, every works great, but a (I suposse) simple question arrive to me, because I have could not solve.
If I have several check boxes and I try to plot some of them, not all, always the data labels appear and I dont know how to blank them, and make apear just when they are used.
Is there a way to do it?
Don´t know how to attach the file, but i suppose this is a simple and mortal question for you, thanks a lot for your knowledge. Sorry for my English.
Jon Peltier says
Juan Carlos –
It’s not an easy task to hide the legend for series which are hidden in this way. What you could do, is apply a data label to the last point of each plotted series, and remove the legend. This will only show labels for plotted series, and labels make it easier to identify series than using a legend.
Colin Bowern says
This proved to be super useful on an area chart to stop the drop off to zero.
=VALUE(TEXT(formula, “0;-0;-9999999;”))
Then set the vertical axis minimum to 0.
Sam says
Fantastic thanks ever so much for this REALLY helpful!!!
Cheets
Maria says
Wow you r a geniuos, thank u , this is all new for me
Jessie says
Hi, is there a way of doing this dynamically? I.e. instead of hard coding in the series name, link to the cell of the series name and only show this when non-zero? (it’s important that this series name is dynamic, as data inputs/data refreshes might not be in the same order each time). We are looking at a chart with over 50 series, and many data points. Many of these series will be zero, but will be updated and changed on a weekly basis, so the least manual input (weekly) the better. Also, is there a way of having a similar dynamic legend, which only shows the series that are non-zero. Unfortunately, I am unable to install external applications to my machine…. Thanks for your help.
Jon Peltier says
Jessie –
I can’t think of a non-code way to do this. I guess what I’d do is whenever the chart updates, run some code to apply the new custom format, the way it’s done manually here.
lillette says
Hi, i just want to ask. Why does the data labels doesnt appear for those our of range of the x-axis, meaning greater than maximum x value and less than minimum value (MS Excel 2010)
Jon Peltier says
If a data point isn’t drawn because it is off-scale, the attachment for the label isn’t there. so the label isn’t drawn.
Heidi says
Hi. I am a little confused by your response to Rishu regarding getting rid of the values AND the labels. I was able to get rid of both but I don’t know if I did it using the steps that you indicated (in parenthesis) below. I’m unclear what you meant and would like clarification on that. Also, as I said, I was able to get rid of the labels and values but the leader line and the comma that separates the label and value remains. I want the leader line and comma to display when the value is not equal 0 but I don’t want them to display when the value is 0. Is there a way to do that? BTW, I am doing this with a pie chart. Thanks.
Heidi says
The steps that I am referring to in my previous post are: “Link the labels to these cells manually (select the set of labels with one click and the individual label with another click, type = in the formula bar, click on the cell with the label, and press Enter) or using the Chart Labeler by Rob Bovey of AppsPro.com.”
Jon Peltier says
Heidi –
Not sure what the question is. My suggestion was to use custom labels, by adding regular labels and then linking each label to its own cell. These cells have formulas that, based on other cells, show a label like “B” in Rishu’s example, or nothing (“”), something like
=IF(A1>5,"B",""). The labels that show “” still exist, but do not appear, because they link to the cells that show “”.jerilyn says
Heidi,
I was trying to do the same thing with a pie chart – if the value is zero and there is no slice to label, do not display anything related to that non-slice. I tried everything in this post, but the leader lines and the commas refused to disappear. So, I used brute force.
My solution is not elegant by any means, but it works for me. Single click twice on the zero label on the chart itself — now only that label is selected — press delete. Do that for all zero values in the chart. Now if the next month or whenever you next use the chart, there is a value for that cell, there will be a slice without a label that requires you to relabel it. In my chart it is obvious and I just handle that when it comes up.
nik says
thank you. got there in the end
Rachel Macdougall says
Hi, I just wanted to thank you for this post – it really helped me to tidy up some charts I have been creating for my boss at work and I was amazed that I could do it! Very well explained.
Jerry Boettcher says
You can get rid of leader lines for 0 value items on the data side of your chart. For each of your data items, make a duplicate of the data by doing something like this: =if(originaldata=0,na(),originaldata).
Then, if your original data is zero, that piece of data will get charted as an na(), which means it will not get charted at all. Therefore, no zero’s appear and no leader lines appear either.
-jb
Jon Peltier says
Jerry –
In some chart types (e.g., column), while #N/A results in no apparent data point, the chart knows it’s there, and it will label the point with #N/A, and no amount of custom formatting will make it go away.
I’ve found that leader lines are less helpful than you’d have expected, so usually I turn off that option.
Jerry Boettcher says
Thanks for the comment. I have seen it put #N/A as a label at the end of a leader line in a column chart. I tested it yesterday on a donut chart and the NA() worked. I should have been more precise in my comment on the blog regarding the chart type.
I appreciate your site. I use it all the time.
Andreas Juncker says
Hallo Jon,
What if the entire beta column is zero, how can you make the “beta” text go away (and make the chart auto fill) ??
Jon Peltier says
Andreas –
There’s nothing you can do with formulas or formats to hide the “Beta” category in the chart or to hide an entire series in the chart (such as “B” in this example). But you could hide the “Beta” column or the “B” row, and since Excel does not by default plot data in hidden cells, these will hide them from the chart. In addition, if you are using Excel 2013 or later, you can filter out categories or series without hiding rows or columns in the worksheet. Click on the filter skittle (the third icon floating next to the chart) or uncheck items in the Select Data dialog.
JeteMc says
Response (Aug 1, 2013 at 1:25 PM) to Heidi’s question is very useful and possibly worthy of a tutorial in itself. Thanks for posting all of this useful information. I hope that you have a blessed day.
Craig Newth says
Hi Jon,
It works perfectly in Excel 2013 (“A”; ; ;).
It took me awhile to figure it out, as I had the “Series Name” ticked as well as the “Value” in Label Options. You only need the “Value” option ticked for it to work.
Thanks for your help,
Craig
cat says
Is there a way to make this, instead of putting “A” in the place of that label, to put the text associated with that datapoint? That is, not the series name?
Jon Peltier says
Cat –
Actually, you can put almost whatever you want into the custom number format, within reason.
I prefer to build my labels in another range, using formulas to build the labels or return “” if that is called for. Then I either use formulas or I use the values from cells option, to link the chart’s data labels to the cells with the labels I’ve built. Read my tutorial Apply Custom Data Labels to Charted Points for details.