XL97: How to Plot Values Against a Recommended Range (161670)

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: