XL97: How to Plot Values Against a Recommended Range

ID: Q161670

The information in this article applies to:
  • Microsoft Excel 97 for Windows

SUMMARY

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. This article contains an example that creates a chart, which is a combination of the line and area chart types, that plots your data against a recommended range.


MORE INFORMATION

Creating a Chart

To create a chart, use the following 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 click Chart on the Insert menu.


  3. In the Chart Wizard, click Area in the Chart Type list, and then click Next. Click the Series tab in step 2 of the Chart Wizard. Click Add to add a new data series to the chart.


  4. Select the entry in the "Category (X) axis labels" box, and then press DELETE. Select the entry in the Values box, and then press DELETE. Type =Sheet1!A2:A6 in the Values box.


  5. Click Finish to create the chart.


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

Formatting the Data Series

  1. If the Chart toolbar is not visible, point to Toolbars on the View menu, and then click Chart. Click 'Series "Actual"' in the Chart Objects list on the Chart toolbar.


  2. On the Format menu, click Selected Data Series. Click the Axis tab. Under Plot Series On, click Secondary Axis and click OK.


  3. 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. Next, format the "Recommended" data series.


  4. In the Chart Objects list on the Chart toolbar, click 'Series "Recommended".'


  5. On the Chart menu, click Chart Type. Under "Chart sub-type," click the upper-left area chart type, and then click OK.


Formatting 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, use the following steps:

  1. In the Chart Objects list on the Chart toolbar, click Value Axis.


  2. On the Format menu, click Selected Axis. Click the Scale tab. Type the value 9 in the Maximum box, and then click OK.


Hiding the Extra Series

Next, apply the formatting that is necessary to hide Series3.

  1. In the Chart Objects list on the Chart toolbar, click Series3.


  2. On the Format menu, click Selected Data Series.

    You must select a color for the area that matches the color 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. Click the legend entry once for Series3. (You must select the entire entry, including the caption.) Press DELETE to remove the legend entry.


Removing Gridlines

Next, remove the gridlines that are automatically inserted in the chart. To do this, use the following steps:

  1. On the Chart menu, click Chart Options. Click the Gridlines tab.


  2. Under Value (Y) Axis, click the Major gridlines box to remove the gridlines.


  3. 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 additional information on doing this in earlier versions of Microsoft Excel, please see the following article in the Microsoft Knowledge Base:
Q152819 How to Create a Chart with Recommended Range


REFERENCES

For more information about creating charts, click the Index tab in Microsoft Excel Help, type the following text

charts, creating
and then double-click the selected text to go to the "Create a chart (Microsoft Excel)" topic.

Additional query words: XL97

Keywords : kbtool kbualink97 kbchart
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: November 4, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.