Modifying a chart is similar to everything else you do in Excel: First you make a selection (in this case, select a chart element); then you issue a command to do something with the selection.

You can select only one chart element at a time. For example, if you want to change the font for two axis labels, you must work on each label separately. The exceptions to the single-selection rule are elements that consist of multiple parts, such as gridlines. Selecting one gridline selects them all.

Excel provides three ways to select a particular chart element:

These selection methods are described in the following sections.

Selecting with the Mouse

To select a chart element with your mouse, just click the element.

Tip

To ensure that you've selected the chart element that you intended to select, check the name that's displayed in the Chart Elements control. The Chart Elements control is available in two Ribbon groups: Chart Tools

When you move the cursor over a selected chart, a small "chart tip" displays the name of the chart element under the mouse pointer. When the mouse pointer is over a data point, the chart tip also displays the series, category, and value of the data point. If you find these chart tips annoying, you can turn them off. Select Office

Some chart elements (such as a chart series, a legend, and data labels) consist of multiple items. For example, a chart series is made up of individual data points. To select a single data point, you need to click twice: First click the series to select it; then click the specific element within the series (for example, a column or a line chart marker). Selecting an individual element enables you to apply formatting only to a particular data point in a series. This might be useful if you'd like one marker in a line chart to stand out from the others.

Selecting with the Keyboard

When a chart is active, you can use the up- and down-arrow keys on your keyboard to cycle among the chart's elements. Again, keep your eye on the Chart Elements control to verify which element is selected.

When a chart series is selected, use the left- and right-arrow keys to select an individual data point within the series. Similarly, when a set of data labels is selected, you can select a specific data label by using the left- or right-arrow key. And when a legend is selected, you can select individual elements within the legend by using the left- or right-arrow keys.

Selecting with the Chart Elements Control

As I noted earlier, the Chart Elements control displays the name of the selected chart element. This control contains a drop-down list of all chart elements (except shapes and text boxes), so you can also use it to select a particular chart element.

Tip

