Section 18.4. Formatting Chart Elements | Excel 2007[c] The Missing Manual

18.4. Formatting Chart Elements

Often, you don't select a chart element to delete or move it, but rather to format it with a different border, font, or color . In this case, simply right-click the element, and then choose the format option from the pop-up menu (or, select it, and then choose Chart Tools Format Current Selection Format Selection from the ribbon). The following sections get you started.

18.4.1. Coloring the Background

Now you're ready to start creating spiffy-looking, customized charts . The background color is a good starting point. Initially, this color is a plain white, but it's easy enough to change if you want to add a personal touch. Just follow these steps:

Select the plot area .

To select the plot area, click the empty space between gridlines, or choose Chart Tools Format Current Selection Plot Area.

Current Selection

Format Selection.

A Format Plot Area dialog box appears (Figure 18-13). Here you can set the background color (Fill), add a border around it (Border Color and Border Styles), and use advanced Shadow and three-dimensional effects (Shadow and 3-D Format). For now, focus on the Fill tab.

Figure 18-13. It's worth getting familiar with all the formatting options in the Format Plot Area dialog box, because you'll see many of the same options again when you format other chart elements, like columns , gridlines, and just about everything else.

Choose "Solid fill", and then click the paint can button to choose from a list of colors .

For the most flexible formatting, choose one of the theme colors (which appear under the heading Theme Colors). That way, if you choose a new theme, then the chart switches itself to one of the new theme colors. Don't worry about the fancy fills for nowyou'll take them on in the next section.
When you've finished making your changes, click OK .

Excel applies the results of your artistry.

Note: Remember, if you don't have a color printer, you need to think about how colors convert when you print them in black and white. In some cases, the contrast may end up being unacceptably poor, leading to charts that are difficult to read. And even if you do have a color printer, remember you can always spare your ink using the "Print in black and white" option (Section 17.2.5.2). As a general rule, the less powerful your printer, the less you should use graphically rich details like tiles, background images, and gradients unless, of course, you're planning to view your worksheet only onscreen.

The neat thing about this sequence of steps is that you can use exactly the same process to format any chart element. That means you now know enough to give a solid fill to a chart title, the gridlines, the columns in a column chart, and so on. And once you learn your way around the rest of the formatting options, you'll be able to really spiff up your chart.

Tip: If you run rampant changing a chart element and you just want to return it to the way it used to be, select it, and then choose Chart Tools Format

Current Selection

Reset to Match Style. Excel removes your custom formatting and leaves you with the standard formatting thats based on the chart style.

18.4.2. Fancy Fills

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 gives you these options and more. And although 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.

Figure 18-14 demonstrates some of your fill choices.

Figure 18-14. Depending on the type of fill you use, you can subtly change the message your chart conveys. These figures show a picture fill (top left), texture fill (top right), and gradient fill (bottom), each with the same numbers and chart type. Fancy fills are particularly useful if you want to use your charts in a presentation program like PowerPoint. (Integrating your Excel worksheets with other programs is covered in Chapter 24.)

To apply a fancy fill, start by selecting the chart element, and then choosing Chart Tools Format Current Selection Format Selection to get to the appropriate formatting window (like Format Plot Area). Then, choose the Fill section. What you do next depends on the type of fill you want.

18.4.2.1. Gradient fills

A gradient is a blend between two colors. You may use a black-and-white gradient that gradually fades from black in the top-left corner to white in the bottom-right corner. More complex gradients fade from one color to another to another, giving a 60s-era tie-dye effect. To set a gradient, in the formatting window's Fill section, choose the "Gradient fill" choice.

