Chapter 19: Learning Advanced Charting

Tip

Changing the size and position of the Plot Area can have a dramatic effect on the overall look of your chart. When you’re fine-tuning a chart, you’ll probably want to experiment with various sizes and positions for the Plot Area. n

Working with Chart Titles

A chart can have several different types of titles:

•Chart title

•Category (X) axis title

•Value (Y) axis title

•Second category (X) axis title

•Second value (Y) axis title

•Depth axis title (for true 3-D charts)

The number of titles that you can use depends on the chart type. For example, a pie chart supports only a chart title because it has no axes.

To add a chart title, activate the chart and choose Chart Tools Layout Labels Chart Title. To add a title to one or more of the axes, choose Chart Tools Layout Labels Axis Titles. These controls are drop-down lists, and each has several options.

After you add a title, you can replace the default text and drag the titles to a different position. However, you can’t change the size of a title by dragging. The only way to change the size of a title is to change the font size.

Adding Free-Floating Text to a Chart

Text in a chart is not limited to titles. In fact, you can add free-floating text anywhere you want. To do so, activate the chart and choose Chart Tools Layout Insert Text Box. Click in the chart to create the text box and enter the text. You can resize the text box, move it, change its formatting, and so on. You can also add a Shape to the chart and then add text to the Shape (if the Shape is one that accepts text).

Many people prefer to use a text box in place of a chart’s “official” title elements. Resizing a title is not possible (except by changing its font size). If you use a text box, though, you can resize it by dragging its corners, change the text alignment, and even rotate it.

445

Part III: Creating Charts and Graphics

The chart title or any of the axis titles can also use a cell reference. For example, you can create a link so the chart always displays the text contained in cell A1 as its title. To create a link, select the title, type an equal sign (=), point to the cell, and press Enter. After you create the link, the Formula bar displays the cell reference when you select the title.

Working with a Legend

A chart’s legend consists of text and keys that identify the data series in the chart. A key is a small graphic that corresponds to the chart’s series (one key for each series).

To add a legend to your chart, choose Chart Tools Layout Labels Legend. This drop-down control contains several options for the legend placement. After you add a legend, you can drag it to move it anywhere you like.

Tip

If you move a legend from its default position, you may want to change the size of the Plot Area to fill in the gap left by the legend. Just select the Plot Area and drag a border to make it the desired size. n

The quickest way to remove a legend is to select the legend and then press Delete.

You can select individual items within a legend and format them separately. For example, you may want to make the text bold to draw attention to a particular data series. To select an element in the legend, first select the legend and then click the desired element.

If you didn’t include legend text when you originally selected the cells to create the chart, Excel displays Series 1, Series 2, and so on in the legend. To add series names, choose Chart Tools Design Data Select Data to display the Select Data Source dialog box (see Figure 19.6). Select the series name and click the Edit button. In the Edit Series dialog box, type the series name or enter a cell reference that contains the series name. Repeat for each series that needs naming.

FIGURE 19.6

Use the Select Data Source dialog box to change the name of a data series.

446

Chapter 19: Learning Advanced Charting

In some cases, you may prefer to omit the legend and use callouts to identify the data series. Figure 19.7 shows a chart with no legend. Instead, it uses Shapes to identify each series. These Shapes are from the Callouts section of the Chart Tools Layout Insert Shapes Gallery.

FIGURE 19.7

Using Shapes as callouts in lieu of a legend.

Copying Chart Formatting

You created a killer chart and spent hours customizing it. Now you need to create another one just like it, but with a different set of data. What are your options? You have several choices:

•Copy the formatting. Create your new chart with the default formatting. Then select your original chart and choose Home Clipboard Copy (or press Ctrl+C). Click your new chart and choose Home Clipboard Paste Paste Special. In the Paste Special dialog box, select the Formats option.

•Copy the chart; change the data sources. Press Ctrl while you click the original chart and drag. This creates an exact copy of your chart. Then choose Chart Tools Design Data Select Data. In the Select Data Source dialog box, specify the data for the new chart.

•Create a chart template. Select your chart and then choose Chart Tools Design Type Save as Template. Excel prompts you for a name. When you create your next chart, use this template as the chart type.

447

Part III: Creating Charts and Graphics

Cross-Reference

For more information about using chart templates, see “Creating Chart Templates,” later in this chapter. n

Working with Gridlines

Gridlines can help the viewer determine what the chart series represents numerically. Gridlines simply extend the tick marks on an axis. Some charts look better with gridlines; others appear more cluttered. Sometimes, horizontal gridlines alone are enough although XY charts often benefit from both horizontal and vertical gridlines.

To add or remove gridlines, choose Chart Tools Layout Axes Gridlines. This drop-down control contains options for all possible gridlines in the active chart.

Note

Each axis has two sets of gridlines: major and minor. Major units display a label. Minor units are located between the labels. n

To modify the color or thickness of a set of gridlines, click one of the gridlines and use the commands from the Chart Tools Format Shape Styles group.

If gridlines seem too overpowering, consider changing them to a lighter color or use one of the dashed options.

Modifying the Axes

Charts vary in the number of axes that they use. Pie and doughnut charts have no axes. All 2-D charts have two axes (three, if you use a secondary-value axis; four, if you use a secondary-category axis in an XY chart). True 3-D charts have three axes.