Although the Chart Elements control is available in two Ribbon groups (Chart Tools

The Chart Elements control is a drop-down list that lets you select a particular chart element from the active chart (see Figure 4-1). This control lists only the top-level elements in the chart. To select an individual data point within a series, for example, you need to select the series and then use one of the other techniques to select the desired data point.

Figure 4-1. Use the Chart Elements control to select an element on a chart.

Table 4-1 contains a list of the various chart elements that you may encounter. Note that the actual chart elements that are present in a particular chart depend on the chart type and on the customizations that you've performed on the chart.

When a chart element is selected, you have some choices as to which user interface method you can use to format the element:

Formatting by Using the Robbon

The controls in the Chart Tools

For a bit more control, follow these steps:

Note that you can modify the Shape Fill, Shape Outline, and Shape Effects for almost every element in a chart.

Here's one way to change the formatting of a chart's title so that the text is white on a black background:

Notice that some of the controls in the Home

Formatting by Using tha Mini Toolbar

When you right-click a chart element that contains text, Excel displays its shortcut menu, with the Mini toolbar on top. Figure 4-2 shows the Mini toolbar that appears when you right-click a chart title. Use the Mini toolbar to make formatting changes to the text (including the fill and border). Note that the Mini toolbar also works if you've selected only some of the characters in the chart element. In such a case, the text formatting applies only to the selected characters.

Formatting by Using the Format Dialog Box

For complete control over text element formatting, use the Format dialog box. Each chart element has a unique Format dialog box, and the dialog box has several tabs.

Figure 4-2. You can use the Mini toolbar to format chart elements that contain text.

You can access the Format dialog box by using either of the following methods:

In addition, some of the Ribbon controls contain a menu item that, when clicked, opens the Format dialog box and displays a specific tab. For example, when you choose Chart Tools

Figure 4-3 shows an example of a Format dialog box. Specifically, the figure shows the Legend Options tab of the Format Legend dialog box. As I noted, each chart element has a different Format dialog box.

Figure 4-3. Each chart element has its own Format dialog box. This dialog box controls formatting for the chart's legend.

Figure 4-4 shows the Fill tab in the Format Chart Area dialog box when the Solid Fill option is selected. The controls on this tab change, depending on which option is selected.

Although the Fill tabs of the various Format dialog boxes are similar, they are not identical. Depending on the chart element, the dialog box may have additional options that are relevant for the selected item. For example, the Fill tab of the Format Data Series dialog box includes a check box that can vary the colors for each data point.

Figure 4-4. The Fill tab of the Format Chart Area dialog box.

Not all chart elements can be filled. For example, the Format Major Gridlines dialog box does not have a Fill tab because filling a line makes no sense. You can, however, change the gridline formatting by using the tabs that are displayed.

The main Fill tab options are as follows:

Figure 4-5 shows a rather ugly chart with various types of fill formatting applied. The column data series has clip art, in the form of stacked monkeys. The plot area uses a texture, and the chart area uses a gradient fill.

Figure 4-5. Using too many fill types is a quick way to create a very ugly chart.

A border is the line around an object. Excel offers four general choices for formatting a border:

Figure 4-6 shows the Border Styles tab of the Format Chart Area dialog box. If you explore this dialog box, you'll soon discover that a border can have a huge number of variations. Keep in mind that all settings are not available for all chart elements. For example, the Arrow Settings are disabled when a chart element that can't display an arrow is selected.

Specifying gradients

The Fill tab in many of the Format dialog boxes lets you specify a gradient fill for the chart element. Excel includes 24 preset color gradients, and each of these can be changed by setting the type (linear, radial, rectangular, or path). For a linear or radial gradient, you can also specify a direction, and for a linear fill you can specify the direction more precisely in terms of an angle (0 to 360 degrees). These combinations work out to tens of thousands of different prebuilt gradients.

But the fun is only beginning. You can create your own gradients by defining up to ten "stops" in the dialog box. A stop is a color, a transparency setting, and a position (expressed as a percent) within the gradient.

The accompanying figure shows a custom gradient applied to a column series. This linear gradient has five stops, is displayed at a 0-degree angle, and uses these settings:

• Stop 1: Stop position 0%, color black

• Stop 2: Stop position 25%, color white

• Stop 3: Stop position 50%, color black

• Stop 4: Stop position 75%, color white

• Stop 5: Stop position 100%, color black

The best way to find out about gradients is to experiment. You can spend quite a bit of time creating a custom gradient. But, unfortunately, you can't save a custom gradient.

Figure 4-7 shows a simple line chart with some extreme border formatting applied — for demonstration purposes only. The data series has thick lines and arrows, the plot area has a line with dashes, the horizontal axis has a thick line with a round cap type, and the chart area has a compound line with rounded corners.

Figure 4-6. Some of the settings available for a chart element border.

Figure 4-7. Extreme border formatting.

The chart area is an object that contains all other elements on the chart. You can think of it as a chart's master background. The chart area is always the same size as the chart object (the chart's container).

When the chart area is selected, you can adjust the font for all the chart elements. In other words, if you want to make all text in a chart 12 point, select the chart area and then apply the font formatting.

In some cases, you may want to make the chart area transparent so that the underlying worksheet shows through. Figure 4-8 shows a column chart with a transparent chart area. You can accomplish this by setting the chart area's fill to No Fill, or set it to a Solid Fill and make it 100% transparent.

When a chart is on a chart sheet, you can resize the chart by dragging a corner of the chart area. After you've made the chart area smaller, you can drag it to a different location on the chart sheet.

The plot area is the Part of the chart that contains the actual chart. The plot area contains all chart elements except the chart title and the legend.

Figure 4-8. The chart area for this chart is transparent. The plot area, however, contains a fill color.

Although the plot area consists of elements such as axes and axis labels, when you change the fill of the plot area, these "outside" elements are not affected — except for a 3-D chart. Figure 4-9 shows a 2-D column chart and a 3-D column chart. Both charts have their plot area shaded and enclosed in a heavy dashed border. Notice that the plot area for the 3-D chart includes the axis labels. Typically, a 3-D chart has a transparent plot area, and color fills are used for the walls and floor elements.

You can insert an image into the plot area. To do so, use the Fill tab of the Format Plot Area dialog box, and choose the Picture or Texture Fill option. The image can come from a file, the clipboard, or clip art. Figure 4-10 shows a column chart that uses a graphic in the plot area.

To reposition the plot area within the chart area, select the plot area and then drag a border to move it. To change the size of the plot area, drag one of the corner "handles." If you like, you can expand the plot area so that it fills the entire chart area.