You can use two basic strategies to choose your gradient: the colors and the shading pattern. For the utmost simplicity, you can use a prebuilt set of color and shading options. You choose this option by clicking the "Preset colors" button, and then clicking one of the thumbnail previews that appears in the drop-down list. Each one has a picturesque name like Late Sunset or Ocean. (As you make a choice here, Excel updates your chart to show the results you'll get if you apply the new fill.) The box below explains your custom gradient options.

POWER USERS' CLINIC
Custom Gradients

If you're an unrelenting micromanager, you may want to choose your own gradient colors. But be forewarnedExcel lets you design gradient fills in truly ridiculous detail. Here's the recipe you need to follow to cook up your own custom gradient.

If you're just starting out, begin with one of the choices from the "Preset colors" list. You can then modify it.

From the Type list, choose a gradient type .

The type determines how the fill's shaded. A normal linear gradient shades colors from one side (like the top) to the other (the bottom). The more exotic radial shades colors in concentric rings, starting from a single point.
From the Direction list, choose an option .

The directions you can use depend on the type you chose in the previous step. If you're using a linear fill, you can choose whether the gradient starts at the top, bottom, left, or right. (For each option, a tiny thumbnail preview shows you what it looks like.) If you're using a linear fill, you can also set an angle in the Angle text box to tilt the gradient just a bit, so that it's not completely horizontal or vertical.
In the Gradient Stops section, click the Add and Remove buttons until you have the right number of stops .

A gradient stop is a color in a gradient. In a simple gradient (like a blue-to-white shading), you have two colors, which means you have two stops, one for each color. If you have a more complex gradient (like a tacky yellow-green-red number), you need three gradient stops, one to put in each color. Of course, your gradient stops don't need to be different colors; you could use different tints or shades of the same color.
Modify each gradient stop to suit .

Select a gradient stop from the list. When you pick one, you can adjust the color and the stop position . The stop position is a percentage that determines when the gradient switches from one color another. Say you create three gradient stops, with the colors yellow, green, and red. You may choose gradient stops of 0%, 50%, and 100% to space them out evenly. That way, the fill is yellow at the starting point (0%), green in the mid-point (50%), and red at the end (100%).
Click Close .

18.4.2.2. Texture fills

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 may have greater, more photographic detail.

To choose a texture for a fill, click "Picture or texture fill". You can then choose one of the ready-made textures from the drop-down texture list (Figure 18-15).

Figure 18-15. 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. Most textures are great for presentations, although potentially problematic for all but the most capable printers.

Further down the window, you see options that let you control exactly how Excel tiles your texture. You can size your texture to be larger or smaller by adjusting the scale percentages, and you can play with the offset settings to alter how the tiles of texture overlap. Finally, you can use the Mirror list to flip the texture around. But in truth, all these options are excessive frills, and you'll rarely need to touch any of them once you have a texture you like.

If the ready-made list of textures doesn't have what you want, you can choose your own texture from a picture you have in a separate file, which is the next section's topic.

Note: You'll notice that the Fill section has a slider bar that you can use to set the degree of transparency you want. You can make a fill partially transparent so that other elements show through. You can see a chart background through a partially transparent chart column. However, transparency is difficult to get right, and it often makes ordinary charts harder to read. But if your boss is out of the office and you need to fill the next hour , go ahead and experiment!

18.4.2.3. Picture fills

A picture is a graphical image that goes behind your chart and stretches itself to fit. Excel doesn't provide any ready-made pictures. 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 chartlike 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.

To use a picture fill, choose the "Picture or texture fill" option, and then click the File button to browse for the picture you want to use. Once you've picked the right picture, you can adjust the other options in the Fill section. Start by making sure the "Tile picture as texture" checkbox isn't selectedif it is, Excel tiles your picture just like the textures you saw in the previous section. (On the other hand, if that's the effect you're looking for, click away.)

Ordinarily, Excel stretches a picture over the surface of the chart. However, if you want your picture to fill just a part of the chart, you can adjust the different offset percentages (Top, Left, Right, and Bottom).

Tip: If you just want to add an unstretched image or two somewhere on your chart, you shouldn't use a picture fill. Instead, add a picture object, as described in the next chapter.

18.4.3. Fancy Borders and Lines

Now that you've tweaked the background fill to be slick and sophisticated (or wild and crazy), you're ready to modify other details. Along with the fill, the border is the next most commonly modified detail. You can add a border around any chart element, and your border can sport a variety of colors, line thicknesses, and line styles (like dashed, dotted , double, and so on).

To set a line from a format window (like Format Plot Area), follow these steps:

Choose the Border Color or Line Color section .

Both these sections are basically the same. The difference is that the name changes depending on the chart element you're tweaking. Some chart elements (like the plot area) can have a border, so they have a Border Color section. Other elements (like the gridlines) contain lines, so they have a Line Color section.
Choose the type of line you want. Usually, it's "Solid line" .

Obviously, "No line" removes the line altogether. Your only other option is "Gradient line", which lets you create a line that's painted with a gradient fill of colors (Section 18.4.2.1). The reason this odd feature exists may never be made entirely clear.
Choose the Border Styles or Line Style section .

Here's where you really get the chance to customize your border or line. Once again, some chart elements get a Border Styles section, while others have an almost identical Line Style section.
Set the line settings, and then click Close when you're finished .

Figure 18-16 shows your options.

Figure 18-16. Using the Border Styles section, you can set a width (the thickness of the line), choose a fancy double or triple-edged border (from the "Compound type" list), choose between solid and different types of dotted or dashed lines (from the "Dash type" list), and even set what the line corners look like (the "Join type") and what the line ends look like (the "Cap type"). The "Cap type" doesn't have an effect on borders unless you're using dashed lines, because that's the only situation where the line is broken, and so has visible ends.

Tip: Most of the time, you probably won't bother putting borders around chart elements. However, you can use the options in the Line Style section to configure the gridlines that appear behind your chart data. Just select the gridlines (you can use the Chart Tools Format

Current Selection list if youre having trouble clicking in the right spot), and then choose Chart Tools Format

