Add vertical lines below a line chart

[mq1973]

Hi guys,

I am not sure if this can be done. I need to create s simple line chart that represents cycle times of records and below this line I would like to add vertical lines which represent "# of review". I need to be able to see in the line chart, the record cycle time and how many times it was reviewed during this cycle time. Please ignore column B (i was trying to add time limit).

Roughly, it would look like this
line chart.png

[MrShorty]

Did you upload the correct sample file? All I see is a blank spreadsheet.

From you picture, your vertical lines look like error bars. https://support.office.com/en-us/art...5-864049a145f0 As pictured, you would add the error bars and format to show negative error bar only and set the value 100%, though I am not sure how -100% error bar is related to the number of reviews in a cycle, so maybe you intend a different value for the vertical line.

Since you can't add a data label to an error bar, getting those data labels for the # of reviews would probably most easily be accomplished by adding another "dummy" series and attaching the appropriate marker/data label to that dummy series. I have an example here that uses data labels to do something very similar that you may find useful: https://www.excelforum.com/tips-and-...p-example.html

Let us know if something like that will work and if you need help implementing.

[mq1973]

My bad, I attached an empty file. I added the correct file. The vertical lines I added them manually by inserting shapes.

I know it may be a little tricky because the vertical lines don't go up and down according to their values. The idea is that I am dealing with records that need to be reviewed with a specified time. They can be reviewed multiple times. For example, I may have a record that was reviewed 1 time and completed in 4 days or I may have a record that was reviewed 3 times and completed in 2 days. The example you referenced is different. In my example, there is not correlation between the number of review and cycle time.

Thank you MrShorty.

[MrShorty]

Did you try either of my suggestions? As I explained, the vertical lines look like vertical error bars formatted as minus error bar only and 100%. The helpfile should walk you through how to add and format the error bars. Steps I would have expected (recognizing that I am using an older version of Excel so you may need to check the helpfile to find the appropriate command buttons):
1) Select the data series in the chart.
2) Add error bars
3) Select the error bars -> Format error bars -> Select minus only -> Select value from percentage and enter 100 into the text box.

I realize that my data label example will not be exactly what you are looking for, but did you understand how I put the data labels into the chart? Steps I would have expected:
1) Add a column of dummy values that will position the # of reviews value. I put these values in E2:E6 in your sample file.
2) Add E2:E6 as a new data series (copy->paste or bring up the select data dialog or however you like to add data series to an existing chart).
3) Select the new data series -> Add data labels.
4) Format the new data labels so that they are centered (or whatever position you like) and that the data label values are from cells and select D2:D6. Change font and other formatting as desired.
5) If desired, select the new data series and format it to have no marker and no line so it is essentially invisible (except for the data labels).

Unless I am missing something, it seems like these strategies should be adaptable to what you are wanting.

[mq1973]

Thanks MrShorly, it seem like the error bar works but now I need to add data labels and I am not really sure how to do that. I will try to follow your sample for adding data labels.

error bar.png

[Andy Pope]

In the example I used additional series to plot a point midway between value and zero. The line can be formatted to remove marker and line.
The data label content of the new series can be linked to the values you want to display.

I used dropdown lines rather than error bars to get vertical lines.