atom

Benchmark Chart in Excel 2007:-


A benchmark chart shows sales for each period as a column chart. The quota, goal, or benchmark for the period is shown as a cap. If the sales exactly meet the quota, the sales column fits perfectly into the cap. If sales fell short of the quota, you see some whitespace between the cap and the column.

You follow these steps to create a benchmark chart like the one in the Figure (given below):



(The horizontal markers on each column indicate where the quota had been for that month.)

1. Enter Months in column A, Sales in column B, and Quota in column C.

2. Create a clustered 2-D column chart from the data. Delete the legend.

3. Click the Quota series in the chart. From the Design ribbon, choose Change Chart Type and then choose Scatter (XY). Excel automatically moves the series to a secondary axis.

4. Select one of the quota data markers. Right-click and choose Format Data Series. Choose Primary Axis.

5. Make sure the quota series is still selected. From the Layout ribbon, choose Error Bars, More Error Bar Options. Close the Error Bar Format dialog and open the Current Selection drop-down. If you see Series Quota Y Error Bars, select that and press the Delete key. Select Series Quota X Error Bars and click Format Selection. Change Fixed Value from 1.0 to 0.2. For the Line Style category, choose a 3-point width.

6. Choose Series Quota from the Current Selection drop-down. Click Format Selection. For the Marker Options category, choose None.

The horizontal lines in this chart are actually error bars that show the goal or quota for each month.