17.1. Formatting Chart Elements
Every chart is actually built out of small components , like titles, gridlines, axes, a legend, and the bars, points, or exotic shapes that actually represent the data. And Excel lets you manipulate each of these details separately. That means you can independently change the format of a label, the outline of bar, the number of gridlines, and the font and color of just about everything.
The first step in manipulating your charts components is learning how to select them. The easiest way is to click directly on the element you want to change. Black boxes (known as handles ) appear on the edges of whatever you've selected.
Some elements, like titles and legends, are easy to select this way. Others, like gridlines and axes, are a little trickier because you need to click in exactly the right place. If you end up selecting the wrong element, it's sometimes easiest to just use the arrow keys to select another one. Each time you press an arrow key, Excel selects the next chart element. So if you keep pressing an arrow key, eventually you'll cycle through all the elements that you can select in the current chart. (You can also use the Chart toolbar, which is covered later in this chapter.)
The chart elements you can select are shown in Figure 17-1. They include:
Title . The title labels the whole chart. In addition, you can add titles to the other axes. If you do, you can select these titles separately.
Legend . The legend identifies each data series on the chart with a different color. A legend is useful only when the chart contains more than one series.
Axis . An axis runs along each edge of the chart and determines the scale. In a typical two-dimensional chart, you have two axes: the category axis (typically on the bottom of the chart, running horizontally), and the value axis (typically on the left, running vertically).
Plot Area . The plot area is the background of the chart. In a standard chart, this is a plain gray background, which you can customize.
Gridlines . The gridlines are the lines that run across the plot area. Visually, you line the data up with the gridlines to get an idea of the value of each data point. Every chart starts out with horizontal gridlines, but you can remove them or add vertical gridlines. You can tell Excel how many gridlines to draw, and even how to format them.
Data Series . The data series is a single set of data plotted on the chart across the category axis. In a line chart, for example, the data series is a single line. If a chart has multiple series, it's often useful to format them separately to make them easier to differentiate or to emphasize which one is the most important.
Data Point . A data point is a single value in a data series. For example, in a line chart a data point is a single dot, and in a column chart a data point is a single column. If there's an exceptionally important value that you want to call attention to, you can format a data point so it looks different than the rest of the series.
|Excel Oddity AutoFudge|
In one of its most bizarre features, Excel actually lets you change the data in your worksheet by dragging points and lines on your chart. You can try this out with a column chart, if you're nimble enough with the mouse. First, select a single column. You may need to click it twice to ensure that the individual column is selected, and not the entire series (as shown in Figure 17-6). Next, using the resizing handle at the top of the column, stretch it upward. Once you make your change, Excel modifies the corresponding data in your worksheet so it matches the adjusted column.
For example, if you adjust a sales column upward, Excel calculates the new dollar amount and inserts it into the source table. The same trick works with the slices in a pie chart and even points in a line chart or XY scatter chart.
Technically, this feature is known as graphical goal seeking . But it seems more likely that Microsoft is dedicated to giving business people all the tools they needeven if these tools include a convenient way to tweak the numbers so a chart looks better!
So what do you do with a chart element once you've selected it? There are three basic tasks you can perform, although not every chart element supports every task. The tasks include:
Deleting an element . To remove a selected element from your chart, just press Delete. You can delete any chart element, including titles, legends, data series, gridlines, the background, and even an axis. It's possible to restore these elements after you've deleted them, but only by using the Edit Undo command immediately.
Moving an element . You can move a chart element by dragging it, just like the way you move the whole embedded chart. Some elements are easily moved, like labels, legends, and the whole chart. Other chart elementsincluding axes, gridlines, and data pointscan't go anywhere. A few chart types support additional manipulation. For example, three-dimensional charts provide an extra sizing handle that lets you rotate a chart in three dimensions, tilting it forward or backward to offer a better view.
Resizing an element . You can resize an element by clicking on one of the resizing handles (the black boxes that appear on the edges of the element when you select it). Resizing is mainly useful with legends and the overall chartthere aren't many other chart elements you can resize.
Often, you won't select a chart element to delete or move it, but instead to format it with a different border, font, or color. In this case, simply right-click the element and choose the format option from the pop-up menu. However, different chart elements are formatted in different ways; they're all explained separately in the following sections.
The Chart toolbar provides one-stop -shopping for tweaking and refining any chart. Although you can show the Chart toolbar at any time by selecting View Toolbars Chart, it automatically springs into view when you select a chart or an element inside a chart.
The most useful part of the Chart toolbar is the drop-down list on its left side. This list allows you to select any chart element (except for a single data point) in the chart. When you choose the option from the list, Excel automatically highlights the corresponding part of the chart. Figure 17-2 dissects the Chart toolbar.
Now you're ready to start creating spiffy-looking, polished charts. A good starting point is the background color. Initially, this color is a dull gray, but it's easy enough to change. Just follow these steps:
Select the plot area.
The easiest way to do this is to click the gray space between gridlines, or just choose Plot Area from the list in the Chart toolbar.
Right-click the plot area and choose Format Plot Area, or click the Format button on the Chart toolbar.
A dialog box appears containing a single Patterns tab (Figure 17-3). Here you can set the background color (on the right) and add a border around it (on the left). The border can sport a variety of colors, line thicknesses, and line styles (like dashed, dotted , double, and so on). The fill area can use any ordinary color. Or, if you feel adventurous, click Fill Effects to open another dialog box where you can set a background using a gradient that shades between two colors, a regular pattern (like spots or hatches), special textured tiles, or even a background picture. (Fill Effects are described in more detail in the next section.)
When you've finished making your changes, click OK.
Excel applies the results of your artistry.
Coloring the background of a chart is nice, if a little quaint. In the 21st century, charting mavens are more likely to add richer details like textured backgrounds or gradient fills. Excel provides these options and more. And although patterned and textured fills don't always make sense, they can often add pizzazz when used in the background of a simple chart. You can apply fancy fills to the chart background (the plot area) or individual chart items, like the columns in a column chart.
To use a fancy fill, simply click the Fill Effects button in the Patterns tab (Figure 17-3). (The dialog boxes for formatting most chart elements have a Patterns tab.) The Fill Effects dialog box in turn has four separate tabs. You choose the tab that represents the type of fill you want to create:
Gradient . A gradient is a blend between two colors. For example, you might use a black and white gradient that gradually fades from black in the top-left corner to white in the bottom-right corner. When you create your own gradient, you choose the colors and the shading pattern. You can even choose from a list of color and shading presets, with picturesque names like Late Sunset and Ocean.
Texture . A texture is a detailed pattern that's tiled over the whole chart element. The difference between a texture and an ordinary pattern is that patterns are typically simple combinations of lines and shading, while a texture uses an image that has greater, more photographic detail. When you choose a texture, you have choices like Woven Mat, Canvas, and Blue Tissue Paper. When you apply the texture, it actually looks like its name , which is great for presentations, although potentially problematic for all but the most capable printers.
Pattern . A pattern is a simple way of combining two colors using hatches, squares, and other simple line patterns. When you use a pattern, Excel works with the background and foreground colors you've already chosen . Patterns usually aren't so attractive, but they're often easier to print on ordinary printers than textures.
Picture . A picture is a graphical image that goes behind your chart and stretches itself to fit. Excel doesn't provide any ready-made pictures for you to use. Instead, you'll need to browse to a graphics file on your computer (a .bmp, .jpg, or .gif file). This option works well if you need a themed chartfor example, a beach scene behind a chart about holiday travel choices. If you just want to add a company logo somewhere on your chart, you're better off using the drawing tools described in the next chapter to place the logo exactly where you want it.
Figure 17-4 demonstrates some of your fill choices.
It doesn't matter how spectacular your chart looks if it's hard to figure out what the data represents. To clearly explain what's going on, you need to make sure you have the right titles and labels.
Here's how you add basic titles:
Right-click anywhere on your chart and select Chart Options.
The Chart Options dialog box appears.
Choose Titles, which is the first tab. Enter the appropriate titles in the text boxes labeled "Chart title," "Category (X) axis," and so on.
As you switch from one text box to another, the preview on the right side of the tab updates to include the titles you've added.
The titles appear in the chart.
Right-click one of the titles that you want to format. Choose Format Chart Title or Format Axis title (depending on which element you selected) from the pop-up menu.
It's easy to format your new titles. The only drawback is that you need to format them one at a time.
The formatting dialog box provides three tabs: Patterns, Font, and Alignment. The Patterns tab provides options for adding a fill color and a border to the title background. The Font tab is where to change the typeface, color, and size , and apply bold and italic formatting. The Alignment tab lets you configure how the text is positioned in the title box (the standard option is centered). None of these options are newyou've seen the Patterns tab when setting the chart background and the Font and Alignment tabs when formatting cells , all of which were covered in Chapter 4.
Once you've made your changes, click OK.
You can now return to step 4 to format additional chart titles.
Titles help explain a chart's overall purpose. Usually, titles indicate what a chart is comparing or analyzing. For example, you might add a chart title like "Patio Furniture Sales" and the axis labels "Gross Revenue" and "Month of Sale" to a chart that shows how patio furniture sales pick up in the summertime. However, the category labels don't help you single out important data. They also don't let you point out multiple series (for example, the sales results in two different stores). The solution is to add additional labels or a legend . A legend is a separate box off to the side of the chart that contains one entry for each data series in a chart. The legend indicates the series name, and it adds a little sample of the line style or fill style that's used to draw that series on the chart.
Excel automatically adds a legend to most charts. If you don't already have a legend, just right-click your chart, select Chart Options, and choose the Legend tab. Turn on the "Show legend" checkbox to insert a floating legend box, which you can resize, format, or drag around your chart like most other chart elements.
Unfortunately, legends aren't always an asset when you need to build slick, streamlined charts. They introduce two main problems:
Legends are distracting. In order to identify a series, the person looking at the chart needs to glance away from the chart to the legend, and turn back to the chart again.
Legends are confusing. Even if you have only a few data series, the average reader may find it hard to figure out which series corresponds with each entry in the legend. This problem becomes more serious if you print your chart out on a printer that doesn't have the same range of colors as your computer monitor, in which case different colored lines or blocks may begin to resemble each other.
The best way to overcome these problems is to add data labels, as described in the next section.
Data labels are labels that you attach to every data point in a series. This text floats just above the point, column, or pie slice that it describes, clearly identifying each piece of information. Data labels have unrivalled explaining powerthey can identify everything . The only possible drawback is that adding data labels to a chart that's already dense with data may lead to an overcrowded jumble of information.
To apply data labels, right-click your chart and select Chart Options. Then, choose the Data Labels tab, which provides several options for labeling your data. You can apply one piece of information or a combination that includes multiple items. If you use multiple items, you can also choose a character from the Separator list box to specify how to separate each piece of text in the full label (for example, with a comma, space, semicolon, or new line). If you look carefully , you can see the effect of your choices in the Preview window on the left side of the dialog box. After you've made your selection, click OK to add the labels to the chart.
The text you can use in your labels depends on the type of chart you've created. Here are your choices:
Series name . The series name identifies the series each data point comes from. Because most series have multiple data points, using this option means the same text repeats again and again. For example, in a line chart that compares sales between two stores, using this option would put the label "Store 1" above each data point for the first store, which is probably overkill.
Category name . The category name adds the information from the category axis. For example, if you're using a line chart to compare how sales fluctuated month by month, this option adds the month above every data point. Assuming you have more than one line in your line chart, this creates duplicate labels, which crowds out the important information in your form. For that reason, category labels don't work very well with most charts, although you can use them to replace the legend in a pie or donut chart.
Value . Value labels insert the data that corresponds with a data point. This is the actual information in the corresponding cell in your worksheet. For example, if you're plotting changing sales, this is the dollar amount of sales for a given month. Value labels are probably the most frequently used type of label.
Percentage . Percentage labels apply only to pie charts and donut charts. They're similar to value labels, except they divide the value against the total of all values to find a percentage.
Bubble size . Bubble size labels apply only to bubble charts. They add the value from the cell that was used to calculate the bubble size next to each bubble. Bubble labels are quite useful in bubble charts because bubble sizes don't correspond to any axis, so you can't understand exactly what numeric value a bubble represents just by looking at the chart. Instead, you can judge relative values only by comparing the size of one bubble to another.
Figure 17-5 shows a few examples of useful data labels at work.
Once you add your data labels, they become distinct chart elements. That means you can select them and change their font, background, and border in exactly the same way as you modify any other chart element, like titles. To do so, just right-click one and choose Format Data Labels. This trick is extremely handy because it's often important to shrink a label's font so it fits into a chart properly, or apply bold formatting so it really stands out. You can even edit the text in each labeljust click once to select the label, and click again to put it in edit mode.
In simple charts, data series labels work well. But in more complex charts, data series labels can be more trouble than they're worth because they lead to chart overcrowding, particularly with line charts or any chart that has multiple series. The solution is to add labels to only a few data points in a seriesthose that are most important.
To add an individual data label, follow these steps:
Click the precise data point that you want to identify. For example, this could be a slice in a pie chart, a column in a column chart, or a point in a line chart.
Selecting a data point is a little tricky. You need to click twicethe first click selects the whole series, and the second click selects just the data point you want. You'll see the handles appear around the specific column or point to indicate you've selected it, as shown in Figure 17-6.
When you have the right data point selected, right-click it and choose Format Data Point.
The Format Data Point dialog box appears.
If you can't find the Format Data Point options, and the menu only displays the Format Data Series option, you probably have inadvertently selected the whole series. Return to step 1 to fix the problem.
Click the Data Labels tab.
The Format Data Point dialog box includes other options for formatting a data point. You can use these options too, as detailed in the next section.
Select the label information you want to use.
The Data Labels tab provides the same options for a data point as it does for an entire data series, including value, category, series, and percentage information.
Click OK to add the label to your data point.
As with data series labels, you can format your data label by right-clicking it and choosing Format Data Labels. It's almost always a good idea to format your labels so they stand out and don't crowd other information on the chart. Figure 17-7 shows two charts with data points labeled.
Adding labels is one way to distinguish important points on your chart. Another technique is to use color, borders, and patterns. These techniques can't provide any additional information (like the value of the data point), but they're a great way to emphasize important information without cluttering up your chart. (Figure 17-8 shows a few examples.)
There are several reasons for formatting a data series or data point:
You want to draw attention to specific data.
You want to make sure that you distinguish between different series.
You want to make sure your printout is legible, and that you can identify all the important information, even if your printout is black and white.
If you've added data labels, you already know the basic steps to take to format a data series because they're almost identical. It works like this:
Select the data series or data point that you want to format.
If you select a data series, your changes affect all the data points in that series. Usually, you'll use this approach to help distinguish between different series.
If you select a single data point, you'll affect only that one data point. Usually, you'll use this approach to highlight important values.
Right-click the data series or data point, and choose either Format Data Series or Format Data Point.
The formatting dialog box appears.
Select the Patterns tab.
The options you see in the Patterns tab depend on the type of chart you're working with. For a column, bar, or pie chart, you'll see the familiar Border and Area sections, which were first introduced in Section 17.1.3 when you learned how to format a chart's background. You can use these settings to add outlines, fill colors, or even fancy fill effects like the textures and gradients explained earlier.
If you're using a line or scatter chart, you'll see a Line and Marker section. The Line section allows you to set the color, line style (dashed, dotted, solid, and so on), and weight (or thickness ) of the line. The Marker section lets you set the style (square, triangle, cross, and so on) and size of each data point that's placed on the line.
Once you've made your changes, click OK to apply them to the data series or data point.
If you change a whole data series, Excel also updates any legend to make sure it shows the new style.
Many people don't think twice about the scale they use when they create a chartinstead, they let Excel set it automatically based on the values their chart has been built from. There's nothing wrong with this laissez-faire approach, but if you know how to take control of your chart's scale, you can make important data stand out and make it easier for people looking at your chart to spot relative differences in data and understand overall trends.
Usually, you'll be most interested in changing the scale of the value axis that runs on the left side of most charts. You can modify the scale of the value axis on most charts, including column charts, line charts, scatter charts, and area charts. (In a bar chart, the value axis actually runs horizontally along the bottom of the chart, although you can modify the scale in the same way as you do with these other chart types.) Pie and donut charts don't show a value scale at all.
To change the scale, select the value axis, either by clicking it or selecting Value Axis from the Chart toolbar. Right-click the axis and choose Format Axis. Choose the Scale tab (shown in Figure 17-9), make your changes, and click OK to apply them.
Several settings determine the scale of your chart. These include:
Minimum and Maximum . These values set the range of your scale. The axis starts at the minimum value (at the bottom of your chart), and ends at the maximum (at the top). Usually, Excel sets these values so that the minimum is 0 and the maximum is just a little bit above your largest data point. However, if your data points are very large and only have minor differences, you'll be able to help your chart's viewers make comparisons more easily if you compress the scale, by having it start at a higher minimum value.
Major unit . The major unit setting determines how many units the scale is divided into. For example, if you have a scale from 0 to 1,000 and a major unit of 100, gridlines and axis labels appear every 100 units. Altogether, that makes for 11 labels.
Minor unit . The minor unit setting determines how many tick marks are on the scale. (Tick marks are tiny lines added to the axis to help you judge the scale. Unlike gridlines, they don't cross the whole chart.) Usually the minor unit is less than the major unit. For example, you might use a major unit of 100 and a minor unit of 10 in a chart that stretches from 0 to 1,000 (which generates 11 gridlines and 10 tick marks between each gridline). Five to 10 major units on each axis, and 5 to 10 minor units for every major unit, usually make for a pleasant looking chart.
Category (X) axis crosses at . This number controls where the category axis line crosses the value axis. Usually, this line is placed right at the bottom of the chart, at the point. However, you can accomplish an interesting effect by placing it somewhere in the middle of your chart. For example, you might want to plot a chart of test scores, and want to draw the axis at a point that would indicate the minimum passing mark. Note that in a column chart, when a column has a value that's less than the axis, it points "downward," as you can see in Figure 17-10 (bottom).
Using these five basic ingredients , you have a good deal of flexibility. Figure 17-10 compares a few different options that demonstrate how different scale choices can transform a chart, with the help of a little formatting.
The Format Axis dialog box also provides a few specialized options, which aren't as commonly used but are still quite interesting. They include:
Display units . You can use this option to shorten the text labels on your axis. This is particularly useful if you have large dollar amounts. For example, imagine your value axis stretches from $100,000 to $300,000. If you choose Thousands as your display unit, Excel adds the label Thousands along the value axis and changes the scale values to three digit numbers like $100 and $300. The bottom example in Figure 17-10 shows this space-saving trick at work.
Logarithmic scale . A logarithmic scale is one that doesn't increase gradually. Instead, every major unit represents an increase by a power of 10. For example, the values 0, 10, 20, 30, 40, 50 make up a typical scale. The values 0, 10, 100, 1000, 10000 make a logarithmic scale. Logarithmic scales lend themselves to scientific and mathematical applications to help you see certain types of relationships and patterns in your data.
Values in reverse order . This option turns your chart upside down. It places the category axis at the top of the chart (instead of the bottom), and changes the value scale so that it increases as it stretches down the side of the chart.
Category (X) axis crosses at maximum value . This option places the category axis at the top of the chart, somewhat like the "Values in reverse" order option. The difference is that the scale remains the same, so that the minimum value is at the bottom of the chart and the maximum value is at the top.
| GEM IN THE ROUGH |
Even More Formatting
Once you get the hang of formatting, you'll really appreciate all the ways that Excel lets you tweak even the smallest details on your charts. For example, you can format axes and gridlines the same way that you formatted titles, labels, and the chart background.
Here are some details you might want to change: