HOW TO: Plot Values Against a Recommended Range in Excel 2000

This step-by-step article shows you how to create a chart that is a combination of the line and area chart types, this chart plots your data against a recommended range.

In Microsoft Excel it is possible to display collected data against a desired or recommended range for the data. This range is usually displayed as a shaded or patterned bar on the plot area against which the data is plotted.

Create a Chart

To create a chart, follow these steps:

1. Close and save any open workbooks, and then create a new workbook. In Sheet1, type the following values:

      A1:    B1: Recommended   C1: Actual
      A2: 4  B2: 6             C2: 7.5
      A3: 4  B3: 6             C3: 8
      A4: 4  B4: 6             C4: 5.25
      A5: 4  B5: 6             C5: 3
      A6: 4  B6: 6             C6: 5
   					

2. Select cells A1:C6, and then click Chart on the Insert menu.
3. In the Chart Wizard, click Area in the Chart type list, and then click Next.
4. Click the Series tab in step 2 of the Chart Wizard, and then click Add to add a new data series to the chart.
5. Select the entry in the Category (X) axis labels box, and then press DELETE.
6. Select the entry in the Values box, press DELETE, and then type =Sheet1!A2:A6 in the Values box.
7. Click Finish to create the chart.

An Area chart with three data series appears on the worksheet.

Format the Data Series

1. If the Chart toolbar is not visible, point to Toolbars on the View menu, and then click Chart.
2. Click Series "Actual" in the Chart Objects list on the Chart toolbar. On the Format menu, click Selected Data Series.
3. On the Axis tab, click Secondary axis under Plot series on, and then click OK.
4. On the Chart menu, click Chart type, click Line in the Chart type list, and then click OK.
   
   The "Actual" data series is represented with a line.
5. In the Chart Objects list on the Chart toolbar, click Series "Recommended" to format the "Recommended" data series.
6. On the Chart menu, click Area under Chart type, click the upper-left area chart type under Chart sub-type, and then click OK.

Format the Axes Scale

You must apply the same scale to both of the value axes. In this example, the primary value axis has a maximum value of 7, and the secondary value axis has a maximum value of 9. You must change the maximum value of the primary value axis to 9. To do this, follow these steps:

1. In the Chart Objects list on the Chart toolbar, click Value Axis.
2. On the Format menu, click Selected Axis, and then click the Scale tab.
3. Type the value 9 in the Maximum box, and then click OK.

Hide the Extra Series

Apply the formatting that is necessary to hide Series3. To do this, follow these steps:

1. In the Chart Objects list on the Chart toolbar, click Series3.
2. On the Format menu, click Selected Data Series.
   
   You must select the same color for the area that you set for the plot area. By default, the plot area is gray.
3. Click an appropriate shade of gray, and then click OK.
4. In the Chart Objects list on the Chart toolbar, click Legend, and then click the legend entry once for Series3. (You must select the entire entry, including the caption.)
5. Press DELETE to remove the legend entry.

Remove Gridlines

Remove the gridlines that are automatically inserted in the chart. To do this, follow these steps:

1. On the Chart menu, click Chart Options, and then click the Gridlines tab.
2. Under Value (Y) axis, click the Major gridlines box to remove the gridlines, and then click OK.

A chart with a line that represents the data in the "Actual" column (column C) is created. A blue area represents the range of values that you typed in columns A and B in step 2.

For more information about creating charts, click Microsoft Excel Help on the Help menu, type about charts in the Office Assistant or the Answer Wizard, and then click Search to view the topic.