Chapter 19: Learning Advanced Charting

FIGURE 19.32

This combination chart includes a data table that displays the values of the data points.

Tip

Using a data table is probably best suited for charts on chart sheets. If you need to show the data used in an embedded chart, you can do so using data in cells, which provide you with a lot more flexibility in terms of formatting. n

Creating Chart Templates

This section describes how to create custom chart templates. A template includes customized chart formatting and settings. When you create a new chart, you can choose to use your template rather than a built-in chart type.

If you find that you are continually customizing your charts in the same way, you can probably save some time by creating a template. Or, if you create lots of combination charts, you can create a combination chart template and avoid making the manual adjustments required for a combination chart.

To create a chart template

1.Create a chart to serve as the basis for your template. The data you use for this chart is not critical, but for best results, it should be typical of the data that you’ll eventually be plotting with your custom chart type.

2.Apply any formatting and customizations that you like. This step determines how the appearance of the charts created from the template.

469

Part III: Creating Charts and Graphics

3.Activate the chart and choose Chart Tools Design Type Save as Template.

Excel displays its Save Chart Template dialog box.

4.Provide a name for the template and click Save.

To create a chart based on a template

1.Select the data to be used in the chart.

2.Choose Insert Charts Other Charts All Chart Types. Excel displays its Insert Chart dialog box.

3.From the left side of the Insert Chart dialog box, select Templates. Excel displays an icon for each custom template that has been created.

4.Click the icon that represents the template you want to use and then click OK. Excel creates the chart based on the template you selected.

Note

You can also apply a template to an existing chart. Select the chart and choose Chart Tools Design Change Chart Type. n

Learning Some Chart-Making Tricks

This section describes some interesting (and perhaps useful) chart-making tricks. Some of these tricks use little-known features, and several tricks enable you to make charts that you may have considered impossible to create.

Creating picture charts

Excel makes it easy to incorporate a pattern, texture, or graphics file for elements in your chart. Figure 19.33 shows a chart that uses a photo as the background for a chart’s Chart Area element.

To display an image in a chart element, use the Fill tab in the element’s Format dialog box. Select the Picture or Texture Fill option and then click the button that corresponds to the image source (File, Clipboard, or ClipArt). If you use the Clipboard button, make sure that you copied your image first. The other two options prompt you for the image.

Figure 19.34 shows two more examples: a pie chart that uses Office clipart as its fill; and a column chart that uses a Shape, which was inserted on a worksheet and then copied to the Clipboard.

On the CD

The examples in this section are available on the companion CD-ROM. The filename is picture charts. xlsx.

470

Chapter 19: Learning Advanced Charting

FIGURE 19.33

The Chart Area contains a photo.

Using images in a chart offers unlimited potential for creativity. The key, of course, is to resist the temptation to go overboard. A chart’s primary goal is to convey information, not to impress the viewer with your artistic skills.

Caution

Using images, especially photos, in charts can dramatically increase the size of your workbooks. n

FIGURE 19.34

The left chart uses clip art, and the right chart uses a Shape that was copied to the Clipboard.

471

Part III: Creating Charts and Graphics

Creating a thermometer chart

You’re probably familiar with a “thermometer” type display that shows the percentage of a task that has been completed. Creating such a display in Excel is very easy. The trick involves creating a chart that uses a single cell (which holds a percentage value) as a data series.

Figure 19.35 shows a worksheet set up to track daily progress toward a goal: 1,000 new customers in a 15-day period. Cell B18 contains the goal value, and cell B19 contains a simple formula that calculates the sum. Cell B21 contains a formula that calculates the percent of goal:

=B19/B18

As you enter new data in column B, the formulas display the current results.

FIGURE 19.35

This single-point chart displays progress toward a goal.

On the CD

A workbook with this example is available on the companion CD-ROM. The filename is thermometer chart.xlsx.