You'll find that different chart types vary in how they respond to changes in the plot area dimensions. For example, you cannot change the relative dimensions of the plot area of a pie chart or a radar chart (it's always square). But with other chart types, you can change the aspect ratio of the plot area by changing either the height or the width.

Also, be aware that the size of the plot area can be changed automatically when you adjust other elements of your chart. For example, if you add a legend or title to a chart, the size of the plot area may be reduced to accommodate the legend.

Figure 4-9. Formatting the plot area for a 3-D chart includes the axes and axis labels.

Figure 4-10. The plot area for this chart uses a graphic image.

Basic series formatting is very straightforward: Just select the data series on your chart and use the tools in the Chart Tools

You can add a picture to several chart elements, including data markers on line charts and series fills for column, bar, area, bubble, and filled radar charts. Figure 4-11 shows a column chart that uses a clip art image of a car. The picture was added using the Fill tab of the Format Data Series dialog box. I selected the Picture or Texture Fill option, and then clicked the Clip Art button to select the image. In addition, I scaled the image so that each car represents 20 units.

Figure 4-11. This column chart uses a clip art image.

Figure 4-12 shows another example. The data markers in this line chart display a shape that was inserted in the worksheet and then copied to the clipboard. I selected the line series and pressed Ctrl+V to paste the shape.

You can also use the Marker Fill tab of the Format Data Series dialog box to specify Picture or Texture Fill. However, the result is very different. If you use the Clipboard button to paste the copied shape, the pasted image will fill the existing marker (not replace it). You'll probably need to increase the marker size and hide the marker borders.

Figure 4-12. The data markers use a shape that was copied to the clipboard.

Chart series offer a number of additional options. These options are located in the Series Options tab of the Format Data Series dialog box. The set of options varies, depending on the chart type of the series. In most cases, the options are self-explanatory. But, if you are unsure about a particular series option, try it! If the result isn't satisfactory, change the setting to its original value or choose Undo.

About those fancy effects

Excel 2007 includes several new formatting options, which are known as effects. Access these effects by choosing Chart Tools

Following is a general description of the effect types:

• Shadow: Adds a highly customizable shadow to the selected chart element. Choose from a number of prebuilt shadows, or create your own using the Shadow tab of the Format dialog box. Shadows, when used tastefully, can improve the appearance of a chart by adding depth.

• Reflection: Adds one of six prebuilt reflections. As far as I can tell, this effect is not available for any native chart element. However, you can apply a reflection to shapes or text boxes that you add to a chart.

• Glow: Adds a color glow around the element. I have yet to see a chart that is improved by adding a glow to any element.

• Soft Edges: Makes the edges of the element softer. Extreme settings make the element appear to be out of focus, smaller, or even disappear.

• Bevel: Adds a 3-D bevel look to the element. This effect is highly customizable, and you can use it to create a frame for your chart (see the accompanying figure).

• 3-D Rotation: This effect does not work with any chart elements.

Perhaps the best advice regarding these effects is to use them sparingly with charts. Generally, a chart's formatting shouldn't draw attention away from the point you're trying to make with the chart.

Figure 4-13, for example, shows the series options available for a doughnut chart. This particular chart type has three series options, which affect the appearance of the series.

Figure 4-13. The Series Options tab of the Format Data Series dialog box lists additional options.

Figure 4-14 shows an example of modifying series settings. The chart on the left uses the default settings (Series Overlap of 0% and Gap Width of 150%). The chart on the right uses a Series Overlap of 50% and a Gap Width of 28%.

Figure 4-14. A column chart, before and after adjusting the Series Overlap and Gap Width settings.

To add a chart title to a chart, activate the chart and use the Chart Tools

To add axis titles to a chart, activate the chart and use the Chart Tools

When you add a title to a chart, Excel inserts generic text to help you identify the title. To edit the text used in a chart title, click the title once to select it; then click a second time inside the text area. If the title has a vertical orientation, things get a bit tricky, because you need to use the up- and down-arrow keys rather than the left- and right-arrow keys.

Unfortunately, Excel does not provide a "one-stop" place to change all aspects of a chart title. The Format Chart Title dialog box provides options for changing the fill, border, shadows, 3-D format, and alignment. If you want to change anything related to the font, you need to use the Ribbon (or, right-click and use the Mini toolbar). Yet another option is to right-click the chart element and choose Font from the shortcut menu. This displays the Font dialog box, with options that aren't available elsewhere. For example, the Font dialogbox lets you control the character spacing of the text.

