In This Chapter
Adding lines and background elements to a chart
Working with single-point charts that resemble a thermometer or gauge
Using an XY series to simulate an axis
Creating specialty charts that make use of a variety of tricks
Stacking and overlaying charts
Putting more than one chart on a chart sheet
This chapter might best be described as the catch-all chapter. You'll find a wide variety of useful charting examples that incorporate various tricks of the trade. These examples may give you some new ideas and stimulate your imagination.
Many of the examples in this chapter assume that you're familiar with the material presented in previous chapters. In other words, I focus on the general technique and assume that you know the basic procedures.
The following sections present examples of charts that have been augmented in a number of ways to display various types of lines and background elements. Several of the examples involve tricks that make use of combination charts.
Many charts benefit from adding one or more reference lines. Figure 8-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 above this line represent an unacceptable level of defects.
Adding a reference line is very simple. Just add a new series to the chart that displays as a straight horizontal line. In this case, the line uses the data in column C, which consists ofa single value repeated for each data point.
This is just a simple combination chart. The chart started as a line chart, and then I converted the Defect Rate series to an area chart series. I removed the gridlines to make theline more prominent. You can, of course, add any number of reference lines to a chart. Each line requires a new data series.
Note
This workbook, named horizontal reference line.xlsx, is available on the companion CD-ROM.
The previous section describes how to display a horizontal line on a chart. Adding a vertical line to a chart is a bit more challenging. Figure 8-2 shows a chart that displays monthly sales. The vertical line represents the date of a merger and provides a reference point for comparing pre-merger and post-merger sales, depicted in the column series.
This chart is a combination chart that combines a column chart with a scatter chart.
Note
This example and the example that follows are available in the workbook named vertical line in column chart.xlsx, on the companion CD-ROM.
To create this chart, use the following steps:
Create a standard column chart using the data in range A2:B8.
Select the range D2:E4 and press Ctrl+C to copy the data.
Select the chart and choose Home
Select the new series (a column series), and change the chart type for the series to XY (Scatter). Use the Scatter with Straight Lines subtype. Excel displays two secondaryvalue axes in the chart (at the top and right).
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.
Add a title, remove the legend, and apply other cosmetic formatting as desired.
Note
The XY series uses an arbitrary scale of 0-100 for both axes. This scale could be anything because the scale values are not shown. 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 begins at 67% of the length of the category axis.
Normally, you'll want to hide the secondary axes at the 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 forthe Major Tick Mark Type. Note that you don't want to remove the axes by using the Chart Tools
You can use a similar procedure to create a horizontal line in a chart. Although the processdescribed in the previous section is simpler, it may not be suitable for a column chart or aline chart because the horizontal 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 previous example, but the approachto generating the vertical line is different. In this combination chart, the vertical line iscreated by using an additional column chart series, plotted on a secondary vertical axis. The line is formed because the plot area is visible for data points with a zero value (see Figure 8-3). The advantage in using this method is that you can provide a different color background for the pre- and post-merger periods.
Note
Even though both series use columns, a secondary axis is necessary to control the gap width of the series independently. Technically, this is still classified as a combination chart: a column- column combination chart.
Creating this chart involved the following steps:
Create a standard column chart using the data in range A2:B7. Delete the legend and the horizontal gridlines.
Select D2:D7 and press Ctrl+C to copy the data.
Select the chart and choose Home
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.
Click the newly added series and access the Format Data Series dialog box. Click theSeries Options tab and set the 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.
Click the chart's plot area, and apply a fill color.
This is the background color for the Pre-Merger data.
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 maximum scale value for the right value axis).
Click the secondary value axis and press Delete. Both series will use the primary axis.
Add a title and text boxes to indicate the pre-merger and post-merger sections of the chart.
Note
If you followed the previous steps, you've realized that the value axis on the left is actually associated with the background column series. This is necessary because a columnseries 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 — for example, dividing a chart into three vertical sections. Just change the colors of the appropriate background bars. In most cases, you'll want the background series to contain the same number of data points as the actual data series.
The examples in this section demonstrate a variation on the previous concept. Figure 8-4 shows a chart that displays vertical bands. Again, it's a combination chart — this time an Y scatter series combined with a column chart series.
The column chart series, 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 has the value of 1 and FALSE has the value of 0. Therefore, these Boolean values map perfectly to the chart's value scale.
Note
The examples in this section are available on the companion CD-ROM. The filename is vertical and horizontal bands.xlsx.
The chart in Figure 8-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 the previousone. Column D contains simple formulas that return TRUE or FALSE, depending on the values in column B.
Figure 8-6 shows another example. In this case, the "band" series consists of 50 horizontal 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 series are displayed at the top of the chart in the figure, but you would normally hide these axes.
Figure 8-5. A vertical band indicates that the data point's value is greater than that of the previous point.
Note
This technique 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 theleft chart in Figure 8-7). If you start with a column chart and convert the band series to a bar chart series, Excel plots the horizontal bars in front of the vertical columns (see the right chart in Figure 8-7). In other words, neither option produces a good result.
Figure 8-7. Two ways of displaying horizontal bands in a column chart — and neither is satisfactory,
Tip
If you must have a column chart with horizontal bands, add drop lines to the line series and make the drop lines very wide so they look like columns; then hide the line series.
Figure 8-8 shows a scatter chart that plots 10 data points. Notice that the two value axes cross in the center of the chart, forming four equal-size quadrants. Each of these quadrants is a different color — thanks to the assistance of a stacked column chart series.
Figure 8-8. This scatter chart uses a stacked column chart to provide four different colors in the background.
Note
The example in this section is available on the companion CD-ROM. The filename is scatter chart with colored quadrants.xlsx.
Here's how it's done:
Create a standard scatter chart using the data in range A2:B11. Delete the legend and gridlines.
Select A14:C15 and press Ctrl+C.
Select the chart and choose Home
Select Series 2 and access the Format Data Series dialog box. Click the Series Options tab and choose the Secondary Axis option. Repeat these actions for Series 3.
Select Series 2 and change it to a 100% stacked column chart. Repeat for Series 3.
Use the Chart Tools
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.
Select each of the four individual data points in the column chart and change their color. Remember, the first click selects the series and the second click selects the data point within the series.
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.
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.
Tip
Another way to get a four-color background effect is to create an image file that consists of four colored quadrants. Then you can use the Format Plot Area dialog box to specify this file to be used as the plot area fill.
The examples in the following sections demonstrate various ways to display a single value in a chart.
Note
The examples in the section are available on the companion CD-ROM. The filename is single data point charts.xlsx.
Figure 8-9 shows a minimalist chart, designed to show progress toward a 100% goal. This chart is similar to a thermometer. As the value increases towards 100%, the mercury in the thermometer rises.
The chart's title is linked to a cell that contains the percent completed. All other chart elementswere removed. The chart is grouped with a text box and a shape to present a concise isplay.
Figure 8-10 shows a slightly fancier single-point chart. The worksheet is set up to track daily progress toward a goal: 1, 000 new customers in a 15-day period. Cell B18 containsthe goal value, and cell B19 contains a simple formula that sums the values in column B. Cell B21 contains a formula that calculates the percent of goal.
Following are a few other points about this chart:
The chart title appears at the bottom of the chart. I used commands in the Chart Tools
A text box (linked to cell B21) displays the percent completed.
The single-point column series has a gap width of 0 (this makes the column occupy the entire width of the plot area).
The value scale ranges from 0 to 1. If the goal is exceeded, the column will completely fill the chart.
The data series uses a gradient fill to give it a rounded appearance.
Figure 8-11 shows a variation on this theme. In this case, the chart is a line chart, with a single data point. I replaced the series marker with a shape in the form of a double-headedarrow. I also added a secondary value axis so that the scale values appear on both sides of the chart.
Note
Refer to Chapter 6 for more information about copying shapes and pasting them as series markers.
Figure 8-12 shows a pie chart set up to resemble a gauge. Although this chart displays a ingle value (entered in cell B1), it actually uses three data points (in A4:A6).
One slice of the pie — the slice at the bottom — always consists of 50%, and that slice is hidden (the slice's fill is None and its border color is No Line). The other two slices are apportioned based on the value in B1. The formula in cell B4 is as follows:
=MIN(B1, 100%)/2
This formula uses the MIN function to display the smaller of two values: the value in cell B1 or 100%. It then divides this value by 2 because you're dealing only with the visible half of the pie chart. Using the MIN function prevents the chart from displaying more than 100%.
The formula in A6, which follows, simply calculates the remaining Part of the pie — the Part to the right of the gauge's "needle."
=50%-A5
Figure 8-13 shows a variation that uses a doughnut chart with two series. Again, the lower portions of the two series are hidden. The outer series colors (red, yellow, and green) use data labels to indicate the meaning of various ratings.
Yet another variation is shown in Figure 8-14. This is a scatter chart with two data points connected by a line. All other parts of the chart are hidden. The semicircle is a shape. Cells A3 and B3 contain 0 and define the bottom point of the needle. Two formulas, which use he value in cell A1, determine the upper data point — and, consequently, the angle:
A4: =SIN((30*A1-15)*(2*PI()/60)) A5: =COS((30*A1-15)*(2*PI()/60))
The following sections describe a very useful trick that can be applied in a variety of situations. The trick involves using an XY scatter series that simulates a value axis.
Note
The examples in the section are available on the companion CD-ROM. The filename is dummy axis examples.xlsx.
A common use for a dummy value axis is to provide descriptive labels. As you probablyknow, you can't change the text used in the value axis labels — they are always valuesderived from the numbers in the chart series. You can control the number formatting andfont attributes, but the actual contents of these labels are determined by Excel and cannot be changed.
Figure 8-15 shows a chart that seems to defy this rule. This chart displays the results of 10 tests, and the value axis shows letter grades (A-F), not values. Also, notice that the labels appear in between the gridlines.
About This Chart
Following are a few key points regarding this chart:
The chart is actually a combination chart that combines a standard line chart with a scatter chart.
The actual value axis is hidden. In its place is an XY series that is formatted to look like an axis.
The data for the XY series is stored in A15:B19. The Y values represent the scores for each letter grade category. For example, an F is 0-19, a D is 20-39, and so on.
The axis labels (the letters A-F) are custom data labels for the XY series.
To better understand how a dummy value axis is set up, refer to Figure 8-16. This is a standard scatter chart, with the data points connected by lines, and series markers set todisplay a horizontal tick. It uses the five data points specified in A2:B6. Because each X value is the same (1), the series displays as a vertical line. This XY series uses custom data labels to identify each "tick." As you can see, this data series look very much like a vertical axis.
Figure 8-16. This XY series displays as a vertical line with tick marks and uses custom data labels.
Creating the Chart
Following are the steps required to create the line chart in Figure 8-15, presented at the beginning of this section.
Select the data in A1:B11 and create a standard line chart. Delete the legend.
Select A15:B19 and choose Edit
Select the chart and choose Home
Select the new line series and choose Chart Tools
Select the XY series and access the Format Data Series dialog box. Make the line color black. Using the Marker Options tab, specify a Built-In horizontal tick as the marker style.
Choose Chart Tools
Select the data labels for the XY series, and access the Format Data Labels dialog box. On the Label Options tab, specify Left for the Label Position. You may need to adjust the size and position of the plot area to accommodate the left-positioned labels.
Now it's time to clean up the axes. Select the real value axis on the left (this is the axisfor the line series). Access the Format Axis dialog box, select the Axis Options tab andset the Minimum to 0, the Maximum to 100, and the Major Unit to 20. These selectionscreate gridlines that divide the chart into five vertical sections to correspond to the five letter grades. Then click the Line Color tab and specify No Line (to hide the axis).
Select the X value axis for the XY series (it will be at the top of the chart) and press Delete to remove the axis.
Select the Y value axis for the XY series (it will be on the right side of the chart) and press Delete to delete the axis.
Finally, select each individual data label for the XY series and change the text to correspond to the letter grade labels in column C. You might prefer to create links to thecells. To create a link, click a data label, click the formula bar, type an equal sign, click the cell to link, and press Enter.
Tip
To apply the data labels automatically, refer to the nearby sidebar, "Applying custom data labels."
In the previous example, the dummy axis had equal intervals: Each Y value was separated by 20. Figure 8-17 shows another chart that uses a dummy value axis, but this time the scale intervals on the value axis vary in size. Essentially, this chart makes it easy to translate he monthly numeric rating values into descriptive text. As you can see from the chart, june is the only month in which a "Very Good" rating was attained.
Applying custom data labels
Several of the examples in this chapter use custom data labels. You may have discovered that Excel's Data Label feature has a serious limitation: You can't specify a range of cells to be used as data labels for a chart series.
You can, of course, add data labels to a series and then select each data label and edit it manually. A better approach is to use a VBA macro. The author's PUP v7 add-inprovides such a macro. (You'll find a trial version of the PUP v7 add-in on the companion CD-ROM.) The accompanying figure shows the dialog box for this utility.
This chart is very similar to the previous example but has one additional twist: It simulates gridlines by using X error bars for the XY series. The normal gridlines for the column chart series are not displayed.
The following sections contain a number of examples that demonstrate how to create charts that you may have thought were impossible. As you'll see, the key is applying a fewcharting tricks — and a bit of creativity.
A stacked column chart enables you to compare relative proportions of individual items cross categories. But this type of chart sometimes doesn't quite do the job. Figure 8-18 demonstrates the problem. The goal is to facilitate comparisons by month, across the two years (compare January '06 with January '07, and so on). Because of the data arrangement, this comparison is difficult to do. For example, the January data is separated by five columns.
Figure 8-19 shows an improved version of this chart. Rearranging the data so that the same months are contiguous, as well as inserting blank rows, solves the problem. This chart also has its gap width set to 10%. You may prefer to include an additional blank rowat the top and bottom of the series. Doing so would display a gap before the January columns and after the June columns.
Figure 8-19. Rearranging the data and inserting blank rows facilitate comparisons of the same month.
Note
The examples in this section are available on the companion CD-ROM. The filename is stacked column chart variations.xlsx.
In some cases, you may want to compare a single-value column with a stacked column. The chart in Figure 8-20, for example, displays orders for each item, along with a corresponding stacked column that depicts the Inventory amount and the In Production amount.
Excel doesn't provide a direct way to create a chart with a single column next to a stacked column, but it's fairly easy to do if you arrange your data properly. This is a standard stacked column chart generated from the data in A1:D12. The gap width was set to a small value (10%). The chart actually has 11 categories, although it appears to have only 4.
Figure 8-21 shows a variation on the previous chart. In this case, the data need not be arranged in any special way (as in the previous example). This is actually a combination chart that uses two value axes. The Orders series is assigned to the left value axis, and the other two series are assigned to the right value axis. Normally, the right axis would beremoved.
Because the series use different value axes, you can adjust the thickness of the columns independently. In this example, the gap width for the Orders series is 40% and the gap width for the stacked series is 200%.
Note
Both value axes must use the same scale. Excel's automatic scaling may cause the two value axes to use different scales. In such a case, you need to adjust one or both of theaxis scales manually.
A "step" chart is similar to a hybrid column/line chart. Figure 8-22 shows two charts that use the data in columns A and B. The top chart is a standard line chart, and the bottom chart is a column chart — modified to have a gap width of 0% and no border color. A typicalstep chart is similar to the column chart, but the columns are not visible. Rather, a step chart depicts a single line with the data points connected at right angles.
Note
The examples in this section are available on the companion CD-ROM. The filename is step chart.xlsx.
Excel does not provide a step chart type, but you can create such a chart by using a scatter chart, along with horizontal and vertical custom error bars. Figure 8-23 shows an example of this type of chart. Columns A and B contain the same data used in the previous charts. In addition, this chart uses the data in columns C and D as the source for the error bars.
Note
Refer to Chapter 5 for more information about using error bars.
Figure 8-23. This scatter chart, which resembles a step chart, uses the data in columns C and D for horizontal and vertical error bars.
Column C contains simple formulas that calculate the difference between the dates in column A. For example, cell C3 contains this formula:
=A3-A2
The formulas in column D are similar and calculate the difference between the Balance data in column B. The formula in D3, for example, is as follows:
=B3-B2
Although the chart is a scatter chart, the series line and series markers are both hidden(formatted as None). Therefore, the chart is composed entirely from the horizontal error bars (column C) and vertical error bars (column D). The error bars are formatted as heavy lines, with no caps at their ends.
The column chart shown in Figure 8-24 is a bit unusual: The width of the columns is not the same. The chart displays the Units Sold data on the value axis, and the width of each column is proportional to the Total Income for the product. In other words, this columnchart is conveying more information than a typical column chart.
Note
The examples in this section are available on the companion CD-ROM. The filename is vary column widths.xlsx.
Although you can control the width of all columns by using the Gap Width setting on the Series Options tab of the Format Data Series dialog box, Excel does not provide an option to vary the width of individual columns. This chart is not actually using the data in A1:D7. Rather, the chart consists of six series, each with 100 data points. A portion of the data is shown in Figure 8-25.
The cells in column J contain formulas that determine the number of columns to show for each of the six series, using the values in column D (Total Income). Remember, this chartcontains 100 data points. These data points are allocated among the six series. Formulas in columns K:L determine the starting row and the ending row for the data in each series. For example, Series A will display 12 of the 100 columns. Its 12 data points will be in rows 2-13. Series B will display 5 of the 100 columns, and its 5 data points will be in rows 14-18.
The formulas are relatively complex, but you can easily modify this example to handle other types of data.
Warning
This type of chart should be used with care. Although the column width is used to convey information, people may interpret the chart by comparing areas. For example, in Figure 8-24, the Region C bar has a larger area than the Region A bar. But the important value, total income, is smaller for region C.
You're probably familiar with Excel's conditional formatting feature, which enables you to modify cell formatting based on the value contained in the cell. Unfortunately, Excel doesnot provide an analogous feature for charts. The Fill tab of the Format Data Series dialog box does have an option labeled Vary Colors By Point, but the colors chosen do not depend on the value of the data point.
If you would like to display different colors on a chart based on values, you can do so manually. ut changing the colors manually is tedious, and it's not dynamic (if the valueschange, you need to re-check the colors). The technique described here takes some set-up work, but the colors change automatically if the data changes. Figure 8-26 shows a column chart that appears to display the data in column B, and the columns are colored based on the values.
Note
The example in this section is available on the companion CD-ROM. The filename is conditional column colors.xlsx.
Actually, this chart consists of four series (each using a different fill color), and it uses the data in columns C:F. The cells in these columns contain formulas that reference the data in column B and use the values in row 1 to determine whether the cell should contain the data value or display an empty string.
The formula in cell C2, for example, is as follows:
=IF(B2<=$C$1, B2,"")
This formula examines the value in cell B2. If it's less than the value in C1 (0), the value from column B is displayed. Otherwise, the formula returns an empty string.
The formulas in columns D:F are a bit more complex because they need to determine whether the value in column B falls between two values. The formula in D3, for example, is as follows:
=IF(AND($B2>C$1,$B2<=D$1),$B2,"")
Creating a column chart from the data in columns C:F produces a chart with four data series, and the chart contains gaps for the blank cells. To eliminate the gaps, adjust the Gap Width and Series Overlap settings on the Series Options tab of the Format Data Series dialog box. The chart shown has a Gap Width of 0% and a Series Overlap of 90%.
You can adjust the values in row 1 to create different numeric ranges for the colors. And, of course, you can add more series to display more than four conditional colors.
A comparative histogram, sometimes known as a population pyramid chart, compares two sets of data using horizontal bars. Figure 8-27 shows an example, which depicts sales by source (online versus offline). Although this type of chart is often used with population data, it can be used in a variety of other situations.
Note
The example in this section is available on the companion CD-ROM. The filename is comparative histogram.xlsx.
Excel does not provide this type of chart, but you can create such a chart by using the following steps:
Enter the data in A1:C8, as shown in Figure 8-27. Notice that the values for the Online series are entered as negative values, which is very important.
Select A1:C8 and create a bar chart. Use the subtype labeled Clustered Bar.
Select the horizontal axis, and specify the following custom number format on the Number tab of the Format Axis dialog box:
0;0;0
This custom format eliminates the negative signs in the values.
Select the vertical axis and display the Format Axis dialog box. On the Axis Options tab, set all tick marks to None and set the Axis Labels option to Low. This setting keeps thevertical axis in the center of the chart but displays the axis labels at the left side.
Select either of the data series and display the Format Data Series dialog box. On the Series Options tab, set the Series Overlap to 100% and the Gap Width to 0%.
Delete the legend and add two text boxes to the chart (Online and Offline) to substitute for the legend.
Apply other formatting and labels as desired.
A Gantt chart is a horizontal bar chart often used in project management applications. Although Excel doesn't support Gantt charts per se, you can fairly easily create a simpleGantt chart. The key is getting your data set up properly.
Figure 8-28 shows a Gantt chart set up to depict the schedule for a project, in range A1:C13. The horizontal axis represents the total time span of the project, and each bar representsa project task. The viewer can quickly see the duration for each task and identify overlapping tasks.
Note
The example in this section is available on the companion CD-ROM. The filename is gantt chart.xlsx.
Column A contains the Task name, column B contains the corresponding Start Date, and column C contains the Duration of the task, in days.
Follow these steps to create this chart:
Select the range A2:C13, and create a stacked bar chart.
Delete the legend.
Select the category (vertical) axis, and display the Format Axis dialog box. On the Axis Options tab, specify Categories in Reverse Order to display the tasks in order, starting at the top. Choose Horizontal Axis Crosses at Maximum Category to display the dates at the bottom.
Select the Start Date data series, and display the Format Data Series dialog box. Click the Fill tab, and specify No Fill. Click the Border Color tab and specify No Line. Thesesteps effectively hide the data series.
Select the value (horizontal) axis, and display the Format Axis dialog box. On the Axis Options tab, adjust the Minimum and Maximum settings to accommodate the datesthat you want to display on the axis. Unfortunately, you must enter these values as date serial numbers, not actual dates. In this example, the Minimum is 39880 (March 9, 2009) and the Maximum is 39962 (May 29, 2009). Specify 7 for the Major Unit, to display one-week intervals. Use the Number tab to specify a date format for the axis labels.
Apply other formatting as desired.
Tip
To determine the serial number for a date, enter the date into a cell and format the cellto use General number formatting.
Figure 8-29 shows a line chart that has its maximum and minimum values identified with a circle and a square, respectively. These identifiers are the result of using two additionalseries in the chart. You can achieve this effect manually, by adding two shapes, but using the additional series makes it fully automated.
Note
The example in this section is available on the companion CD-ROM. The filename is identify max and min data points.xlsx.
Figure 8-29. This chart uses two XY series to highlight the maximum and minimum data points in the line series.
To create this chart, using the data in range A1:B13, follow these steps:
Enter the following formula in cell C2:
=IF(B2=MAX($B$2:$B$13),B2, NA())
Enter this formula in cell D2:
=IF(B2=MIN($B$2:$B$13),B2, NA())
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.
Select C1:D13 and press Ctrl+C.
Select the chart and choose Home
Select the Max series and access the Format Data Series dialog box. Specify a circular marker, with no fill, and increase the size of the marker.
Repeat Step 6 for the Min series, but use a large, hollow square for the marker.
Add data labels to the Max and Min series (the #NA values will not appear).
Apply other cosmetic formatting as desired.
Note
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 is not plotted — which is exactly what is needed. As a result, only one data point is plotted (or more, if a tie exists for the maximum or minimum). If two or more values are tied for the minimum or maximum, all the tied values will be identified with a square or circle.
The example in this section describes how to apply shading to the region between two lines in a line chart. Figure 8-30 shows a line chart with two series. The area between the linesis shaded with a fill color.
Note
The example in this section is available on the companion CD-ROM. The filename is shade between lines.xlsx.
This type of chart requires two additional series, formatted as area chart series. One area chart series uses the data in column C. These values are the same as those in column A.You can, in fact, simply create the area chart series from the data in column A. The second area chart series uses the values in column D. These values are generated with a formula that calculates the difference between the first-line and the second-line data point. For example, the formula in D2 is as follows:
=B2−A2
After adding these two new area chart series, you need to hide the Area1 series. Do this by accessing the Format Data Series dialog box. On the Fill tab, select the No Fill option. Onthe Border Color tab, select the No Line option. Also, remove the border from the Area2 series.
Figure 8-31 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 customized datalabels. The vertical value axis for the chart is hidden, but it is set to display Values in Reverse Order so that the earliest events display higher in the vertical dimension.
Figure 8-31. This scatter chart uses data labels and vertical error bars to connect its points to the horizontal value axis.
This type of chart is limited to relatively small amounts of text — otherwise, the data labels wrap and the text may be obscured.
Note
This example is available on the companion CD-ROM. The filename is scatter chart timeline.xlsx.
The following sections present a few examples of what might be considered "impossible" charts — charts that were created by combining two or more charts. The examples in this section are not combination charts. Rather, they are two (or more) separate charts that have been combined either by stacking or overlaying.
Figure 8-32 shows a simple example of combining charts. I simply stacked these three single- series line charts vertically. I removed the category axis from the top two charts, so they appear to share a single category axis.
Note
This example is available on the companion CD-ROM. The filename is stacked and grouped.xlsx.
Tip
After creating the charts, use the tools in the Chart Tools
Figure 8-33 shows another example of stacked charts, this time with different chart types. The charts share a common category axis but use different value axes. Each chart has its own title, and I added a text box to provide a descriptive title of the collection. The three charts are stacked on top of a shape, with 3-D formatting, to provide a frame effect.
The examples in this section demonstrate chart overlays — charts that are positioned on top of other charts.
Note
The examples in this section are available on the companion CD-ROM. The filename is overlay charts.xlsx.
Combining Two Charts in One Frame
Figure 8-34 shows an example of two charts combined. The two column charts are displayed on top of a shape (to provide a frame effect). The smaller chart shows the details ofthe Q4 column. A Bent Arrow shape is used to connect the charts.
Figure 8-35 shows another example: a 3-D pie chart and a 3-D column chart, presented as a single unit. The frame effect results from adding a border around a range of cells. Both charts have a transparent chart area and plot area, and they were positioned over the bordered range. The title is actually text entered into a range of merged cells.
A 2-D Chart Combined with a 3-D Chart
If you attempt to create a combination chart that uses any of the 3-D chart types, you'll find that Excel does not allow this. If you must create such a chart, the only option is to create separate charts and overlay one on top of the other.
Figure 8-36 shows an example of a 2-D line chart overlaid on a 3-D column chart. I stripped the line chart of all elements (except the line itself) and made the Plot Area and Chart Area transparent. I then carefully sized and positioned the line chart so that it aligned properly with the 3-D chart.
Note
When overlaying charts, the stack order of the objects is very important. The top chart in the stack must be higher in the stack order. To change the stack order of objects in aworksheet, select the object, right-click, and choose Order Menu from the shortcut menu. When working with charts, you must select the chart object (not the chart). To select the chart object, press Ctrl or Shift while you click the chart.
When overlaying charts, you must keep the value scales identical. In this case, I set the value scale for both charts manually. It was still necessary to adjust the height of the line chart to force the axes to line up. Letting Excel adjust the scaling often results in mismatched scales and an inaccurate chart.
Overlaying charts is a manual task and will almost always require a bit of trial and error to get things looking right.
Creating a Stacked 3-D Column Chart
You may have discovered a limitation with Excel's 3-D stacked column charts: You can't plot additional series in the depth dimension. In other words, Excel does not provide the option of creating a true 3-D stacked column chart.
As you should know by now, work-arounds are often possible. Figure 8-37 shows three stacked 3-D column charts (one for each year), combined to display a depth dimension. This chart allows comparisons by region, month, and year.
I removed the walls from all charts, and only the frontmost chart has a value axis. The title, legend, and year labels are text boxes that I added separately. When you're combiningcharts like this, the value axes must use identical scaling. Also, any modification to the 3-D view (for example, rotation) must be applied to all the charts.
The final example in Figure 8-38 shows a standard column chart, along with another chart that simulates a "broken" value axis. This type of chart is often used when a few data points greatly exceed the others. In the example, the value for June is much larger than the other values. When plotted on a standard chart, the other values are dwarfed.
Note
The examples in this section are available on the companion CD-ROM. The filename is broken axis.xlsx.
The chart with the broken axis actually consists of two charts, shown in Figure 8-39. Both charts use the same data but have different value axis scaling. The main chart (on the left) contains the title and lots of white space above the plot area. The value axis Maximum is set to 1, 000. The secondary chart has a transparent chart area with no border, and its value axis ranges from 4, 500 to 5, 000. A freeform shape is used to indicate the fact that the column is not continuous.
Figure 8-39. The chart on the right is positioned on top of the chart on the left to form the chart shown in Figure 8-38.
Creating this type of chart is a manual process and will likely require a fair amount of tweaking.
Another approach uses a single chart, with the Maximum value for the value axis set manually to accommodate the "normal" data. A shape is placed over the "outlier" column, along with a text box that contains the value for this column (see Figure 8-40).
As you know, an Excel chart can reside in either of two locations: embedded in a worksheet or on a separate chart sheet. A worksheet can hold any number of charts, but a chart sheet holds only one chart. Well, that's how it normally works. With a little trickery, you can store multiple charts on a single chart sheet.
Following is the general procedure:
Start by creating your charts in a worksheet.
Activate an empty cell that is surrounded by empty cells.
Press F11 to create a chart sheet that contains an empty chart. The chart on this chart sheet contains a single chart element: the chart area.
Activate the worksheet that contains the embedded charts.
Click a chart and choose Chart Tools
In the Move Chart dialog box, select the Object In option, and use the drop-down control to select the chart sheet that you created in Step 3.
Click the OK button, and Excel inserts the embedded chart inside the chart area of the empty chart on the chart sheet.
Repeat Steps 4 through 7 for each of the embedded charts. Note that you can move and resize the charts on the chart sheet. One little quirk, however, is that the size of the textdoes not adjust when you change the chart size. Therefore, you may need to resize the text elements on the charts.
Figure 8-41 shows a chart sheet that contains six charts. I used the controls in the Drawing Tools
Note
This workbook, named multiple charts on chart sheet.xlsx, is available on the companion CD-ROM.
Because these charts are contained in a chart area element, you'll find that you can resize the underlying chart area, and the embedded charts will resize to correspond to the chart area. Figure 8-42 shows the six charts after I change the size of the chart area.
