Add reference line to category

Matthew Pounsett wrote:

when the table gets appended to, the chart will resize and the line will no longer be in the right place. I'm looking for something that is part of the chart itself, not a manual addition.

Have you considered using an error bar to create the vertical line? It will stay in position, though I don't think there is a way to attach a label to it the way you can with reference lines.

You create your 2D Scatter as usual. Add an extra series for the vertical line and enter a value at the desired date. The value you enter should be in the middle of the range for your y-axis values (though it doesn't have to be the "exact" middle because you'll use 100% later).

After inserting chart, click the first series and add a connection line here (and remove the data marker if desired).

Click the second series (the one with the single value) and add Positive and Negative Y Axis Error Bars.

Choose "Percentage" and change range to to 100:

Remove the single point on the vertical line and remove the Data Symbol here:

And click the line (error bar) and remove the ends by selecting the 'Bar Style' here:

Pretty up the axes, etc, as desired until you have the chart looking the way you want.

SG

Hi Matthew,

All of the reference lines described in the article reference specific "values", implying they do not apply to "categories".

Your "Line Chart" is a Category chart, on which each date is a category, not a value..

You might try an x/y Scatter Chart (with connecting lines between data points) in place of the Line Chart. Both x and y axes are Value axes in a Scatter Chart, and I notice the Scatter Chart is not on the list of types that cannot use reference lines.

Or, using a Category chart, you could simply add a line (from the Shapes button), rotate it to vertical, position it where you want the reference line, and label it using a text box.

Regards,

Barry

And just confirmed the error bar method works just as well with a category chart (line chart), where the dates are in a Header Column.

SG

" I'm looking for something that is part of the chart itself, not a manual addition."

Best wishes for success, then. you may get lucky, and discover something that I suspect is 'not there.'

Regards,

Barry

Hi Matthew,

I can't find a way to add a reference line to a category. Following on from Barry's suggestion of a scatter plot:

Scatter doesn't seem to understand what to do with a header column of type Date.

A scatter plot requires both X and Y values to be in body cells. The column labels should be in a header row.

Table 1 above has no header column and one header row.

Regards,

Ian.