Most of the font changes you make will use the tools in the Home

Figure 4-16. Formatting has been applied to individual characters in the chart's title.

When you create a chart, you might like to have some of the chart's text elements linked to cells. That way, when you change the text in the cell, the corresponding chart element updates. And, of course, you can even link chart text elements to cells that contain a formula. For example, you might link the chart title to a cell that contains a formula thatreturns the current date.

You can create a link to a cell for the chart title, a vertical axis title, or a horizontal axis title. Follow these steps:

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 by inserting a text box into the chart. To do so, follow these steps:

1. Select the chart.

2. Choose Chart Tools

   

3. Click and drag within the chart to create the text box.

4. Start typing the text.

You can click and drag the text box to change its size or location. And when the text box is selected, you can access the formatting tools using the controls on the Drawing Tools

The accompanying figure shows a chart with a text box that contains quite a bit of formatted text. The chart's plot area was reduced in size to accommodate the text box.

If you would like to link the text box to a cell, follow these steps:

1. Select the text box.

2. Click the formula bar.

3. Type an equal sign (=).

4. Click the cell that will be linked to the chart element.

After you create the link, the text box will always display the contents of the cell it's linked to.

Some people prefer to use a text box in place of a chart's title because a text box provides a lot more control over formatting. When a text box is selected, its Format Shape dialog box provides several additional options, compared to the Format Chart Title dialog box.

Figure 4-17 shows a chart that has links for the following elements: chart title, the vertical axis title, the horizontal axis title, and a text box.

Figure 4-17. The titles in this chart are linked to cells.

To add a legend to your chart, choose Chart Tools

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

To move a legend, click it and drag it to the desired location. Or, you can use one of the options in the Chart Tools

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.

To change the size of a legend, select it and drag any of its corners. Excel will adjust the legend automatically and may display it in multiple columns.

You can select an individual legend entry within a legend and format it 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 entry.

You can't change the formatting of individual characters in a legend entry. For example, if you'd like the legend to display a superscript or subscript character, you're out of luck.

When a single legend entry is selected, you can use the Format Legend Entry dialog box to format the entry. When a legend entry is selected and you apply any type of formatting except text formatting, the formatting affects the legend key and the corresponding series. In other words, the appearance of the legend key will always correspond to the data series.

The legend text corresponds to the names of the series on the chart. If you didn't include series names when you originally selected the cells to create the chart, Excel displays a default series name (Series 1, Series 2, and so on) in the legend.

To add series names, choose Chart Tools

For some charts, you may prefer that one or more of the data series not appear in the legend. To delete a legend entry, just select it and press Delete. The legend entry will be deleted, but the data series will remain intact.

If you've deleted one or more legend entries, you can restore the legend to its original state by deleting the entire legend and then adding it back.

Before getting into the details of formatting, it's important to understand the difference between a category axis and a value axis. A category axis displays arbitrary text, whereas a value axis displays numerical intervals. Figure 4-19 shows a simple column chart with two series. The horizontal category axis displays labels that represent the categories. The vertical value axis, on the other hand, is a numerical scale.

Figure 4-19. The category axis displays arbitrary labels, whereas the value axis displays a numerical scale.

In this example, the category labels happen to be text. Alternatively, the categories could be numbers. Figure 4-20 shows the same chart after replacing the category labels with numbers. Even though the chart becomes fairly meaningless, it should be clear that the category axis does not display a true numeric scale. The numbers displayed are completely arbitrary, and the chart itself was not affected by changing these labels.

Figure 4-20. The category labels have been replaced with numbers — but the numbers do not function as numbers.

Two of Excel's chart types are different from the other chart types in one important respect. Scatter charts and bubble charts use two value axes. For these chart types, both axes represent numeric scales.

Figure 4-21 shows two charts (a scatter chart and a line chart) that use the same data. The data shows world population estimates for various years. Note that the interval between the years in column A is not consistent.

The scatter chart, which uses two value axes, plots the years as numeric values. The line chart, on the other hand, uses a (non-numeric) category axis, and it assumes that the categories (the years) are equally spaced. This, of course, is not a valid assumption, and the line chart presents a very inaccurate picture of the population growth: It appears to be linear, but it's definitely not.

