Took me a while to try figure out how to do this from getting tips from several different sources (I have included the sources at the bottom if you are interested in more detail). What I was trying to do was produce a graph that would show salary ranges, min to max, for different job positions. As an example imagine you have the following data in a table.
| Job | Min | Avg | Max |
| Butcher | $ 30,000 | $ 40,000 | $ 50,000 |
| Baker | $ 35,000 | $ 45,000 | $ 60,000 |
| Candlestick-maker | $ 50,000 | $ 55,000 | $ 60,000 |
Now you want to create a graph that looks like this:
First thing you need to do is add a new column that is the result of =MAX-MIN for each. You can do this beside the existing table. Now select the entire table including the new column you just created. Create a chart, using the type stacked column. Make sure the the X-axis has each job position listed, if not the you have to tell excel to switch the data around, the data series should be min, average, max and max-min. You should now have a column chart that looks something like this.
Now here are the steps to get things to look right:
- Hide the min series. Select series by click on one of the min blocks. Format series. Set fill to none and line to none. This should make the series hidden but it is still there supporting the other blocks of the column.
- Now convert the Average data series into a line. Select the series. Right click on it and select change series chat type (different version of excel have this labeled differently). Select a chart type of XY scatter – markers only. You should get a dot or some marker that will replace the bar. Depending on the version of excel this will either be centered with the columns or create a secondary x-axis. If the marker is off center then click on one of the marker and then right click and select Format Data Series. Under that menu you should be able to force it to use the primary access. That is it for now.
- Now do the same operation for the Max data series.
- You should now have something that shows ranges with a marker for the average and max.
- Add a marker for the min. Right click on the chart and select data source. In the pop up window add another series. Call it what you want “Min”. Then for source data highlight the min data from the table. Depending on your version of excel it will either add this as an X&Y scatter type or as part of the column. If you get a column then convert it using the method above.
- You can now change the look of chart by adding data labels and changing the marker types to fit your needs.
I hope this will help people out who are trying to do the same sort of thing.
