mime-version: 1.0 content-type: multipart/related; boundary="----=_NextPart_01C90F68.294FC980" This document is a Single File Web Page, also known as a Web Archive file. If you are seeing this message, your browser or editor doesn't support Web Archive files. Please download a browser that supports Web Archive, such as Windows® Internet Explorer®. ------=_NextPart_01C90F68.294FC980 mime-version: 1.0 content-location: file:///C:/679C5A97/xl_AddingLinesBackgroundElements_AF10287537.htm content-transfer-encoding: quoted-printable content-type: text/html; charset="us-ascii"
= This article is an excerpt from Excel 2007 Charts by John Walkenbach. A.k.a. = Mr. Spreadsheet, John is arguably the foremost authority on Excel. His forty= -plus books include Excel 2007 Power Programming with VBA, Excel = 2007 Formulas, and the bestselling Excel 2007 Bible, all published by Wiley. He has also written hundreds of articles and created the award-winning Power Utility Pack. To buy this b= ook, visit Wiley.com.
The following sections present examples of charts that have = been augmented in a number of ways to display various types of lines and backgr= ound elements. Several of the examples involve tricks that make use of combination charts= .
Many charts benefit from adding one or more reference lines.= Figure 1 shows an area chart that depicts a product’s defect rate over a 20= -day period. This chart displays an additional line. Data points that appear ab= ove this line represent an unacceptable level of defects.
Figure 1: This combination chart displays a comparison line.=
Adding a reference line is very simple. Just add a new serie= s to the chart that displays as a straight horizontal line. In this case, the l= ine uses the data in column C, which consists of a single value repeated for e= ach data point.
This is just a simple combination chart. The chart started a= s a line chart, and then I converted the Defect Rate series to an area chart series. I removed the gridlines to make the line more prominent. You can, = of course, add any number of reference lines to a chart. Each line requires a= new data series.
The previous section describes how to display a horizontal l= ine on a chart. Adding a vertical lin= e to a chart is a bit more challenging. Figure 2 shows a chart that displays mont= hly sales. The vertical line represents the date of a merger and provides a reference point for comparing pre-merger and post-merger sales, depicted i= n the column series.
Figure 2: The vertical line is generated by an XY series.
This chart is a combination chart that combines a column cha= rt with a scatter chart.
To create this chart, use the following steps:
= ; 1. Create a standard column chart usi= ng the data in range A2:B8.
&nbs= p; 2. Select the range D2:E4 and press C= trl+C to copy the data.
p; 3. Select the chart and choose Home
Special. In the Paste Special dialog box, select New Series, Values (Y) in
Columns, Series Names in First Row, and Category (X Labels) in First Colum=
&nbs= p; 4. Select the new series (a column se= ries), and change the chart type for the series to XY (Scatter). Use the Scatter = with Straight Lines subtype. Excel displays two secondary value axes in the cha= rt (at the top and right).
&nbs= p; 5. For each of the two new value axes, access the Format Axis dialog box, click the Axis Options tab, and change = the Minimum value to 0 and the Maximum value to 100.
= 6. Add a title, remove the legend, an= d apply other cosmetic formatting as desired.
The XY series uses an arbitrary scale of 0–100 = for both axes. This scale could be anything because the scale values are not s= hown. Using 0–100 enables you to specify the line location in terms of a percentage. In this case, the value 67 (in D3:D4) specifies a line that be= gins at 67% of the length of the category axis.
Normally, you’ll want to hide the secondary axes at th= e top and on the right. To do so, use the Format Axis dialog box and set the Axis Labels setting to None, and specify None for the Major Tick Mark Type. Note that you don’t want to remove the axes by using the Chart ToolsèLayoutèAxesèAxes control.
You can use a similar procedure to create a horizontal line = in a chart. Although the process described in the previous section is simpler, = it may not be suitable for a column chart or a line chart because the horizon= tal line does not extend all the way to the vertical borders of the plot area.=
The example in this section uses the same data as the previo= us example, but the approach to generating the vertical line is different. In= this combination chart, the vertical line is created by using an additional col= umn chart series, plotted on a secondary vertical axis. The line is formed bec= ause the plot area is visible for data points with a zero value (see Figure 3).= The advantage in using this method is that you can provide a different color background for the pre- and post-merger periods.
Figure 3: The vertical line is generated by colored columns.=
Even though both series use columns, a secondary axis= is necessary to control the gap width of the series independently. Technicall= y, this is still classified as a combination chart: a column- column combination c= hart.
Creating this chart involved the following steps:
= ; 1. Create a standard column chart usi= ng the data in range A2:B7. Delete the legend and the horizontal gridlines.
&nbs= p; 2. Select D2:D7 and press Ctrl+C to c= opy the data.
p; 3. Select the chart and choose Home
Special. In the Paste Special dialog box, select New Series and Values (Y)=
Columns. Excel adds a new column series to the chart.
&nbs= p; 4. Click the original series (not the= newly added series) and access the Series Options tab of the Format Data Series dialog box. Select the Secondary Axis option.
&nbs= p; 5. Click the newly added series and a= ccess the Format Data Series dialog box. Click the Series Options tab and set th= e Gap Width to 0. Click the Fill tab and specify a fill color (a light color is a good choice). The columns will appear as a single background block.
&nbs= p; 6. Click the chart's plot area, and a= pply a fill color.
&nbs= p; This is the background color for the Pre-Merger data.
&nbs= p; 7. Access the Format Axis dialog box = for the left value axis (the axis associated with the background series). Click the Axis Options tab and set the Maximum scale value to 1200 (which is the max= imum scale value for the right value axis).
&nbs= p; 8. Click the secondary value axis and= press Delete. Both series will use the primary axis.
= 9. Add a title and text boxes to indi= cate the pre-merger and post-merger sections of the chart.
If you followed the previous steps, you’ve real= ized that the value axis on the left is actually associated with the background column series. This is necessary because a column series plotted on the secondary axis always appears in front of a column series plotted on the primary axis.
This procedure can easily be adapted to other situations = 211; for example, dividing a chart into three vertical sections. Just change the colors of the appropriate background bars. In most cases, you’ll wan= t the background series to contain the same number of data points as the actual = data series.
The examples in th= is section demonstrate a variation on the previous concept. Figure 4 shows a = chart that displays vertical bands. Again, it’s a combination chart –= ;this time an XY scatter series combined with a column chart series.
Figure 4: The vert= ical bands are provided by a column series that uses the secondary value axis.<= /span>
The column chart s= eries, which uses the data in columns D:E, is associated with the secondary value= axis and has a scale range from 0 to 1. Normally, you would hide the secondary = axis, but it’s shown here for clarity. Note that the data consists of TRUE= and FALSE values that determine whether the band is visible. In Excel, TRUE ha= s the value of 1 and FALSE has the value of 0. Therefore, these Boolean values m= ap perfectly to the chart’s value scale.
The chart in Figur= e 5 is a line chart that plots 100 data points. The vertical bands in the background display only if the corresponding line series data point is greater than t= he previous one. Column D contains simple formulas that return TRUE or FALSE, depending on the values in column B.
Figure 6 shows ano= ther example. In this case, the “band” series consists of 50 horizo= ntal bars rather than columns. The result is a line chart that shows horizontal= bars in the background. The visibility of each of the 50 bars is controlled by changing the Boolean values in a range of 50 cells. The axes for the bar s= eries are displayed at the top of the chart in the figure, but you would normally hide these axes.
Figure 5: A vertic= al band indicates that the data point’s value is greater than that of the previous point.
Figure 6: The hori= zontal bands are provided by a bar series that uses the secondary value axis.
This techni= que does not work well for a column series. If you start with a bar chart, and then convert the data series to a column chart, the axis labels are messed up (= see the left chart in Figure 7). If you start with a column chart and convert = the band series to a bar chart series, Excel plots the horizontal bars in fron= t of the vertical columns (see the right chart in Figure 7). In other words, ne= ither option produces a good result.
Figure 7: Two ways= of displaying horizontal bands in a column chart –and neither is satisfactory.
Figure 8 shows a s= catter chart that plots 10 data points. Notice that the two value axes cross in t= he center of the chart, forming four equal-size quadrants. Each of these quad= rants is a different color – thanks to the assistance of a stacked column = chart series.
Figure 8: This sca= tter chart uses a stacked column chart to provide four different colors in the background.
Here’s how i= t’s done:
= 1. Create a standard scatter chart us= ing the data in range A2:B11. Delete the legend and gridlines.
2. Select A14:C15 and press Ctrl+C.= span>
3. Select the chart and choose Home= span>èClipboard= span>èPasteèPaste Speci= al. In the Paste Special dialog box, select New Series, Values (Y) in Columns,= and Categories (X Values) in First Column. The chart now has three XY series n= amed Series 1, Series 2, and Series 3.
4. Select Series 2 and access the For= mat Data Series dialog box. Click the Series Options tab and choose the Second= ary Axis option. Repeat these actions for Series 3.
5. Select Series 2 and change it to a= 100% stacked column chart. Repeat for Series 3.
6. Use the Chart ToolsèLayoutèAxes= èAxes contro= l and make sure that all four axes are displayed.
7. Select either Series 2 or Series 3= and access the Format Data Series dialog box. Click the Series Options tab and= set the Gap Width to 0.
8. Select each of the four individual= data points in the column chart and change their color. Remember, the first cli= ck selects the series and the second click selects the data point within the series.
9. Select the axis on top, access its= Format Axis dialog box, select the Axis Options tab, and set Axis Labels to None. Repeat these actions for the axis on the right.
10. The axis labels for the scatter chart are next to their respective axis. You may prefer to set the Tick Mark Labels option to Low, using the Axis Options tab of the Format Axis dialog box.
Another way= to get a four-color background effect is to create an image file that consists of f= our colored quadrants. Then you can use the Format Plot Area dialog box to spe= cify this file to be used as the plot area fill.