histogram

Dennis -

Make your histogram using any of these techniques.

Create a two by two range in the sheet, with Xmin and Xmax in the first

column and zeros in the second. Copy this range, select the chart, and

choose Paste Special from the Edit menu. Add the data as a new series,

with categories in the first column, but don't replace existing categories.

This series isn't visible because of the zeros, so select the original

series, and press the Up arrow key to select the hidden series. Choose

Chart Type from the Chart menu, and select an XY Scatter type.

Excel draws secondary axes for the new series. Click on the secondary Y

axis (on the right of the chart) and press the Delete key. Adjust the

scale of the secondary X axis so the scatter points fall in the right

position relative to the histogram data. Then double click on the

secondary X axis and on the Patterns tab, select None for major and

minor ticks and tick labels.

Lock the min and max scales of the Y axis (uncheck the Auto box for Min

and Max on the Scale tab), then double click the XY series, and go to

the Y Error Bars tab. Set the length of the positive error bars to the Y

scale maximum.

A little formatting and you're done. You can change the color and line

style of the error bars (a red dotted line might make a good limit

indicator). To remove the XY series from the legend, click on its legend

entry (the text, not the marker), and press Delete.

- Jon

-------

Jon Peltier, Microsoft Excel MVP

Peltier Technical Services

Tutorials and Custom Solutions