Peltier Tech Blog

Excel Charting Utilities | Custom Solutions | Training | Charts and Tutorials | PTS Blog

 

Main menu:

 
Peltier Tech
Chart Utilities

PTS Waterfall Chart Utility
PTS Cluster-Stack Column Chart Utility
PTS Box and Whisker Chart Utility
PTS Marimekko Chart Utility
PTS Dot Plot Utility
PTS Cascade Chart Utility

Subscribe

Site search

Subscribe

Site search


Recent Posts

Recently Commented

Popular Posts

Archive


 

Categories


 

Buy me a coffee

If this topic or the whole blog has been helpful, support further development by treating me to a cup of coffee.


Privacy Policy

Creative Commons License
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

Hide Series Data Label if Value is Zero

by Jon Peltier
Tuesday, May 12th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

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.

stacked columns with series name labels on points with zero values

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.

stacked columns with Y value labels on points with zero values

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.

stacked columns with fake name labels only on points with positive values


Related Posts:

Bookmark and share this entry:
  • Twitter
  • Digg
  • Facebook
  • LinkedIn
  • del.icio.us
  • Technorati
  • StumbleUpon
  • Google Bookmarks
  • Reddit
  • MySpace
  • Slashdot
  • Yahoo! Buzz
  • Tumblr

Learn how to create Excel dashboards.

Comments


Comment from DMurphy
Time: Tuesday, May 12, 2009, 7:36 am

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!


Comment from Jon Peltier
Time: Tuesday, May 12, 2009, 8:13 am

Thanks, David!

I can usually get Excel to do whatever I want. Sometimes it takes unexpected measures, and sometimes a few off-color words.


Comment from ron
Time: Friday, January 8, 2010, 2:43 pm

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?


Comment from Jon Peltier
Time: Saturday, January 9, 2010, 7:38 pm

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.

Write a comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Read the PTS Blog Comment Policy.





Subscribe without commenting

PTS Waterfall Chart Utility PTS Cluster-Stack Column Chart Utility PTS Box and Whisker Chart Utility PTS Marimekko Chart Utility PTS Dot Plot Utility PTS Cascade Chart Utility

Create Excel dashboards quickly with Plug-N-Play reports.