18.2. Adding Chart Elements
You build every chart 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.
Figure 18-3 shows the different elements that make up a chart. They include:
Not all charts include all these elements. As you learned in Section 18.1.2, the layout you pick determines whether you begin with a chart title, a legend, gridlines in the background, and so on. However, in many cases you'll want to pick and choose exactly the elements you want. Excel lets you do this choosing with the buttons on the ribbon's Chart Tools Layout tab. The following sections show you how.
18.2.1. Adding Titles
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.
An ordinary chart can include a main title (like "Increase in Rabbit Population vs. Decrease in Carrot Supplies") and titles on each axis (like "Number of Rabbits" and "Pounds of Carrots"). To show or hide the main title, make a selection from the Chart Tools Layout Labels Chart Title list. Your options include:
Once you select one of those options, you see the title box; you can click inside it and type in new text, as shown in Figure 18-4.
You can just as easily add a title to each axis using the Chart Tools Layout Labels Axis Titles Primary Horizontal Axis Title and Chart Tools Layout Labels Axis Titles Primary Vertical Axis Title lists. Youll find options for showing your title, hiding it, and (in the case of a vertical axis), showing a title that's rotated to run neatly along the side of your chart.
Tip: As with almost all chart elements, you can also format titles by adding a border, a shadow effect, and a fancy background fill. To get these options, right-click the title, and then choose Format Chart Title. You'll learn more about these options throughout this chapter.
18.2.2. Adding a Legend
Titles help explain a chart's overall purpose. Usually, titles indicate what a chart is comparing or analyzing. You may 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 (like the sales results in two different stores). You can fix this problem by adding 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 you've used to draw that series on the chart.
Excel automatically adds a legend to most charts. If you don't already have a legend, you can choose a layout that includes one, or you can make a selection from the Chart Tools Layout Labels Legend list. Different selections let you position the legend in different corners of the chart, although true Excel pros just drag the legend box to get it exactly where they want it.
Legends aren't always an asset when you need to build slick, streamlined charts. They introduce two main problems:
If you don't want to use a legend for these reasons, you can use data labels instead, as described in the next section.
18.2.3. Adding Data Labels to a Series
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, choose a position from the Chart Tools Layout Labels Data Labels list. If you choose Chart Tools Layout Labels Data Labels Center on a column chart, each bars value appears as a number that's centered vertically inside the bar. On the other hand, if you choose Chart Tools Layout Labels Data Labels Outside End, the numbers appear just above the top of each column, which is usually more readable (Figure 18-5).
Tip: No matter how you choose to label or distinguish a series, you're best off if you don't add too many of these elements to the same chart. Adding too many labels makes for a confusing overall effect, and it blunts the effect of any comparison.
If you're in an adventurous mood, you can create even more advanced labels by choosing Chart Tools Layout Labels Data Labels More Data Label Options. The Format Data Labels dialog box appears, with a number of options for customizing data labels (Figure 18-6).
Using the Format Data Labels dialog box, you can choose the data label's position (just like you could from the Chart Tools Layout Labels Data Labels list). But the options under the Label Contains heading are more interesting, as they let you chose the information that appears in the label. Ordinarily, the information is simply the value of the data point. However, you can also apply a combination of values. Your exact options depend on the type of chart you've created, but here are all the possible choices:
Note: In some charts (including XY scatter charts and bubble charts), the checkboxes "Category name" and "Value" are renamed to "X Value" and "Y Value", although they have the same effect as "Category name" and "Value."
When 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 (with a comma, space, semicolon, new line, or a character you specify). And if you want to display a mini square with the legend color next to the label, then choose "Include legend key in label" (although most people don't bother with this feature).
Figure 18-7 shows more advanced data labels at work.
Tip: Wondering what your chart will look like? As you make changes, Excel updates the chart on the worksheet using its handy live preview feature. Just move the Format Data Labels dialog box out of the way to get a sneak peak before you confirm your choices.
18.2.4. Adding Individual Data Labels
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. Figure 18-8 illustrates the difference.
To add an individual data label, follow these steps:
Tip: If a data label doesn't have exactly what you want, you can click inside it and edit the text, just as you do with a chart title.
As with data series labels, right-clicking a data label gives you a choice of formatting options (choose 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. Section 18.4 describes more about formatting chart elements.
Tip: Instead of using data labels, you can add arrows and text boxes anywhere on a chart to call out important information. To do so, you need Excel's drawing features, explained in the next chapter.
18.2.5. Adding a Data Table
Trying to pack as much information as possible into a chartwithout cluttering it upis a real art form. Some charting aficionados use labels, titles, and formatting to highlight key chart details, and then use the data on the worksheet itself to offer a more detailed analysis. However, Excel also provides a meeting point between chart and worksheet that works with column charts, line charts, and area charts. It's called the data table .
Excel's data table feature places your worksheet data under your chart, but lined up by category. You can best understand how this feature works by looking at a simple example, like the one in Figure 18-10.
To add a data table, select your chart, and then choose Chart Tools Layout Labels Data Table Show Data Table. Or, if you want each series in the data table to have a small square next to it with the same color as the matching data series, then choose Chart Tools Layout Labels Data Table Show Data Table with Legend Keys. This way, you might not need a legend at all.