Custom labels for Excel line charts
Suppose that you’re putting together a line chart that will, for whatever reason, have several plateaus.
Now, you’d like to add labels to indicate the value…
Not quite what you had been hoping for? Now, I very well could be wrong, but I couldn’t find a quick way of indicating to Excel which labels you’d like to show, e.g., every third label, etc. Don’t worry, this is a quick and easy work around for this.
Suppose that your data sheet is setup as follows:
All that you need to do, is add a new row for the labels themselves. In cell B3, type the following formula:
=IF(B2>0,IF(B2=A2,"",B2))
What this is doing is looking at B2 and ignoring it if it is equal to 0. The reason that we do this is so that we don’t end up with a row of 0’s printred across the x-axis of our chart. Of course, if your chart extends into the negative numbers, go ahead and change the value following > to be equal to the lower limit of your y-axis. Now, if it finds that the value in B2 is greater than 0 (or your lower limit) it checks it against the value in the cell to it’s left. If they are the same, if prints a blank space, if they differ, it prints the value of B2.
The next step is to add another data series to your chart. Go back to your chart, right click on the chart area, and choose “Select Data” or “Source Data”, depending on the version of Excel that you’re using. Click “Add”, and identify your new row as the source of the series data. Click OK to exit this window.
Now, right click on the new line that has been plotted (it should look like a series of spikes whos apex’s touch the first data point on each plateau. In the right click menu, choose Format Data Series. Set your line and markers to none, then turn on your data labels for values. You should end up with something like this:
If you’d like to try and center the labels as I have here, assuming that the majority of your plateaus are the same width, you have to adjust the formula to look back one or two more cells, instead of just checking for a match to the immediate left.