To make the thermometer chart, select cell B21 and create a column chart from that single cell. Notice the blank cell above cell B21. Without this blank cell, Excel uses the entire data block for the chart, not just the single cell. Because B21 is isolated from the other data, only the single cell is used.

472

Chapter 19: Learning Advanced Charting

Other changes required are to

•Select the horizontal category axis and press Delete to remove the category axis from the chart.

•Remove the legend.

•Add a text box, linked to cell B21 to display the percent accomplished.

•In the Format Data Series dialog box (Series Options tab), set the Gap width to 0, which makes the column occupy the entire width of the plot area.

•Select the Value Axis and display the Format Value Axis dialog box. In the Axis Options tab, set the Minimum to 0 and the Maximum to 1.

Make any other cosmetic adjustments to get the look you desire.

Creating a gauge chart

Figure 19.36 shows another chart based on a single cell. It’s a pie chart set up to resemble a gauge. Although this chart displays only one value (entered in cell B1), it actually uses three data points (in A4:A6).

On the CD

A workbook with this example is available on the companion CD-ROM. The filename is gauge chart. xlsx.

One slice of the pie — the slice at the bottom — always consists of 50 percent. I rotated the pie so that the 50 percent slice was at the bottom. Then I hid that slice by specifying No Fill and No Border for the data point.

FIGURE 19.36

This chart resembles a speedometer gauge and displays a value between 0 and 100 percent.

473

Part III: Creating Charts and Graphics

The other two slices are apportioned based on the value in cell B1. The formula in cell A4 is

=MIN(B1,100%)/2

This formula uses the MIN function to display the smaller of two values: either the value in cell B1 or 100 percent. It then divides this value by 2 because only the top half of the pie is relevant. Using the MIN function prevents the chart from displaying more than 100 percent.

The formula in cell A5 simply calculates the remaining part of the pie — the part to the right of the gauge’s “needle”:

=50%-A4

The chart’s title was moved below the half-pie. The chart also contains a text box, linked to cell B1, that displays the percent completed.

Displaying conditional colors in a column chart

You may have noticed the Vary Colors by Point option on the Fill tab of the Format Data Series dialog box. This option simply uses more colors for the data series. Unfortunately, the colors aren’t related to the values of the data series.

This section describes how to create a column chart in which the color of each column depends on the value that it’s displaying. Figure 19.37 shows such a chart (more impressive when you see it in color). The data used to create the chart is in range A1:F14.

FIGURE 19.37

The color of the column depends varies with the value.

474

Chapter 19: Learning Advanced Charting

On the CD

A workbook with this example is available on the companion CD-ROM. The filename is conditional colors.xlsx.

This chart displays four data series, but some data is missing for each series. The data for the chart is entered in column B. Formulas in columns C:F determine which series the number belongs to by referencing the bins in Row 1. For example, the formula in cell C3 is

=IF(B3<=$C$1,B3,””)

If the value in column B is less than the value in cell C1, the value goes in this column. The formulas are set up such that a value in column B goes into only one column in the row.

The formula in cell D3 is a bit more complex because it must determine whether cell C3 is greater than the value in cell C1 and less than or equal to the value in cell D1:

=IF(AND($B3>C$1,$B3<=D$1),$B3,””)

The four data series are overlaid on top of each other in the chart. The trick involves setting the Series Overlap value to a large number. This setting determines the spacing between the series. Use the Series Options tab of the Format Data Series dialog box to adjust this setting.

Note

Series Overlap is a single setting for the chart. If you change the setting for one series, the other series change to the same value. n

Creating a comparative histogram

With a bit of creativity, you can create charts that you may have considered impossible. For example, Figure 19.38 shows a chart sometimes referred to as a comparative histogram chart. Such charts often display population data.

On the CD

A workbook with this example is available on the companion CD-ROM. The filename is comparative histogram.xlsx.

Here’s how to create the chart:

1.Enter the data in A1:C8, as shown in Figure 19.38. Notice that the values for females are entered as negative values, which is very important.

