Chart more Labels

I do a lot of "hot" labels linked to a cell. Click on the graph, click on the formula bar space, type "=" and then click on the cell. Finish by clicking the green check in the formula bar or Enter. The new text will appear in the middle of the graph. Move it to desired location.

If you're trying to label lots of data-related labels (ie, next to the data pts), I think you could probably use one of the chart labeler addins, specify the labels to appear offset (above, right, left, etc) and then select and move them as a group.

With individual labels that need their positions tweaked, I find the mouse to be a blunt instrument. Instead, I select the text box, hit F2 (edit) and then Esc. This leaves the focus on the text box and allows you to use the cursor arrows to fine tune the positioning. Cool thing is when the focus is on one text box, you can use the tab key to loop through selecting the other text boxes and sequentially tweak them, too, without having to select each one with the mouse (big time saver!).

g'luck
wbd

Good information. However, I want to bring a lot of data in so doing individual add ons would be too time consuming. Is there a way to bring a lot of data in automatically?

Yeah, I guess I wasn't specific. There are free addins (JWalk tools, XY Labeler, etc) that do chart labeling where you select a column of labels to apply to a series of data. That'll bring in all the labels. On some routines, they let you specifiy how to arrange all the labels relative to the data point (also available under data format).

If you have more than one set of labels to port, a kluge would be to graph a dummy or duplicate series of data and then ghost them with no symbols just to use for the data label placemarker.

Another brainstorming approach would be using VBA to create textboxes and fill them from a range of cells. I haven't done this. Just depends on how desperate you are and if there's much to gain by automating the task.

Thank you. Which one do you like better, JWalk tools or XY Labeler?

I have and use both. JWalk does a nice graph resize I use to make selected/all graphs the same size (important when creating publication figures) while XY labeler includes the data position as an option. JWalk has other functions but I don't seem to use them much (eg, picture export and chart data report). Trivially, JWalk appears as a Chart addin, XY as a Data addin.

wbd - can I pick your brain on your dummy series solution, please? I'm just playing around trying to do it. I've created a second series with the same values as the first, and created a third series with the values I want displayed, which I set to be the category x labels for the second series. This works well in terms of displaying the numbers next to the Series1 data points, but I lose the category x labels I originally had for the Series1.

Have I misunderstood your proposed solution?

cfg

Okay, you actually made me go and try it. I plotted a 2 col data set as a bar chart (I'm more and xy kinda guy myself--no pun). The value of y2 would not be important in this chart type, you're just looking for a placeholder for the labels; xy charts should use same values.

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>label 1</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>y1</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>y2</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>label 2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>a</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">h</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>b</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">i</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>c</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">j</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>d</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">k</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>e</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>8</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>8</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">l</TD></TR></TBODY></TABLE>

Then I removed all the formatting (line and fill) for the 2nd series symbols. Then I used xylabeler to attach the label1's to series 1 and label2's to series 2. Then I used XYlabeler Move Labels utility to nudge all of series 2 labels into the vicinity of series 1 (style to suit). Then I went back to the series1 symbol format to dink with the gap and overlap options until I found something pretty to fill the xAxis gaps.

A similar strategy should work with other chart types.

cheers,
wbd

wbd - thanks for that!
Don't think work would let me download the add-in to have two sets of axis labels, so I guess the text box solution will be the best for me. Hadn't come across them before, so thanks for the tip!

Okay, I've found a method without add-ins, so I thought I'd come back and share:

Create the dummy line series, create category data labels, and remove the line and point markers. The data labels will be the same as the x-axis labels.

Put the dummy series on a secondary axis. It'll put it on a y-axis, but in Chart Options this can be changed to an x-axis. The two series can then have different x-axis labels. Then clear the secondary x-axis, leaving just the data labels.

(With thanks to the Jon Peltier tip where I discovered the secondary x-axis!)