Current Selection

Format Selection. Youll see the familiar Border Color and Border Styles sections that let you change the line color, thickness, and dash style.

POWER USERS' CLINIC
Using Shadows

The various formatting windows have another option. You can choose the Shadow section to apply a shadow effect to a title, data series, or some other part of a chart. (A shadow, of course, is a faint shaded region behind a graphical shape or piece of text. Usually, you use a shadow to make something look more three-dimensional, so it seems to float above the rest of the background.) And although the idea sounds a little strange , a soft shadow can make an ordinary chart seem more professional (Figure 18-17).

To apply a shadow, choose the Shadow section, and then, in the Presets list, choose one of the ready-made options. This list includes thumbnails for a wide range of different shadow effects with different degrees of fuzziness and shadow placement. If you're a hard- core artist, you can choose a different shadow color, and you tweak all the other aspects of your shadow using the sliders that control transparency, shadow size, blurriness , distance from the shadowed chart element, and so on.

Figure 18-17. Here, a shadow makes this line stand out from the chart background. Shadows works best with simple charts, like this one. If you had several different series on the same chart and gave each line its own shadow, the effect would be dizzying.

18.4.4. Formatting Data Series and Data Points

Adding labels is one way to distinguish important points on your chart. You can also 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 18-18 shows a few examples.)

Figure 18-18. These figures show two examples of formatting at work.
Top: Here's a line chart where the two lines are carefully distinguished from one another with different shaped markers and line styles. To get this effect, format each series separately.
Bottom: Here's a column chart where a few columns are emphasized with a gradient fill color. For this result, format data points individually.

You could have 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's legible, and that you can identify all the important information, even if your printout's black and white.

You already know the basic steps to format a data series because they're almost identical to the steps you use to format other parts of the chart. Start by selecting the area you want:

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 (using the two-click technique explained in Figure 18-9), you affect only that one data point. Usually, you'll use this approach to highlight important values.

