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" (without the quotation marks) 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" (without the quotation marks) 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:

   ARTICLE-ID: Q152819
   TITLE     : 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: October 31, 1998