The numerical range of a value axis represents the axis's scale. By default, Excel automatically scales each value axis. It determines the minimum and maximum scale values for the axis, based on the numeric range of the data. Excel also automatically calculates a major unit and a minor unit for each axis scale. These settings determine how many intervals (or tick marks) are displayed on the axis and determine how many gridlines are displayed. In addition, the value at which the axis crosses the category axis is also calculated automatically.

Figure 4-21. These charts plot the same data but present very different pictures.

You can, of course, override this automatic behavior and specify your own minimum, maximum, major unit, minor unit, and cross-over for any value axis. You set these specifications on the Axis Options tab of the Format Axis dialog box (see Figure 4-22).

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 default setting is Auto. You can override Excel's choice and set any or all of them to Fixed and then enter your own values.

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 4-23 shows two line charts that depict the same data. The top chart uses Excel's default axis scale values, which extend from 8, 200 to 9, 200. In the bottom chart, the Minimum scalevalue was set to 0, and the Maximum scale value was set to 10, 000. A casual viewer might draw two very different conclusions from these charts. The top chart makes the differences in the data seem more prominent. The lower chart gives the impression that not much change has occurred over time.

Figure 4-22. The Axis Options tab of the Format Axis dialog box.

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. In addition, most agree that the value axis of a bar or column chart should always start at zero (and even Excel follows that rule).

If you're preparing several charts that use similarly scaled data, keeping the scales constant across all charts facilitates comparisons across charts. The charts in Figure 4-24 show the distribution of responses for two survey questions. For the top chart, the value axis scale ranges from 0% to 50%. For the bottom chart, the value axis scale extends from 0% to 35%. Because the same scale was not used on the value axes, however, comparing the responses across survey items is difficult.

Another option in the Format Axis dialog box is Values in Reverse Order. The top chart in Figure 4-25 uses default axis settings. The bottom 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.

If the values to be plotted cover a very large range, you may want to use a logarithmic scale for the value axis. A log scale is most often used for scientific applications. Figure 4-26 shows two charts. The top chart uses a standard scale, and the bottom chart uses a logarithmic scale. Note that the base is 10, so each scale value in the chart is 10 times greater than the one below it. Increasing the base unit to 100 would result in a scale in which each tick mark value is 100 times greater than the one below.

Figure 4-23. These two charts show the same data, but they use different value axis scales.

Figure 4-24. These charts use different scales on the value axis, making a comparison between the two difficult.

Figure 4-25. The bottom chart uses the Values in Reverse Order option.

Figure 4-26. These charts display the same data, but the lower chart uses a logarithmic scale.

If your chart uses very large numbers, you may want to change the Display Units settings. Figure 4-27 shows a chart that uses very large numbers. The lower chart uses the Display Units as Millions setting, with the option to Show Display Units Label on Chart. Excel inserted the label "Millions," which I edited to display as "Million of Miles."

Figure 4-27. The lower chart uses display units of millions.

#,##0,

An axis also has tick marks — the short lines that depict the scale units and are perpendicular to the axis. In the Axis Options dialog box, you can select the type of tick mark for the major units and the minor units. The options are as follows:

MAX + 0.05 * (MAX –MIN)

MIN –[(MAX –MIN) / 2]

You can also control the position of the tick mark labels. The options are as follows:

When you combine these settings with the Axis Crosses At option, you have a great deal of flexibility, as shown in Figure 4-28. These charts all display the same data, but the axes are formatted differently.

Figure 4-28. Various ways to display axis labels and crossing points.

When you create a chart, Excel attempts to determine whether your category axis contains date or time values. If so, it creates a time-series chart. Figure 4-29 shows a simple example. Column A contains dates, and column B contains the values plotted on the column chart. The data consists of values for only 10 dates, yet Excel created the chart with 31 intervals on the category axis. It recognized that the category axis values were dates, and created an equal-interval scale.

Figure 4-29. Excel recognizes the dates and creates a time-based category axis.

If you would like to override Excel's decision to use a time-based category axis, you need to access the Axes Options tab of the Format Axis dialog box. There, you'll discover that the default category axis option is Automatically Select Based on Data. Change this option to Text Axis, and the chart will resemble Figure 4-30. On this chart, the dates are treated as arbitrary text labels.