Note: Your use of color and fills is limited only by your imagination , but excessive formatting can be distracting, so it's best to add extra flourishes only when they help you make a point. You could use different colors in a bar chart to help highlight the meaning of the results on a company's annual sales chart. Redcolored bars could represent losses, while black bars could show profits.

Then, use the familiar Chart Tools Format Current Selection Format Selection command. Youll see a Format Data Series or Format Data Point dialog box that looks a lot like the Format Plot Area dialog box you started out with in Section 18.4.2. Now, though, the formatting window includes a Format Series section where you can adjust some additional chart-specific details. You'll explore most of these options as you build better charts through the rest of this chapter.

Here are some formatting ideas:

Note: Use the Format Data Series or Format Data Point dialog box to implement any of these ideas.

If you're using a column, bar, or area chart, you can adjust the fill colors and even use gradients and textures to make different series stand out. Go straight to the Fill section.
If you're using a column or bar chart, you can adjust the width of the bars and the spacing in between. (In the hands of a crafty charter, this technique allows you to build an overlay chart, as described in Section 18.6.3.)
If you're using a line or XY scatter chart, you can use the Line Color and Line Style section to change what the line looks like for each series.
If you're using a line or XY scatter chart, you can use the Marker Options (Figure 18-19) and Marker Fill sections to choose the style (square, triangle, cross, and so on) and size of each data point that's placed on the line.

Tip: If you format a data point and then format the series that contains that data point, the new formatting for the series takes over. Therefore, you need to reapply your data point formatting if you want a specific value to stand out from the crowd . To save time, you can use the helpful Redo feature to apply changes over and over again. First, format a data point the way you want it. Then, select a second data point, and press Ctrl+Y to reapply your formatting to the new data point. This technique can save you loads of time.

Figure 18-19. It rarely makes sense to format an individual data point in a line or scatter chart. Instead, you'll usually format the whole series so that different lines stand out from one another. If you have more than two or three lines, you may need to get creative with different line styles and marker shapes to make sure that you really can tell them apart at a glance.

18.4.5. Reusing Your Favorite Charts with Templates

You can put a lot of work into creating the perfect chart. After you've slaved over your creation, it would be nice to have a way to reuse the formatting again in another workbook. Fortunately, Excel makes it possible through a template feature that lets you store your chart settings. Each chart template stores all the chart formatting settings you've made, but none of the data.

Here's how it works. Once you've finished polishing up your chart, complete with all the formatting choices, choose Chart Tools Design Type Save Template. Excel then prompts you to save a chart template file (which it identifies with the .crtx file extension). Choose a descriptive file name, like "Psychedelic Pie Chart," and then click Save.

By default, Excel offers to save the chart template in a Chart subfolder inside your personal template folder (Section 16.3). Don't change this folderthe Chart Template folder's the only place Excel looks for templates, so if you place it somewhere else, you can't reuse it. (Of course, nothing's stopping you from copying the chart template file, perhaps to get it into the Charts folder on someone else's computer so they can benefit from all your hard work.)

To reuse your chart template, you need to pick it from the Create Chart or Change Chart Type dialog box. To create a new chart using your template, head to the ribbon's Insert Charts section, and then click the dialog launcher (the icon of a square with an arrow in it) in the bottom-right corner. This shows the Create Chart dialog box, giving you a full selection of chart types. If you pick the Templates section at the top, youll see all the chart templates that are stored in the Templates folder, complete with a mini preview (Figure 18-20).

Figure 18-20. This computer has two custom chart templates to choose from. You can hover over either one to see the descriptive file name (minus the .crtx file extension).

When you select your template and click OK, Excel creates a new chart with the same formatting but using the data that's selected on your worksheet. Obviously, these options may not all apply to a new chart you create based on your template. Maybe your template includes formatting information for four series, but your new chart has only three. In this case, Excel just ignores any formatting information it's not using.

Note: The formatting in the chart template is just a starting point. If you want to reuse some of the formatting but not all of it, you're free to use any of the formatting techniques you learned about in this chapter to further refine your new chart.