atom

Delta Chart in Excel 2007:-

You can use a delta chart to plot revenue and a quota as line charts. A special data marker appears halfway between the two lines to show the percentage of quota. Look into the Figure (given below). The hard part of creating a delta chart is getting the labels to float halfway in between the two lines.



(The labels automatically float halfway between the two lines.)

You follow these steps to create a delta chart:

1. Set up a dataset with months in column A, quota in column B, and revenue in column C.

2. In column D, enter the following formula to average revenue and quota:

=AVERAGE(B2;C2)

This will be the location point for the data label. Copy the formula down the column.

3. In column E, enter the formula =C2/B2 to hold the label. This formula will show the percentage to quota. Format the results as a percentage with 0 decimal places and copy the formula down the column.

4. Create a chart from cells A113. From the Insert ribbon, choose Line, Line with Markers.

5. Select Value Axis from the Current Selection drop-down on the Layout ribbon. Click Format Selection. Choose a fixed minimum and a fixed maximum that are appropriate to zoom in on the data in the chart.

6. Choose Series Average from the Current Selection drop-down on the Layout ribbon. Click Format Selection. Choose to move the series to the secondary axis. This allows the labels to be adjusted for just this series.

7. Choose Secondary Vertical (Value) Axis from the Current Selection drop-down on the Layout ribbon. Choose the same minimum and maximum as in step 5.

8. Close the Format dialog box.

9. Click the Average series to select it. From the Design ribbon, choose Select Data.

10. Click the Average series on the left side of the Select Data Source dialog. On the right side of the dialog, click the Edit button. Excel displays the Axis Labels dialog, where you can choose new Horizontal Category Axis Labels. Point to the percentages in column E. Click OK to close the Axis Labels dialog. Click OK to close the Select Data Source dialog.

11. Make sure Average Series is still selected. From the Layout ribbon, choose Data Labels, More Data Label Options. In the Format Data Labels dialog, choose Category Name and deselect Value. Then do the following:

• For Label Position, choose Center.

• For Fill, choose Solid Fill and choose a light color.

• For Border Color, choose Solid Line and choose a dark color.

12. Click the Average series to select it. From the Format ribbon, choose Shape Fill, No Fill. Choose Format, Shape Outline and select No Outline. Close the Format dialog box.

13. From the Layout ribbon, choose Lines, High-Low Lines. Select High Low Lines 1 from the Current Selection drop-down. From the Format ribbon, choose Shape Outline, Dashes and choose the fourth dash selection.

14. From the Layout ribbon, choose Axes, Secondary Value Axis and set it to None.

15. Click the Average entry in the legend. The first click selects the whole legend. The second click selects just the Average entry. Press Delete to remove that entry.