# Range bars, not error bars

1. ## Range bars, not error bars

I'm trying to create individual range bars for each data point in a series on a line chart. For example, the first data point is 8.3, but I need the range to show 7.9 to 9. The second data point is 10.9 with a range of 10.4 to 11.1, and so on. I haven't found a way to use Excel error bars as range bars, because it only lets you format the bar for each data point so that the positive and negative values differ by the same amount (e.g. + or - .6, not + .8 and - .5). Is there a way to do this?

2. ## Re: Range bars, not error bars

Right click on your series. FormatDataSeries>Y error bars has a custom error bar option.

3. ## Re: Range bars, not error bars

@Anselm

I had the same issue with creating real range bars, but I learned a way to do it. It has a few steps, but it's worth the effort.

(Note that these directions were made using Excel for Mac 2008, but you should be able to make it work for Windows too. The buttons just might be in a slightly different place.)

1. Make a column for the minimum and maximum values for each set of numbers. Label them "Real Minimum" and "Real Maximum."
2. Make a column of the numbers you want to graph (this is usually an average).
3. Make a fourth column and label it "Relative Minimum." Use the formula bar to subtract the value of the Real Minimum from the average. This will give you a number representing the "relative distance" of the minimum from the data point. Copy this formula for your other data points.
4. Make a fifth column and label it "Relative Maximum." Again, use the formula bar, but this time subtract the average from the Real Maximum. This will give you a number representing the relative distance of the maximum from the data point.
5. Right-click on the line of the data series in your graph and click on "Format Data Series."
6. Click "Error Bars."
7. Click "Both" in the "Display" section, "Cap" in the "End Style" section, and "Custom:" in the "Error amount" section.
8. Click the "Specify Value" button.
9. For the "Positive Error Value," click the little box to the right of the field and click and drag to select all the cells under the column you labeled "Relative Maximum."
10. Click the little box again.
11. For the "Negative Error Value," again click the little box to the right of the field, then click and drag to select all the cells under the column labeled "Relative Minimum."
12. Click the little box again.
13. Click "OK," and "OK" again to accept the changes made to the graph.

You should now have range bars added to your graph! It does seem intense, but it's worth the effort. It can be done very quickly when you know what you need to do, but you still might want to create a macro for it.

There are currently 1 users browsing this thread. (0 members and 1 guests)