Dynamic Labels for Line Charts

  • Thread starter Thread starter Smooth
  • Start date Start date
S

Smooth

I am monitoring several processes in which I am using line charts with
date range on x axis and percentage on y axis. I have be asked to
annotate process improvements by date, and to update the charts on a
quarterly basis. I am now using chart text boxes and arrows. This is a
tedious process and quite prone to mistakes. Is there a way to have
these events update automatically each quarter, shifting left on the
chart with each subsiquent quarter? Thanks for any advice.

SRR
 
Probably the easiest way to do this is to select the series, then the
first point after a change. Press Ctrl+1 to format the point, and using
the options on the Patterns tab change its color or marker style so it
stands out. Before pressing OK, mosey on over to the Data Labels tab,
and add a data label to the point. Pick any option, we'll change the
text later anyway. Now press OK. Click once on the new label, then click
on it again (two single clicks, not a double click). Now you can select
the text and edit it, or type something in the formula bar. To link the
text to a worksheet cell, single click it twice as above, press the
Equals key, then select the cell with the mouse. You'll see the cell
reference in the formula bar like this: =Sheet1!A1. Press OK, and the
label will keep up with the contents of the cell.

If you update the chart by adding points at the end but not removing any
earlier ones, you'll be fine. The label doesn't stick to the particular
values, but to the point number. So if it labels point 6, and you change
the range to start four cells later than before, the label still refers
to the 6th point, but the point it had referred to is now point 2. If
you want to move all the points, make sure you have an XY Scatter chart,
so you can set the axis wherever you want, and let some points fall to
the left of the plot area.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks for the advice, Jon. My points shift left every quarter, so the
points are not constant. However, your technique of using data labels
beats using chart notes and arrows. I'm doing that along with the
color coding to mark the events. For instance, I select the point,
make it a standout color (as you suggest), then create the label with
a background color that matches the point.
Yes, I will need to update each quarter, but I suspect this will be
less prone to error.

Thanks for the great ideas.

SRR
 
Back
Top