In This Chapter
Getting an overview of chart formatting
Formatting fill and borders
Formatting chart background elements
Working with chart titles
Working with legends, data labels, gridlines, and data tables
Understanding chart axes
Formatting 3-D charts
Excel usually does an adequate job of transforming a range of numbers into a chart. Often, the basic chart that Excel creates is good enough. If not, many users are content to apply a different prebuilt layout (using Chart Tools
If you create a chart for your own use, spending a lot of time on formatting and customizing the chart may not be worth the effort. But if you want to create the most effective chart possible, or if you need to create a chart for presentation purposes, you will want to take advantage of the additional customization techniques available in Excel.
This chapter discusses the ins and outs of formatting and customizing your charts. It's easy to become overwhelmed with all the chart customization options. However, the more you work with charts, the easier it becomes. Even advanced users tend to experiment a great deal with chart customization, and they rely heavily on trial and error — a technique that I strongly recommend.
Customizing a chart involves changing the appearance of its elements, as well as possibly adding new elements to it or removing elements from it. These changes can be purely cosmetic (such as changing colors or modifying line widths) or quite substantial (such as changing the axis scales or rotating a 3-D chart).
Before you can customize a chart, you must activate it:
To activate an embedded chart, click anywhere within the chart.
To deactivate an embedded chart, just click anywhere in the worksheet (or, press Esc once or twice).
To activate a chart on a chart sheet, click its sheet tab.
If you press Ctrl while you activate an embedded chart, the chart is selected as an object. In fact, you can select multiple charts using this technique. When a group of charts is selected, you can move and resize them all at once. In addition, the tools in the Drawing Tools
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:
Use the mouse
Use the keyboard
Use the Chart Elements control
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.
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.
If you find that some chart elements are difficult to select with the mouse, you're not alone. If you rely on the mouse for selecting a chart element, it may take several clicks before the desired element is actually selected. And in some cases, selecting a particular element with the mouse is impossible. Unfortunately, this problem has gotten worse in Excel 2007. Fortunately, Excel provides other ways to select a chart element, and it's worth your while to be familiar with them.
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.
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.
When a single data point is selected, the Chart Elements control will display the name of the selected element, even though it's not actually available for selection in the dropdown list.
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.
Table 4-1. CHART ELEMENTS
When a chart element is selected, you have some choices as to which user interface method you can use to format the element:
The Ribbon
The Mini toolbar
The Format dialog box
Formatting by Using the Robbon
The controls in the Chart Tools
For a bit more control, follow these steps:
Click the series to select it.
Choose Chart Tools
Choose Chart Tools
Choose Chart Tools
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:
Click the chart title to select it.
Choose Chart Tools
Choose Chart Tools
Notice that some of the controls in the Home
Click the chart title to select it.
Choose Insert
Choose Insert
It's important to understand that the Ribbon commands do not contain all possible formatting options for chart elements. In fact, the Ribbon controls contain only a small subset of the chart formatting commands. For optimal control, you need to use the Format dialog box (discussed later in this chapter).
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.
A few of the common keystroke combinations also work when a chart element that contains text is selected — specifically: Ctrl+B (bold), Ctrl+I (italic), and Ctrl+U (underline).
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.
You can access the Format dialog box by using either of the following methods:
Select the chart element and press Ctrl+1.
Right-click the chart element and choose Format xxxx from the shortcut menu (where xxxx is the chart element's name).
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.
The Format dialog box is a stay-on-top dialog box. In other words, you can keep this dialog box open while you're working on a chart. It's not necessary to close the dialog box to see the changes on the chart. In some cases, however, you need to activate a different control in the dialog box to see the changes you've specified. Usually, pressing Tab will move to the next control in the dialog box and force Excel to update the chart.
Many of the Format dialog boxes for chart elements include a tab named Fill as well as other tabs that deal with border formatting. These tabs are used to change the interior and border of the selected element.
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.
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:
No Fill: Makes the chart element transparent.
Solid Fill: Displays a color selector so that you can choose a single color. You can also set the transparency for the color.
Gradient Fill: Displays several additional controls that allow you to select a prebuilt gradient or construct your own gradient. A gradient consists of from two to ten colors that are blended together in various ways. You have literally millions of possibilities. See the nearby "Specifying gradients" sidebar for more information.
Picture or Texture Fill: Enables you to select from 24 built-in textures, choose an image file, or use clip art for the fill. You can control how the picture is displayed: stretched, stacked, or stacked and scaled. This feature can often be useful in applying special effects to data series. See the section "Formatting Chart Series," later in this chapter.
Automatic: Sets the fill to the default color.
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.
A border is the line around an object. Excel offers four general choices for formatting a border:
No Line: The chart element has no line.
Solid Line: The chart element has a solid line. You can specify the color, the transparency, and a variety of other settings.
Gradient Line: The chart element has a line that consists of a color gradient.
Automatic: The default setting. Excel decides the border settings automatically.
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.
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.
Every chart has two key components that play a role in the chart's overall appearance:
The default colors of the chart area and the plot area depend on which chart style you choose from the Chart Tools
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).
If you delete the chart area, you delete the entire chart. If the chart is on a chart sheet, you can't delete the chart area — which is actually the basis for a useful trick that enables you to place multiple charts on a chart sheet. See Chapter 8 for an example.
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.
If you set the Fill option to No Fill, the plot area will be transparent. Therefore, the color and patterns applied to the chart area will show through. You can also set the plot area to a solid color and adjust the Transparency setting so that the chart area shows through partially.
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.
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.
Making a few simple formatting changes to a chart series can make a huge difference in the readability of your chart. When you create a chart, Excel uses its default colors and marker styles for the series. In many cases, you'll want to modify these colors or marker styles for clarity (basic formatting). In other cases, you may want to make some drastic changes for impact.
You can apply formatting to the entire series or to a single data point within the series — for example, make one column a different color to draw attention to it.
The examples in this section are available on the companion CD-ROM. The file name is series.xlsx
.
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-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.
Data series elements are not the only type of chart element that can have a picture or graphic displayed. You can also apply a picture to the chart area, the plot area, the legend, and even text elements. For 3-D charts, you can apply a picture to the walls or floor. Refer to Chapter 6 for more information about combining graphics with charts.
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.
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-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%.
A chart can have as many as five different titles:
Chart title
Category axis title
Value axis title
Secondary category axis title
Secondary value axis title
The number of titles depends on the chart type. For example, a pie chart supports only a chart title because it has no axes. Figure 4-15 shows a chart that contains four titles: the chart title, the horizontal category axis title, the vertical value axis title, and the secondary vertical axis title.
The examples in this section are available on the companion CD-ROM. The file name is titles.xlsx
.
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
Contrary to what you might expect, you cannot resize a chart title. When you select a title, it displays the characteristic border and handles — but the handles cannot be dragged to change the size of the object. The only way to change the size is to change the size of the font used in the title. For more control over a chart's title, you can use a text box instead of an official title.
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.
For lengthy titles, Excel handles the line breaks automatically. To force a line break in the title, press Enter. To add a line break within existing title text, press Ctrl+Shift+Enter.
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
You can easily modify the formatting for individual characters within a title. Select the title, highlight the characters that you want to modify, and apply the formatting. The formatting changes you make will affect only the selected characters. Figure 4-16 shows an example of a two-line chart title that uses different sizes and styles of text.
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:
Select the chart element that will contain the cell link. Make sure that the text element itself is selected (don't select text within the element).
Click the formula bar.
Type an equal sign (=).
Click the cell that will be linked to the chart element.
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.
A chart legend identifies the series in the chart and consists of text and keys. A key is a small graphic image that corresponds to the appearance of the corresponding chart series. The order of the items within a legend varies, depending on the chart type.
If you've added a trendline to your chart, the trendline also appears in the legend. For more information about trendlines, refer to Chapter 5.
Legends are appropriate for charts that have at least two series. But even then, all charts do not require a legend. You may prefer to identify relevant data using other methods, such as a data label, a text box, or a shape with text. Figure 4-18 shows a chart in which the data series are identified by using text in shapes, which were added to the chart using Insert
Refer to Chapter 6 for more information about using shapes with charts.
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.
You can't use the Chart Elements drop-down list to select a legend entry. You must either click the item or select the legend itself, and then press the right-arrow key until the desired element is selected.
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.
As you know, charts vary in the number of axes that they use. Pie and doughnut charts have no axes. All 2-D charts have at least two axes, and they can have three (if you use a secondary value or category axis) or four (if you use a secondary category axis and a secondary value axis). Three-dimensional charts have three axes — the "depth" axis is known as the series axis.
Refer to Chapter 8 for a variety of chart examples that use additional axes.
Excel provides you with a great deal of control over the look of chart axes. To modify any aspect of an axis, access its Format Axis dialog box. The dialog box varies, depending on which type of axis is selected.
The examples in the section are available on the companion CD-ROM. The file name is axes.xlsx
.
All aspects of axis formatting are covered in the sections that follow.
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.
For more information about time-based axes, refer to the section "Using Time-Scale Axes," later in this chapter.
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.
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).
A category axis does not have a scale because it simply displays arbitrary category names. For a category axis, the Axis Options tab of the Format Axis dialog box displays a number of other options that determine the appearance and layout of the axis.
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.
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-24. These charts use different scales on the value axis, making a comparison between the two difficult.
A log scale works only for positive values. A new feature in Excel 2007 enables you to specify a base value between 2 and 1, 000.
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."
Another way to change the number display is to use a custom number format for the axis values. For example, to display the values in millions, click the Number tab of the Format Axis dialog box, select the Custom category, and then enter this format code:
#,##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:
None: No tick marks
Inside: Tick marks on the inside of the axis only
Outside: Tick marks on the outside of the axis only
Cross: Tick marks on both sides of the axis
You can also control the position of the tick mark labels. The options are as follows:
None: No labels.
Low: For a horizontal axis, labels appear at the bottom of the plot area; for a vertical axis, labels appear to the left of the plot area.
High: For a horizontal axis, labels appear at the top of the plot area; for a vertical axis, labels appear to the right of the plot area.
Next to axis: Labels appear next to the axis (the default setting).
Major tick marks are the axis tick marks that normally have labels next to them. Minor tick marks are between the major tick marks.
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.
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.
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.
A time-scale axis option is available only for the category axis (not the value 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.
For this chart, I entered the following scale values:
Minimum axis scale value: .25 (6:00 am)
Maximum axis scale value: .58333 (2:00 pm)
Major unit: .041666 (1:00:00)
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.
This type of data layout is common when you work with PivotTables, and PivotCharts often use multiline category axes. Refer to Chapter 9 for more information about PivotTables and PivotCharts.
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.
Don't forget about custom number formats. Figure 4-35 shows a chart that uses the following custom number format for the value axis:
General " mph"
This number format causes the text mph to be appended to each value.
Gridlines can help the viewer determine the values represented by the series on the chart. Gridlines are optional, and you have quite a bit of control over the appearance of gridlines. Gridlines simply extend the tick marks on the axes. The tick marks are determined by the major unit and minor unit specified for the axis.
Gridlines are applicable to all chart types except pie charts and doughnut charts.
Some charts look better with gridlines; others appear more cluttered. It's up to you to decide whether gridlines can enhance your chart. Sometimes, horizontal gridlines alone are enough, although scatter charts often benefit from both horizontal and vertical gridlines. Inmany cases, gridlines will be less overpowering if you make them dashed lines, with a gray color.
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.
If a chart uses a secondary axis, you can specify either or both secondary axes to display gridlines. As you might expect, displaying two sets of gridlines in the same direction can be confusing and result in additional clutter.
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.
You can't apply different formatting to individual gridlines within a set of gridlines. All gridlines in a set are always formatted identically.
For some charts, you may wish to identify the individual data points in a series by displaying data labels.
Some of the layouts in the Chart Tools
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
The examples in this section are available on the companion CD-ROM. The file name is data labels.xlsx
.
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.
Excel lets you either format all data labels at once or format just one data label. When you click a data label once, the labels for the entire series are selected. If you click a second time (on a single label), only that data label is selected.
The types of information that can be displayed in data labels are as follows:
The series name
The category name
The numeric value
The value as a percentage of the sum of the values in the series (for pie charts and doughnut charts only)
The bubble size (for bubble charts only)
Other options are as follows. Keep in mind that not all options are available for all chart types.
Show Leader Lines: If selected, Excel displays a line that connects the data label with the chart series data point.
Label Position: Specifies the location of the data labels, relative to each data point.
Include Legend Key in Label: If selected, each data label displays its legend key image next to it.
Separator: If you specify multiple contents for the data labels, this control enables you to specify the character that separates the elements (a comma, a semicolon, a period, a space, or a line break).
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.
The data labels display the values for each data point. For this particular chart, it would be preferable to display the value as a percentage of the total. Unfortunately, the Percent option is available only for a pie or doughnut chart. The alternative is to calculate the percentages using formulas and then plot the percentage data rather than the actual value data.
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.
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:
Click in the formula bar.
Type an equal sign (=).
Click the cell that contains the text.
Press Enter.
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.
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.
My Power Utility Pak add-in includes a utility that makes it easy to add data labels to a chart by specifying an arbitrary range of labels for a series (see Figure 4-41). The companion CD-ROM includes a trial version of this add-in.
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.
In some cases, you might want to display a data table for a chart. A data table displays the chart's data in tabular form, directly in the chart's chart area. Figure 4-43 shows a chart that includes a data table.
The examples in this section are available on the companion CD-ROM. The file name is data table.xlsx
.
Data tables can be used with only a few chart types. You cannot use a data table with scatter charts, pie charts, doughnut charts, radar charts, bubble charts, and surface charts.
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.
An exception to the behavior described in the preceding paragraph occurs with bar charts and charts with a time-scale category axis. For these types of charts, the data table is positioned below the chart and does not replace any axis labels.
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.
The only marginally useful 3D charts in Excel are surface/contour charts, and they must still be used with great care to avoid distorting the message of the data.
One of the most interesting classes of Excel charts is 3-D charts. Certain situations benefit from the use of 3-D charts, because these charts let you depict changes over two different dimensions. Even a simple column chart commands more attention if you present it as a 3-D chart. Be aware, however, that the perspective of a 3-D chart can often obscure differences among data points and make the chart more difficult to understand.
Not all charts that are labeled "3-D" are true 3-D charts. A true 3-D chart has three axes. Some of Excel's 3-D charts are simply 2-D charts with a perspective look to them.
All 3-D charts have a few additional parts that you can customize. For example, most 3-D charts have a floor and walls, and the true 3-D charts also have an additional axis (the series axis). You can select these chart elements and format them to your liking. To change the depth of a 3-D chart, use the Depth setting on the 3-D Rotation tab of the Format Plot Area dialog box. Or, you can select the floor element and use the 3-D Rotation tab of the Format Floor dialog box.
Figure 4-44 shows a 3-D column chart with three series (Apples, Oranges, and Pears). As you can see, it's virtually impossible to use the gridlines to determine an accurate value for any of the columns. For example, can you tell that the January value for Apples is 154? Even though that column appears next to the vertical axis, the numerical value for the column is not at all obvious.
The examples in this section are available on the companion CD-ROM. The file name is 3-d charts.xlsx.
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
Previous versions of Excel allow you to rotate a 3-D chart by dragging the one of the corners of the wall. This technique does not work in Excel 2007.
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:
Make sure that the chart displays a legend.
Click the legend to select it, and then click the legend entry that corresponds to the color you want to change.
Use the Fill tab in the Format Band dialog box to change the color and use the Border Color tab to change the line color.
The controls on the other tabs in the Format Band dialog box have no effect on the chart.
To change the view point of a surface chart, choose Chart Tools