How to Plot Values Against a Recommended Range

MORE INFORMATION

You can do this by plotting the recommended area as two area plots. The lower of the two series has its area the same color as the chart background. The higher of the two series has a different color pattern. The actual data is then plotted as a line.

Enter the following sample data on a spreadsheet. This example will demonstrate a recommended area between 5 and 6.

   A1:    B1: Recommended  C1: Actual
   A2: 5  B2: 6            C2: 10
   A3: 5  B3: 6            C3: 4
   A4: 5  B4: 6            C4: 5.5
   A5: 5  B5: 6            C5: 6
   A6: 5  B6: 6            C6: 7
				

Steps to Create Chart

1. Select all the data and start the Chart Wizard, either as an embedded chart or as a separate chart sheet. Click Next.
2. In the CharWizard - Step 2 of 5 dialog box, select Area chart. This will plot a stacked area chart. Click Next.
3. In Step 3 of 5, select option 1, and then click Next.
4. In step 4 of 5, set "Use First x Column(s) for Category (X) Axis Labels" to 0 (zero). Click Finish.
5. Activate the chart and select the series depicting the Actual data. On the Format menu, click Selected Data Series, and then click the Axis tab. Under "Plot Series on," click to select Secondary Axis, and then click OK.
6. To change this series to the chart type Line, on the Format menu, click Area Group 2, click Chart Type, click Line, and then click OK.

These next steps will correctly format and change the color of the remaining two series.

1. Both of the Value (Y) axes need to be set to the same scale. To do this, take note of the largest Maximum and the lowest Minimum. Then select the smaller of the two axes, and click Selected Axis on the Format menu. On the Scale tab, change the Maximum to match the larger value; if necessary, change the Minimum to match the lowest value and then click OK. If adjustment to the other axis is required, select it and repeat.
2. On the Format menu, click Area Group and select the first subtype on the Subtype tab. This is the unstacked area chart type. This subtype will place one series behind the other. The second series will be in the back. Do not click OK and go to step 9.
3. To correct the order in which the data series for the recommended range are plotted so that the smaller of the two is on top, click the Series Order tab and move the data series called "Recommended" to the top of the list. The other data series has no title. Click OK.
4. Select the larger of the two series plotted as area. On the Format menu click Selected Data Series, and then click the Patterns tab. Under Color, select white. Make sure that the None option is not selected. Click OK.
5. Select the smaller of the two series plotted as area and change the area color to the same color as the plot background color as Step 10.

The chart now appears to have a horizontal white bar outlining the recommended range of the data.

1. The last item to clean up is the legend. This is purely cosmetic. Click the legend key for the smaller of the two area plots. There will be only a Data Marker key with no label. Press the DELETE key. Note, you must be sure to select the entire entry, label and marker, or you will delete the series from the chart.