Excel: Graphing Ranges

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:

This is the final chart.

This is the final chart.

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.

This is what you should get with the first chart.

This is what you should get with the first chart.

Now here are the steps to get things to look right:

  1. 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.
  2. 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.
  3. Now do the same operation for the Max data series.
  4. You should now have something that shows ranges with a marker for the average and max.
  5. 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.
  6. 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.

  • MAHA

    hello,

    this is exactly what i was seeking for, thanks alot.
    but im not able to excute the 5th point, where to create another data series. specifically “Then for source data highlight the min date from the table. “. what is the (min date”. i have the specify the series x values and then series y value, what should be the selected data?

    thank you

  • Hi MAHA,

    Sorry about that, there was a typo in step #5. It should have said “min data” and not “min date”. I have updated the post. To clarify in step 5 you want to create a new series based on the min data, in the example above the lower part of the salary range of each occupation. The point of this step is to add a marker to indicate on the chart what the lower part of the range is. Hope this clears this up, thanks for catching the typo.

    Jamas

  • RC

    This saved me hours! Thanks.

  • Julian

    Jamas, I don’t understand “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.” Is is done in Excel after you have already clicked INSERT > CHART? What does it mean to add a column that is the result of =MAX-MIN? What is the existing table? Would you be iwlling to put up screen shots for this part?

  • Hey Julian, lets see if I can clear this up. I assume you already have a column of MAX values and a column of MIN values in a spreadsheet. In my example in the post this is the MIN and MAX salaries of the three jobs. In excel lets assume the job titles are in column A, min salary is in B, average in C and max in D. The first step is to add a new column in excel, lets say in column E, that is the result of max minus min salary. With columns assigned as I have them listed the formula for the first cell of the new column would just be =D2-B2 and that would just be repeated down the rows of the column. Does that clear that up? Let me know if you still need a screen shot.

  • yuli

    Hi Jamas,
    It really help me!
    The next question is how if we want to compare with previous salary ranges ? could you also share, I’ve tried many times but it couldn’t work..
    Thanks

  • Hi Yuli,

    Not sure if I fully understand your question. Are you wanting to compare salary ranges over different years? In that case you could just create a row in the table for each year, for example I could add Butcher 2011, Butcher 2012 and Butcher 2013 as rows and then show the change in salary range over time. Let me know if that covers what you wanted.

    James