Excel gives you a great deal of control over these axes, via the Format Axis dialog box. The content of this dialog box varies depending on the type of axis selected.

Value axis

To change a value axis, right-click it and choose Format Axis. Figure 19.8 shows the Axis Options tab of the Format Axis dialog box. The other tabs in the dialog box deal with cosmetic formatting.

On the Axis Options tab, the four sets of option buttons at the top determine the scale of the axis (its minimum, maximum, and intervals). By default, Excel determines these values based on the numerical range of the data, and the settings are set to Auto. You can override Excel’s choice and set any or all of them to Fixed and then enter your own values.

448

Chapter 19: Learning Advanced Charting

FIGURE 19.8

These options are available for a Value Axis.

Adjusting the scale of a value axis can dramatically affect the chart’s appearance. Manipulating the scale, in some cases, can present a false picture of the data. Figure 19.9 shows two line charts that depict the same data. The chart on the left uses Excel’s default (Auto) axis scale values, which extend from 7,400 to 9,400. In the chart on the right, the Minimum scale value was set to 0, and the Maximum scale value was set to 10,000. The first chart makes the differences in the data seem more prominent. The second chart gives the impression that there is not much change over time.

FIGURE 19.9

These two charts show the same data, but use a different Value Axis scales.

449

Part III: Creating Charts and Graphics

The actual scale that you use depends on the situation. There are no hard-and-fast rules regarding setting scale values except that you shouldn’t misrepresent data by manipulating the chart to prove a point that doesn’t exist.

Tip

If you’re preparing several charts that use similarly scaled data, keeping the scales the same is a good idea so that the charts can be compared more easily. n

Another option in the Format Axis dialog box is Values in Reverse Order. The left chart in Figure 19.10 uses default axis settings. The right chart uses the Values in Reverse Order option, which reverses the scale’s direction. Notice that the Category Axis is at the top. If you would prefer that it remain at the bottom of the chart, select the Maximum Axis Value option for the Horizontal Axis Crosses setting.

FIGURE 19.10

The right chart uses the Values in Reverse Order option.

If the values to be plotted cover a very large numerical range, you may want to use a logarithmic scale for the value axis. A log scale is most often used for scientific applications. Figure 19.11 shows two charts. The left chart uses a standard scale, and the right chart uses a logarithmic scale.

Note

The Base setting is 10, so each scale value in the chart is 10 times greater than the one below it. Increasing the major unit to 100 results in a scale in which each tick mark value is 100 times greater than the one below. You can specify a base value between 2 and 1,000. n

On the CD

This workbook, log scale.xlsx, is available on the companion CD-ROM. n

450

Chapter 19: Learning Advanced Charting

FIGURE 19.11

These charts display the same data, but the right chart uses a logarithmic scale.

If your chart uses very large numbers, you may want to change the Display Units settings. Figure 19.12 shows a chart that uses very large numbers. The chart on the right uses the Display Units as Millions settings, with the option to Show Display Units Labels on Chart.

FIGURE 19.12

The chart on the right uses display units of millions.

The Major and Minor Tick Mark options control how the tick marks are displayed. Major tick marks are the axis tick marks that normally have labels next to them. Minor tick marks fall between the major tick marks.

451

Part III: Creating Charts and Graphics

Excel lets you position the axis labels at three different locations: Next to Axis, High, and Low. Each axis extends from –10 to +10. When you combine these settings with the Axis Crosses At option, you have a great deal of flexibility, as shown in Figure 19.13.

FIGURE 19.13

Various ways to display axis labels and crossing points.

Category axis

Figure 19.14 shows the Axis Options tab of the Format Axis dialog box when a category axis is selected. Some options are the same as those for a value axis.

Excel chooses how to display category labels, but you can override its choice. Figure 19.15 shows a column chart with month labels. Because of the lengthy category labels, Excel displays the text at an angle. If you make the chart wider, the labels will then appear horizontally. You can also adjust the labels from the Alignment tab of the Format Axis dialog box.

In some cases, you really don’t need every category label. You can adjust the Interval between Labels settings to skip some labels (and cause the text to display horizontally). Figure 19.16 shows such a chart; the Interval between Labels setting is 3.

452

Chapter 19: Learning Advanced Charting

FIGURE 19.14

These options are available for a category axis.

FIGURE 19.15

Excel determines how to display category axis labels.

453

Part III: Creating Charts and Graphics

FIGURE 19.16

Changing the Interval between Labels setting makes labels display horizontally.

When you create a chart, Excel recognizes whether your category axis contains date or time values. If so, it uses a time-based category axis. Figure 19.17 shows a simple example. Column A contains dates, and column B contains the values plotted in the column chart. The data consists of values for only 10 dates, yet Excel created the chart with 30 intervals on the category axis. It recognized that the category axis values were dates and created an equal-interval scale.

FIGURE 19.17

Excel recognizes dates and creates a time-based category axis.

You can override Excel’s decision to use a time-based category axis by choosing the Text Axis option for Axis Type. Figure 19.18 shows the chart after making this change. In this case, using a time-based category axis presents a truer picture of the data.

454