I'm facing a condition where I'd need to plot some graph similar to this: enter image description here

where 2 items share the same x axis represents the upper and lower bound, and should be joined by a line in the graph.

This is what I currently do to plot the chart I wanted: enter image description here

Currently I manually add each entry series to plot the graph, which certainly will fail if the number of items increases. Is there any way to force Excel to use custom Entries / Series arrangement when a selected range is supplied to the chart, such that the desired chart can be created with minimum time? Or are there better ways to solve this problem?


There is a way to do this with error bars, and that's described in some detail on Peltier's Excel page.

BUT, if you really want to have the kind of control over formatting that you show in your example, you really need to make each bar a separate series. To do that, you have to format your data table like this:

enter image description here

If all that "wasted" space is a problem, you can hide this table or move it somewhere. The benefit of organizing the data this way is shown in the graph below.

To get the series to be vertical bars, each data point has to have the same x-value, hence the repetition in the first column. Once the table is organized, select all the data and choose Straight Lined Scatter from the Graph tab.

You'll get something like the picture below. I think I chose "Marked Lined Scatter", which is why the data markers show. I've formatted the first series to have arrowheads, which you can get under Format Data Series > Line > Weights and Arrows.

Hope this helps and good luck.

enter image description here


Try this:

I added another column of data at the end (call it Column C) and make the contents equal to the low.

Highlighting columns with headings A, B, and the new Column C, I use the "stock" chart type (under the Insert tab) which will plot the A, B, and C date series, and make the line from A to B. I then delete the data series of Column C from the Chart.

I then highlight and right click on the lines which will bring up a menu to reformat these lines. Click on the shape outline icon (looks like box with a pencil on the right), and then change the line to a double headed arrow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.