# Making horizontal max-min-average chart in Excel [closed]

I'm trying to make a min, max, average chart with horizontal lines, where the vertical axis represents years in the series, something similar to this chart at

I've already created the data for the chart, i just don't know how to proceed

• You have a fair chance of creating such a chart in R. But this would require learning R, and that is not an something you can do in a day or two. – Dirk Eddelbuettel Jul 26 '11 at 22:40
• The fact that the blue lines are randomly aligned with points prove that someone had done this mostly manually; I really advise you to use something feasible of doing graphs. And don't listen to Dirk, one can learn R in few hours. – user88 Jul 27 '11 at 11:09
• When I use your graph, Mr. Peltier, my categories do not align with the data when using 10 categories, however, they shown up fine when there is only 8. Everything has been replicated from your chart. What might be the issue? – user127232 Aug 10 '16 at 12:55

This kind of chart is pretty easy to make in Excel, if you just think a little outside the box (and visit Jon Peltier's website: PeltierTech.com, you'll find all the tech info to make the chart at his site).

If the high/low/range can be vertical, instead of horizontal, you can use Excel's built in Stock chart types and it'll take care of everything for you. If not, you'll need to be a little more creative.

Basically you'll be overlaying 2 chart types: XY and stacked bar. You're primary data points (the markers) will be the XY data, with the values on your X-axis and the Y-axis will be a basic integer series like 1,2,3 to match up with the bars you'll be adding for the ranges.

The range bars are a stacked bar chart, with the first data series (left most) being a hidden, dummy series to align all of your bars to a common low value. Your second bar data series is the range data and once you tweak your chart axis, will line up with the XY data markers. Make sure your XY markers plot over your bar chart and you'll have a similar chart to what's shown.

Make your first chart, then copy as many times as you need for the 8 panels, and adjust your data source for each of the rows and you'll recreate the charts shown.

And, for what its worth, I've played with R and R for Excel and tend to agree more with Dirk.

My first thought was to make a dot plot (top left chart below). I have a tutorial showing how to make Dot Plots in Microsoft Excel, and also I sell a program that makes them.

But there is no horizontal line connecting low to high, so I decided to make only one series of dots, the Average, and use error bars to reach from the average down to the minimum and up to the maximum (bottom left chart).

Then again, you can plot all three sets of dots, and add the error bars in the same way (top right chart).

A final option is to make a stacked bar chart (bottom right chart). The first value is the minimum, and the bars are formatted to be hidden (no fill or border). The first visible series is the difference between the min and the average, and the second is the difference between the average and the max. These amounts are the same as the error bar lengths, not coincidentally, though having a bar span the axis requires some special fiddling.