Microsoft Office Tutorials and References
In Depth Information
Figure 17-12: This chart uses two XY series to highlight the maximum and minimum data points in the line
Start by creating a line chart using the data in range A1:B13:
1. Enter the following formula in cell C2:
2. Enter this formula in cell D2:
3. Copy range C2:D2 down, ending in row 13. These formulas display the maximum and minimum values in
column B, and all other cells display #NA.
4. Select C1:D13 and press Ctrl+C.
5. Select the chart and choose Home ⇒ Clipboard ⇒ Paste ⇒ Paste Special. In the Paste Special dialog box, choose
New series, Values (Y) in Columns, and Series Names in First Row. This adds two new series, named Max
6. Select the Max series and access the Format Data Series task pane. Specify a square marker, with no fill,
and increase the size of the marker.
7. Repeat Step 6 for the Min series, but use a large square for the marker.
8. Add data labels to the Max and Min series (the #NA values will not appear).
9. Apply other cosmetic formatting as desired.
The formulas entered in Steps 1 and 2 display #NA if the corresponding value in
column B is not the maximum or minimum. In a line chart, an #NA value causes a gap
to appear in the line — which is exactly what is needed. As a result, only one data point
is plotted (or more, if there is a tie for the maximum or minimum). If two or more values
are tied for the minimum or maximum, all the values will be identified with a square or
Creating a Timeline
Figure 17-13 shows a scatter chart, set up to display a timeline of events. The chart uses the data in columns A
and B, and the series uses vertical error bars to connect each marker to the timeline (the horizontal value axis).
The text consists of data labels from column C. The vertical value axis for the chart is hidden, but it is set to dis-
play Values In Reverse Order so that the earliest events display higher in the vertical dimension.