Figure 4-30. The previous chart, using a standard category axis.

When a category axis uses dates, the Axis Options tab of the Format Axis dialog box lets you specify the Base Unit, the Major Unit, and the Minor Unit — each in terms of days, months, or years (see Figure 4-31).

Figure 4-31. When a category axis uses dates, the Format Axis dialog box displays additional options.

If you need a time-scale axis for smaller units (such as hours), you need to use a scatter chart. That's because a date-scale axis treats all values as integers. Therefore, every time value is plotted as midnight of that day. Figure 4-32 shows a scatter chart that plots scheduledversus actual arrival times for flights. Note that both of the value axes display times, in one-hour increments.

Unfortunately, Excel does not allow you to specify time values on the Axis Options tab of the Format Axis dialog box. If you want to override the default minimum, maximum, or major unit values, you must manually convert the time value to a decimal value. In previous versions of Excel, you could simply enter a time value, and Excel would perform the conversion.

Figure 4-32. This scatter chart displays times on both value axes.

For this chart, I entered the following scale values:

To convert a time value to a decimal number, enter the time value into a cell. Then apply General number formatting to the cell. Time values are expressed as a percentage of a 24-hour day. For example, 12:00 noon is 0.50.

Most of the time, the labels on a category axis consist of data from a single column or row. You can, however, create multiline category labels, as shown in Figure 4-33. This chart uses the text in columns A:C for the category axis labels.

When this chart was created, range A1:E10 was selected. Excel determined automatically that the first three columns would be used for the category axis labels.

Figure 4-33. The category axis contains labels from three columns.

To change the visibility of an axis from the chart, use the Chart Tools

A more direct way to remove an axis is to select it and then press Delete.

Figure 4-34 shows three charts with no axes displayed. Using data labels makes the value axis superfluous, and it is assumed that the reader understands what the horizontal axis represents.

A value axis, by default, displays its values using the same number format that's used by the chart's data. You can provide a different number format, if you like, by using the Number tab of the Format Axis dialog box. Changing the number format for a category axis that displays text will have no effect.

Figure 4-34. Three line charts with no axes.

General " mph"

This number format causes the text mph to be appended to each value.

Figure 4-35. The value axis uses a custom number format to provide units for the values.

To add or remove gridlines, use the Chart Tools

A more direct way to remove a set of gridlines is to select the gridlines and press Delete.

To modify the properties of a set of gridlines, select one gridline in the set (which selects all in the set) and access the Format Gridlines dialog box. Or, use the controls in the Chart Tools

Figure 4-36 shows a scatter chart with major and minor gridlines displayed for both axes. The major gridlines are thicker and darker than the minor gridlines.

Figure 4-36. This scatter chart displays gridlines for both axes.

Some of the layouts in the Chart Tools

Note

If a chart series is selected, the Chart Tools

To remove data labels from a particular series, select the data labels and press Delete. To remove a single data label, select the individual label and press Delete. To remove the data labels from all series in a chart, select an element other than data labels and choose Chart Tools

After adding data labels to a series, you can apply formatting to the labels by using the Format Data Labels dialog box. Just select the data labels for a series (or, select an individual data label) and press Ctrl+1. You specify the contents of the data labels by using the Label Options tab of the Format Data Labels dialog box. Figure 4-37 shows this dialog box for a pie chart.

The types of information that can be displayed in data labels are as follows:

Figure 4-37. Options for displaying data labels.

The Format Data Labels dialog box also lets you specify a variety of other formatting options.

The column chart in Figure 4-38 contains data labels that display category names and their values. These labels are positioned to appear on the Outside End. I specified the New Line separator option, so the value appears on a separate line. By including the category name in the data labels, I was able to delete the redundant text in the horizontal category axis.

Figure 4-38. Data labels in a column chart.

Figure 4-39 shows a line chart in which the data labels substitute for the series markers. In this chart, the markers were set to None, and the data labels were positioned using the Center option. I formatted the data label fill and added a shadow. I also deleted the category axis (which originally showed month names), because that information is available in the data labels.

Figure 4-39. Using data labels in place of series markers.

To override a particular data label with other text, select the label and enter the new text. To select an individual data label, click once to select all the data labels; then click the specific data label to select it.