2.Select A1:C8 and create a bar chart. Use the subtype labeled Clustered Bar.

3.Select the horizontal axis and display the Format Axis dialog box.

4.Click the Number tab and specify the 0%;0%;0% custom number format. This custom format eliminates the negative signs in the percentages.

475

Part III: Creating Charts and Graphics

5.Select the vertical axis and display the Format Axis dialog box.

6.On the Axis Options tab, set all tick marks to None and set the Axis Labels option to Low. This setting keeps the vertical axis in the center of the chart but displays the axis labels at the left side.

7.Select either data series and display the Format Data Series dialog box.

8.On the Series Options tab, set the Series Overlap to 100% and the Gap Width to 0%.

9.Delete the legend and add two text boxes to the chart (Females and Males) to substitute for the legend.

10.Apply other formatting and labels as desired.

FIGURE 19.38

A comparative histogram.

Creating a Gantt chart

A Gantt chart is a horizontal bar chart often used in project management applications. Although Excel doesn’t support Gantt charts per se, creating a simple Gantt chart is possible. The key is getting your data set up properly.

Figure 19.39 shows a Gantt chart that depicts the schedule for a project, which is in the range A2:C13. The horizontal axis represents the total time span of the project, and each bar represents a project task. The viewer can quickly see the duration for each task and identify overlapping tasks.

On the CD

A workbook with this example is available on the companion CD-ROM. The filename is gantt chart.xlsx.

476

Chapter 19: Learning Advanced Charting

FIGURE 19.39

You can create a simple Gantt chart from a bar chart.

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:

1.Select the range A2:C13, and create a stacked bar chart.

2.Delete the legend.

3.Select the category (vertical) axis and display the Format Axis dialog box.

4.From the Format Axis dialog box, 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.

5.Select the Start Date data series and display the Format Data Series dialog box.

6.Still in the Format Data Series dialog box, click the Series Options tab and set the Series Overlap to 100%. From the Fill tab, specify No Fill. From the Border Color tab, specify No Line. These steps effectively hide the data series.

7.Select the value (horizontal) axis and display the Format Axis dialog box.

8.In the Format Axis dialog box, adjust the Minimum and Maximum settings to accommodate the dates that 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

477

Part III: Creating Charts and Graphics

is 40301 (May 3, 2010) and the Maximum is 40385 (July 26, 2010). Specify 7 for the Major Unit, to display one-week intervals. Use the number tab to specify a date format for the axis labels.

9. Apply other formatting as desired.

Plotting mathematical functions with one variable

An XY chart is useful for plotting various mathematical and trigonometric functions. For example, Figure 19.40 shows a plot of the SIN function. The charts plots y for values of x (expressed in radians) from –5 to +5 in increments of 0.5. Each pair of x and y values appears as a data point in the chart, and the points connect with a line.

The function is expressed as

y = SIN(x)

The corresponding formula in cell B2 (which is copied to the cells below) is

=SIN(A2)

On the CD

The companion CD-ROM contains a general-purpose, single-variable plotting application. The file is named function plot 2D.xlsx.

FIGURE 19.40

This chart plots the SIN(x).

478

Chapter 19: Learning Advanced Charting

Plotting mathematical functions with two variables

The preceding section describes how to plot functions that use a single variable (x). You also can plot functions that use two variables. For example, the following function calculates a value of z for various values of two variables (x and y):

z = SIN(x)*COS(y)

Figure 19.41 shows a surface chart that plots the value of z for 21 x values ranging from 1 to 5, and for 21 y values ranging from 1 to 5. Both x and y use an increment of 0.2.

The formula in cell B2, copied across and down, is

=SIN($A2*COS(B$1))

On the CD

The companion CD-ROM contains a general–purpose, two-variable plotting application. The file is named function plot 3D.xlsm. This workbook contains a few simple VBA macros to allow you to change the chart’s rotation and elevation. n

FIGURE 19.41

Using a surface chart to plot a function with two variables.

479