Add data labels without displaying data points?

[Guest]

I have a bar graph showing payer mixes for our 3 business units, 6 bars for
each unit, with the data labels above each bar showing the percent of total.
Across the bottom of graph I would like to add the prior year percentages for
comparison, but I don't want to include that data in the graph - just display
the percentage.

I tried adding a text box with the information, but I'm not able to
consistently get it to line up with the current data. I also tried adding
individual text boxes linked to my data, but this was not ideal because of
the difficulty in lining them up as well. I also thought of adding the prior
year data to the graph as a line graph and then changing the color to match
the background so that it would not show, and then repositioning the data
labels to where I want them... but some of the lines ran through the bars,
showing a white line (that idea was in the middle of the night - worth a
shot, anyway).

Does anyone know how this might be done? Seems like it isn't too difficult
of a request and there should be a way...

Thanks!
Brenda

 

[Jon Peltier]

You can format a line chart to have no line and no markers, to avoid the background
colored line.

You can add a line series, hidden as above, using Y values to put the labels where
you want, then use a third party utility to assign labels to the points from another
worksheet range. Two such utilities that do this, both free and easy to install and
use, are:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

I did as you suggested and added a hidden line series, then respositioned the
labels to where I wanted them. But, I still had issues with getting the
labels to line up correctly, and even when they appears to be straight and
below the bars on the graph, they didn't print that same way.

I installed the J-walk chart tools, but I don't really understand the use of
"assigning labels to the points from another worksheet range". How would
this help?

We ended up adding a test box below the graph, but this is still not the
ideal solution. I'm not able to position them exactly where I want them
because they print differently, and they will have to be updated manually.

 

[Jon Peltier]

No, don't reposition the labels by dragging them around, reposition them
by adjusting the X and Y values in the sheet. The purpose of using an XY
series is to position them by putting the data point exactly where you
want the label, and using the "Center" position for the label. As soon
as you drag the label, it is no longer spatially linked to the data
point, and the alignment is now up to chance. If you keep them spatially
linked, then the labels will view and print where the point locates them.

Both the add-ins I suggested apply labels from a worksheet range to a
chart series. This way, you can edit the cells with the labels, which I
find easier than editing the labels in the chart. Also, suppose you had
three columns:

Date Amount Salesman

You can use these tools to place the salesman's name on a chart of sales
vs. date.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______