To link a selected data label to a cell, follow these steps:

After adding data labels, you'll often find that the data labels aren't positioned optimally. For example, one or more of the labels may be obscured by another data point or a gridline. If you select an individual label, you can drag the label to a better location.

A data label trick

One way to get around Excel's data label limitation is to use a custom number format for each of your x values. The trick is to use a number format that consists only of text. The cell will still be treated as a number, but only the text will be displayed. Also, the formatted value is what appears in the chart data label!

The accompanying figure shows a chart that uses the data in columns C and D. Column C contains actual values, but the cells are formatted to display as text. Each value uses a different custom number format. For example, the value in cell C27 uses this custom number format: "Abigail." The x values are duplicated in column B but formatted as numbers. The data labels in the chart use the X Value option, and the custom formats (not the actual values) are displayed. These custom labels also display along the x axis, so you'll need to change the number formatting for the axis (as I did in the figure).

As you work with data labels, you will probably discover that Excel's Data Labels feature leaves a bit to be desired. For example, it would be nice to be able to specify a range of text to be used for the data labels. This would be particularly useful in scatter charts in which you want to identify each data point with a particular text item. Figure 4-40 shows a scatter chart. If you would like to apply data labels to identify the student for each data point, you're out of luck.

Despite what must amount to thousands of requests, Microsoft still has not added this feature to Excel! You need to add data labels and then manually edit each label.

Figure 4-40. Excel provides no direct way to add descriptive data labels to the data points.

Figure 4-41. The Chart Data Labeler utility (Part of the author's Power Utility Pak) overcomes a limitation in Excel.

As you work with data labels, you'll find that this feature works best for series that contain a relatively small number of data points. The chart in Figure 4-42, for example, contains 24 data points. You can't display all the data labels on this chart and keep the chart legible.

One option is to delete some of the individual data labels. For example, you might want to delete all the data labels except those at the high and low points of the series. Deleting only certain data labels is, however, a manual process. To delete an individual data label, select it and press Delete. Using gridlines provides another way to let the reader discern the values for the data points. Yet another alternative is to use a data table, which is described in the next section.

Figure 4-42. Data labels don't work well for this chart.

To add a data table to a chart, select the chart and choose Chart Tools

To remove a data table from a chart, select the chart and choose Chart Tools

One problem with data tables, as I mentioned previously, is that this feature is available for only a few chart types. Formatting options for a data table are relatively limited. Data table formatting changes are made in the Format Data Table dialog box.

The Fill tab is a bit misleading because it does not actually allow you to change the fill color for the data table. Rather, you are limited to formatting the background of the text and numbers in the data table.

Unfortunately, you cannot apply different font formatting to individual cells or rows within the data table. You also can't change the number formatting. The numbers displayed in a data table always use the same number formatting as the source data.

When you add a data table to a chart, the data table essentially replaces the axis labels on the horizontal axis. The first row of the data table contains these labels, so losing them isn't a major problem. However, you will not be able to apply separate formatting to the axis labels — they will have the same formatting as the other parts of the data table.

Another potential problem with data tables occurs when they are used with embedded charts. If you resize the chart to make it smaller, the data table may not show all the data.

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 provides you with much more flexibility in terms of formatting.

When you start flirting with the third dimension, you have a great deal of flexibility regarding the viewpoint for your charts. Figure 4-45 shows a 3-D column chart that has been rotated to show four different views. It should be clear that Excel allows you to rotate a 3-D chart in such a way that it becomes useless.

To rotate a 3-D chart, select the chart and choose Chart Tools

Figure 4-45. Four different views of the same chart.

A surface chart is different from the other chart types because you cannot select any of the series in the chart. Another difference is that the colors in the chart are based on the values.

The number of colored bands used in the chart depends on the major unit setting for the value axis. Figure 4-46 shows two surface charts. In the chart on the left, the value axis major unit is 0.05 (the default). In the chart on the right, the value axis major unit is 0.4, which covers the entire scale for the chart. Consequently, this chart displays a single color.

Figure 4-46. Changing the major unit for the value axis controls the number of colors used on a surface chart.

The procedure to adjust the colors used on a surface chart is rather counterintuitive. After you create the surface chart, follow these steps:

To change the view point of a surface chart, choose Chart Tools