2. Customizing Charts

Accessing Element Formatting Tools

In Chapter 1, “Introducing Charts in Excel 2010,” you learned how to create a chart using the Insert tab. You also learned how to choose built-in layouts and styles on the Design tab and apply them to your chart.

In real life, you usually want to have some control over the various elements in a chart. Excel 2010 provides three additional levels of control over chart elements:

• The Layout tab contains a few popular choices for formatting 15 chart elements. In 60 percent of cases, you can use options from the drop-down menus on the Layout tab to create a perfect chart.

• The Format tab contains icons that enable you to micromanage the color, fill, outline, and effects for any individual chart element. For example, if you want to apply a soft glow, a metallic finish, and a reflection to the January data point, you can do so using the Format tab.

• For ultimate control over individual elements, the powerful Format dialog box can be used to format the currently selected element in the chart. You can access the dialog box in the following ways:

• Select an element and press Ctrl+1.

• Right-click a chart element and select Format.

• Double-click any chart element,

• Select the chart element from the first drop-down on either the Layout or Format tabs and then click the Format Selection icon under the drop-down.

• Most of the drop-downs on the Layout tab lead to a More option that opens the Format dialog box.

This chapter walks you through the various chart components that can be customized. It also provides tips and tricks for creating eye-catching but meaningful results.

Identifying Chart Elements

Many elements of a chart can be customized. You rarely want to include all the available elements in a single chart because too many elements detract from the meaning of the data in the chart. Therefore, titles, axes, and gridlines should be used judiciously to help the reader understand the data presented in a chart.

Chart Labels and Axis

Figure 2.1 shows a chart that has too many elements that were included to help identify various elements in the chart.

Figure 2.1 This chart shows the various components available in a 2-D chart.

Figure 2.1 contains the following elements:

• Chart area—The entire range shown in Figure 2.1, including all the area outside the plot area. The chart area is where labels and legends often appear.

• Plot area—The rectangular area that includes the data series and data markers.

• Chart title—Typically appears in a larger font near the top of the chart. Whereas Excel 2003 always included the chart title outside the plot area, choices in Excel 2010 encourage you to have the title overlaying the plot area.

• Horizontal axis title—Identifies the type of data along the horizontal axis. In case it is not clear that “2005 2006 2010” represent years, you can add a horizontal axis title such as the one shown near number 9 in Figure 2.1.

• Vertical axis title—Commonly used along the left side of a chart to identify the units along the axis. In Figure 2.1, the word “Sales” near number 12 is the vertical axis title.

• Legend—Initially appears to the right of the plot area to help identify which color in the chart represents which series. The legend can be dragged anywhere in the chart area or plot area to free up space for the plot area to extend further to the right.

• Data label—Option used have the actual value for each bar or point to appear on the chart. Data labels are frequently overwritten by gridlines and other charting elements. Far too often, individual data labels are nudged so they can be read.

• Data table—Instead of using data labels, you can have Excel add a spreadsheet such as a data table beneath the plot area. The data table frequently takes up too much space in the chart area and reduces the size available for the plot area.

• Horizontal axis—Appears along the bottom of the chart for column and line charts and along the left side for bar charts. The horizontal axis is also referred to as the category axis. Your main choice is whether the axis contains a time series. If it does, Excel varies the spacing between the points to represent actual dates.

• Vertical axis—The axis along the left side of the chart in a column or line chart. In some advanced charts, you might have a second vertical axis on the right side of the chart. This axis typically contains values, and your main choice is whether you want the axis scaled in thousands, millions, and so on. This is discussed in Chapter 7, “Advanced Chart Techniques.”

• Horizontal gridlines—Run horizontally across the plot area and line up with each number along the vertical axis. If you do not have data labels on a chart, the horizontal gridlines are particularly useful for telling whether a particular point is just above or below a certain level. It is best to keep gridlines unobtrusive. Some of the best charts include gridlines in a faint color so they do not obscure the main message of the chart.

• Vertical gridlines—Used less often than horizontal gridlines. If you are considering using them in a line chart or a surface chart, consider using drop lines instead.

Special Elements in a 3-D Chart

Some chart elements are editable only in 3-D charting styles. Figure 2.2 shows a 3-D column chart.

The following are the 3-D chart elements:

• Back wall—In Figure 2.2, the back wall is formatted with a texture. In legacy versions of Excel, any formatting applied to the back wall also applied to the side wall. However, in Excel 2010 these are now two separate elements.

• Side wall—In Figure 2.2, the side wall is formatted with a dark fill.

• Floor—The surface below the 3-D columns.

• Column depth—One of the many 3-D rotation settings you can change. In Figure 2.2, each column appears to be a deep rectangular slab. This effect is created by increasing the column depth. The chart has also been tipped forward a bit, so it appears that the viewer is at a slightly higher viewing angle.

Figure 2.2 This chart shows the various elements available in a 3-D chart.

Analysis Elements

The Analysis group of elements located on the Layout tab includes elements that are of particular importance to scientists. However, some of the elements, such as the trendline, can also be useful in business charting. Figure 2.3 illustrates some of the analysis elements.

The following are the analysis elements:

• Trendline—If you ask for a trendline, Excel uses regression analysis to fit your existing data points to a statistical line. You can have Excel extend this line into future time-periods. In Figure 2.3, the dotted trendline shows that unless you alter the system that has been generating those actuals, you will likely miss the goal.

• Drop lines—The vertical lines that extend from the data point to the horizontal axis in either line or area charts. Drop lines are helpful because they enable the reader to locate the exact point where the line intersects the axis.

• Up/down bars—When you are plotting two series on a line chart, Excel can draw rectangles between the two lines. In Figure 2.3, when the actual exceeds the goal in Days 2 through 11, the up/down bars are shown in a dark color. When the goal exceeds the actual in Days 11 through 15, the up/down error bars are shown in a contrasting color. If the up/down error bars seem too wide, try using high/low lines.

Figure 2.3 This chart shows the various analysis elements.

• Error bars—This feature is popular in scientific analysis to show the error of an estimate. You might see these used in business charts to indicate the acceptable tolerance from a quality goal. For example, a quality goal might be to achieve 99.5 percent quality, with anything between 99 percent and 100 percent being acceptable. The error bars could be added to the 99.5 percent goal series to show whether the actual quality falls within the acceptable tolerance.

Formatting Chart Elements

When formatting chart elements, you generally start with some of the built-in choices available on the Layout tab. This section describes how to do this, and how to use the mini toolbar, Home tab, Format tab, or Format dialog to customize certain elements further.

Formatting a Chart Title

By default, a chart with more than one series is created without any title, as shown in the upper-right chart in Figure 2.4. To add a title, use the Chart Title drop-down on the Layout tab. Your choices are a centered overlay title or a title above the chart. When the title is added above the chart, Excel shrinks the plot area to make room for the title (see the lower-right chart in Figure 2.4). If the title fits, using a centered overlay title leaves more space for the title. However, there is the chance that the words in the title and the points plotted in the series will collide, as they do in the chart shown on the left in Figure 2.4.

Figure 2.4 Excel offers two built-in locations for chart titles.

Typing and Editing a Title

After you select a title location from the drop-down, Excel adds the generic “Chart Title” placeholder text to the top of your chart. The title is selected by default, so you can immediately type the actual title, which will appear in the formula bar. When you press the Enter key, Excel replaces “Chart Title” with the words you typed.

You might prefer to edit the title in the title box instead of typing the words in the formula bar. When you initially insert a title or when you single-click a title, the title will appear with a solid box that has four handles, as shown in the top of Figure 2.5. In this selection mode, you can format the entire title and type words in the formula bar.

If you perform a second single-click inside the title, the solid box will change to a dashed box as shown in the bottom of Figure 2.5. In this mode, you can edit characters right in the title box. However, any formatting will only apply to characters that are selected within the title.

To select characters while in Edit mode, you can either drag the mouse or use the following standard Windows mouse shortcuts: Double-click in a word to select the word or triple-click to select the entire title. Note that these shortcuts only work after you have performed a single-click to select the title, and then a second single-click to enter edit mode.

To exit Edit mode, either click directly on the dotted border or just click outside the title and then back on the title.

Figure 2.5 Click inside the solid title box to edit words directly in the box.

Moving a Title

To move a title, first single-click the title to select it, and then move the cursor so that it is above the outline of the title. When the cursor changes to a four-headed arrow, click and drag the title to a new location.

Formatting a Title with the Mini Toolbar

Once the title is in Edit mode with a dashed selection box, you can triple-click a title to select all the words in the title. When your cursor is moved slightly up and to the right, the mini toolbar appears, which allows you to select the font and other formatting to use in the title (see Figure 2.5).

If you have already moved the mouse too many pixels down and to the left, the mini toolbar may not appear. If this occurs, you can select the words and then right-click to make the mini toolbar appear.

Formatting a Title with the Home Tab

If trying to get the mini toolbar to display is too frustrating for you, you can use the Home tab instead. To do so, select characters in the title and then display the Home tab of the ribbon, and then use the icons in the Font and Alignment groups to format the title.

Formatting a Title with the Format Tab

Many options on the Format tab allow you to change the fill, font, and line style used in the title. The settings in the Shape Styles group affect the currently invisible rectangle that surrounds the title. A reflection cannot be added, but all the other settings are available.

To add effects to the actual words in a title, use the drop-downs in the WordArt Styles group of the Format tab (see Figure 2.6). You can control the font color, outline color of the font, shadow, reflection, and glow. Other WordArt features such as Bevel and Transforms are not available in charting titles.

Figure 2.6 Select a title and then use the three drop-downs in the WordArt Styles group to format the title.

More Title Options

To access the Format dialog, right-click a title, and then select Format Chart Title. This dialog offers Fill, Border Color, Border Styles, Shadow, Glow and Soft Edges, 3-D Format, and Alignment options. The Shadow option gives you microcontrol over the shadow transparency, size, blur, angle, and distance. The Alignment tab allows you to rotate the text in 1-degree increments.

Formatting an Axis Title

Figure 2.7 shows the three built-in options for the vertical axis: Rotated Title (top left), Vertical Title (bottom left), and Horizontal Title (bottom right). In many cases, the Rotated Title option looks the best. For a horizontal axis title, the only built-in option is to turn the title on or off.

All the formatting options available for the chart title also apply to the axis titles. You can use the mini toolbar, the Home tab, or the Format tab to change the font, color, fill, and effects for the axis title.

Figure 2.7 The vertical axis title can be oriented in three ways using the Axis Titles drop-down on the Layout tab.

Formatting a Legend

The built-in choices for the legend include having the legend outside the left, right, bottom, or top of the plot area. If you move the legend to the top or the bottom, Excel rearranges the legend in a horizontal format, as shown in Figure 2.8.

Figure 2.8 The built-in choices include moving the legend to the top or bottom, which works well in this chart.

My favorite legend trick, which is discussed in the next section, is to float a legend in an unused portion of the plot area. This can still be done in Excel 2010, but it is more difficult than in legacy versions because it takes a few more clicks now than it used to.

Floating a Legend in the Plot Area

To float a legend in the plot area, I recommend going outside the built-in legend options. Although the legend usually starts on the right side of the chart, you can often find a corner of the plot area that has whitespace. For example, due to the yearly growth of the data in Figure 2.8, there is room above the 2009 columns for the legend. Follow these steps to move the legend to that spot:

1. From the Layout tab, select the Legend drop-down, and then Overlay Legend at Right. This keeps the legend in a vertical arrangement and stretches the plot area out to the right edge of the chart.

2. Carefully click inside the legend. When the mouse pointer is a four-headed arrow, drag the mouse and drop the legend in a free spot on the chart.

3. While the legend is still selected, click the Format tab. Select Shape Fill, White to convert the transparent fill to a solid fill. The fill prevents gridlines from overwriting your legend titles.

4. Select Format, Shape Outline, Black to add a border around the legend.

Figure 2.9 shows the legend floating over a vacant area of the plot area. Notice that this allows the plot area to extend nearly to the right edge of the chart area.

Figure 2.9 Drag the legend over the chart and then format the legend so that it has a border and no transparency.

Changing the Arrangement of a Legend

A new series has been added in the top-left chart in Figure 2.10. This causes the floating legend to obscure part of the columns. To fix this problem, click the Legend to activate the resizing handles. Then drag the bottom-right corner of the legend up and out to produce a legend with the labels arranged in a 2-by-2 grid, as shown in the lower-left chart in Figure 2.10. If you keep dragging, the legend becomes a 1-by-4 arrangement, as shown in the top-right chart in the figure.

Figure 2.10 You have some control over the arrangement of the labels in the legend when you resize the legend border.

Formatting Individual Legend Entries

A feature in Excel 2010 is the ability to format or resize the individual legend entries. This proves particularly useful when one entry is longer than the other entries. To format an individual legend, follow these steps:

1. Click the legend so the entire legend is selected.
2. Click the legend entry you want to change.
3. To change the font size, right-click the text and use the options in the mini toolbar. To change anything else, use the Home tab or the Format tab.

In the chart at the bottom right of Figure 2.10, each legend entry is a different font size. You can also apply effects such as glow to the legend entries.

To remove certain legend entries, select an individual legend entry, and then press Delete. Deleting a legend is useful when you are using extra series to add elements to a chart. Several examples in Chapter 7, “Advanced Chart Techniques,” utilize this method.

Using the Format Legend Dialog

To use the Format Legend dialog, select More Legend Options from the Legend drop-down on the Layout tab. This dialog allows you to change the fill color, border color, border styles, shadow, and the glow and soft edges of the legend.

Adding Data Labels to a Chart

Individuals reading your chart might be able to discern whether one adjacent column is taller than the other column. However, it is difficult to compare a column in the center of a chart that has numbers along the left axis. This is where data labels can be an advantage. This feature works best if you have only a few data points on the chart.

Traditionally, data labels for a single-series column chart appear above each column. In Excel 2010, this choice is known as Outside End. The other built-in choices on the Data Labels tab are Center, Inside End, Inside Base, and None (see Figure 2.11).

Figure 2.11 Data labels look best when placed above the bars in a column chart.

The three “inside” choices are useful when you have a stacked column chart. Rather than placing the label for the bottom column inside the top column, the inside choices keep each label inside the appropriate bar.

Figure 2.12 compares the Inside End, Inside Base, and Center options on a single chart.

Figure 2.12 When multiple series are used in a stacked column chart, you should use one of the inside locations for the data label.

Nudging a Label to Avoid a Gridline

If your chart has dark gridlines, it may appear that the data label is crossed out by the gridline. It is a slightly tedious process to fix the labels, but doing so ensures that your data labels are readable. Complete the following steps to nudge a data label out of the way of the gridline:

1. Click a data label in the chart. Excel selects all the labels for that particular series.
2. Again, click the data label to be moved. Excel now selects just that data label (see Figure 2.13).
3. Carefully position the cursor over the border of the label. When the cursor changes to a four-headed arrow, click and drag the data label above or below the gridline.
4. Repeat steps 1-3 for any other labels that are running into the gridlines. If the next label is in the same series, you can skip step 1.

Figure 2.13 A second click selects just one data label.

Using the Format Data Labels Dialog

The Format Data Labels dialog includes nine categories in the left navigation bar. There are many important settings you can access in the various categories of the dialog to customize data labels on a chart:

• On the Label Options category, you can change the content of the label. For example, in Figure 2.14, the pie chart is labeled with the category name and the value. For other pie charts, you might want to use the percentage instead.

• On the Number category, you can change the number format or choose to have the format be the same as the original dataset.

Figure 2.14 Control the text in data labels by using the Format Data Labels dialog.

Adding a Data Table to a Chart

A data table is a mini-worksheet that appears below a chart. In it, Excel shows the values for each data point in the table. One advantage of a data table is that you can show the numbers that would normally be shown with data labels without adding any elements to the plot area.

There are two built-in options in the Data Table drop-down on the Layout tab: You can show the data table with legend keys or without legend keys.

The data table takes up a fair amount of space at the bottom of the chart. Putting the legend keys in the table allows you to regain some of that space by eliminating the legend element from the chart.

In the chart on the left in Figure 2.15, the legend keys appear in the chart. In the chart on the right in Figure 2.15, the data table appears without legend keys, which requires a separate legend element to appear. In this case, the legend element appears above the chart, which causes the plot area to shrink making it more difficult to interpret the data on the chart.

Figure 2.15 A data table provides a concise grid for the actual values without adding extra data to the plot area itself.

Special Options for Data Tables

If you select the More Data Table options in the Data Table drop-down, you will see the usual options for fill, border color, border styles, shadow, and 3-D format, plus special data table options. You can decide to hide or show the horizontal or vertical gridlines in the table and show or hide the outline around the data table. In Figure 2.16, the outline and horizontal gridlines are turned off, which gives the data table a cleaner look.

Figure 2.16 Special options in the Format Data Table dialog enable you to turn on or off various elements of a data table.

Formatting Axes

Unlike the previously described elements that are fairly cosmetic, the axis options have major ramifications. For example, axis options affect how data is displayed and whether the reader has the ability to interpret data in a chart.

The built-in choices in the Axes drop-down of the Layout tab do not begin to touch on the powerful choices in the Format Axis dialog box. For example, there are four kinds of axes, although the most you will find in a single chart is three. A pie chart actually has no axes. A radar chart has one axis. A surface chart has three axes. An X-Y chart has two axes. All other charts have two axes, with the possibility of adding an optional third axis.

Your most important choice for this axis is whether it should be time based or text based. In a chart with a text-based axis, the points along the axis are equally spaced. In a chart that uses a time-based axis, the points are spaced based on the relative time distance between the points. Other choices include selecting whether the data should be plotted left to right or right to left.

The vertical axis contains the scale for the numbers plotted in the chart. The primary vertical axis generally appears along the left side of the chart. In a bar chart, this axis, which appears along the bottom of the chart, is called a horizontal axis. Choices for this axis include scaling of the axis, the minimum and maximum value for the axis, and the distance between tick marks on the axis. If your data has numbers of different scales, you should specify a logarithmic axis.

In charts that have one data series that is of a vastly different order of magnitude than the others, you may want to plot that series on a secondary vertical axis. This axis has the same choices as the primary vertical axis. It usually appears along the right side of a chart or the top of a bar chart.

The depth axis is a special axis that appears in 3-D surface charts.

Built-in Axis Choices for the Horizontal Axis and Depth Axis

Excel 2010 provides a few built-in options on the Layout tab for each axis. As mentioned previously, these options are a tiny fraction of the ones available. It is interesting to note that the options chosen for the built-in menus are a few of the obscure options that you may not have discovered before.

For the horizontal axis, the built-in menu choices allow you to specify that the data should be plotted left to right or right to left. The other choice is for the horizontal axis to appear without any tick marks or labeling. This choice seems remarkably similar to the choice None (see Figure 2.17).

Figure 2.17 The choices in the Primary Horizontal Axis menu are interesting but obscure.

This is one of the few built-in charting menus where you can choose one item and then alter that selection by choosing another menu item. If you choose Show Right-to-Left Axis and then Show Axis Without Labeling, you get a right-to-left axis without labeling. However, if you reverse those choices, the axis labels reappear.

The choices for the depth axis in a surface chart are similar to those for the horizontal axis. However, instead of calling the choices Left-to-Right and Right-to-Left, the depth axis menu offers the options Normal Axis and Reverse Axis.

Built-in Choices for the Vertical Axis

For the vertical axis, Excel offers the choices None, Default, Thousands, Millions, Billions, and Logarithmic. The different scales are interesting and allow you to prevent having excess zeros along the vertical axis. In the bottom-left chart shown in Figure 2.18, there are so many zeros that it is difficult to figure out the numbers at a glance. In the top chart, the zeros are reduced, and a label indicates that the numbers are in billions.

Figure 2.18 Rather than fill your chart area with zeros, you can use an axis-scaling factor.

If you access the Format Axis dialog, the Display Units drop-down offers nine scaling options instead of Thousands, Millions, and Billions. You can use Hundreds, Ten Thousands, or even Trillions, as shown in the bottom-right chart in Figure 2.18.

Logarithmic is a useful choice when you need to compare numbers of different scales along the same series. This option is described in the next section.

Using a Logarithmic Axis

While the concept of logarithmic axes sounds scary, there is a simple use for logarithmic axes.

Say that you have a series of data with both large and small data values such as sales by model line. Your company probably has high-flying models that account for 80 percent of your revenue and then some older-model lines that are still hanging around. When you try to plot these items on a chart, Excel must make the axis scale large enough to show the sales of the best-selling products. This causes the detail for the smaller product lines to become lost because the values are a relatively small percentage of the entire scale.

In Figure 2.19, there is important data regarding the first three product lines. However, when looking at the chart, no one will be able to see if the sales of these products were near the forecast.

Figure 2.19 It is impossible to see the detail of the smaller product lines because they are relatively small compared to the largest product line.

When this occurs, the solution is to convert the axis to a logarithmic axis. In a logarithmic axis, the distance from 1 to 10 is the same as the distance from 10 to 100, and so on. This allows you to see detail of the product selling a few hundred units as well as the products selling 100,000 units.

To convert to a logarithmic scale, select Layout, Axes, Primary Vertical Axis, Show Axis with Log Scale. The result is a chart such as the one in Figure 2.20. Notice that now you can see that the actual sales were lower than the forecast in the A107 and A106 product lines.

Figure 2.20 If you convert to a log scale, you can see detail for small items as well as large items.

Consider Date Versus Text-Based Axes

In most Excel charts, the points along the horizontal axis are equidistant. This makes sense when you are comparing departments or regions, or even when comparing months of the year.

At times, the horizontal axis might be based on dates that have points that may not be equally spaced. Figure 2.21 shows the results of random quality control audits. For example, audits do not occur at regular periods since they are supposed to be a surprise. In the top chart, the axis uses a text-based setting that shows the points at equal distances from each other. In the bottom chart, the axis uses a date-based setting that shows certain audits happened closer to or farther away from each other.

Figure 2.21 The text-based axis in this figure shows that you can explicitly control whether the horizontal axis is equally spaced or if it is date based.

Excel often autoselects the type of axis to use. To control the axis setting, select Layout, Axes, Primary Horizontal Axis, More Primary Horizontal Axis Options, Axis Options, and then choose either Text Axis or Date Axis from the Axis Type section.

Changing the Scale of an Axis

Typically, a chart has a vertical axis that runs from zero to a value larger than the largest value in the dataset. This is not ideal for some datasets.

The chart in Figure 2.23 shows daily changes in a measurement that typically fluctuates from 802 to 804. If you use a zero-based vertical axis, as in the top chart, you cannot make out any variability. Instead, if you change the vertical axis to run from 801 to 804.5, you can see the daily fluctuations in the value, as shown in the bottom chart in the figure.

Figure 2.23 Altering the scale of the axis allows you to see detail in certain datasets.

Clearly, altering the axis in Figure 2.23 makes sense. If you didn’t change the axis, the chart reader would not be able to make out the variability in a value that is fluctuating wildly within the expected range of values.

In other cases, altering the axis is a great way to mislead people. For example, the top chart in Figure 2.24 shows a 50 percent increase from 1997 to 2006. If someone wants to manipulate the reader’s opinion, he or she can change the minimum and maximum values along the vertical axis. The bottom chart contains the same data as the top chart, but the increase looks much more severe because the scale has been adjusted to focus on values between 5 and 9.

When you create a chart, Excel automatically decides whether the vertical axis should reach to zero. The rule is fairly simple: If the range between the lowest and highest values in the dataset is less than 20 percent of the lowest value, Excel automatically chooses a scale that is not zero based. For example, suppose are plotting data that ranges from 100 to 198. This is a range of 98. Because 98 is almost 50 percent of the lowest value, Excel automatically creates a vertical axis that extends to 0. As another example, say you are plotting data that ranges from 1,000 to 1,098. This is a range of 98. However, 98 is less than 10 percent of the lowest value, which means Excel will create a vertical axis that brackets the range—perhaps 950 to 1,150.

Figure 2.24 Two charts of the same data paint different pictures when the range of the vertical scale is adjusted. The single vertical line between ’99 and ’00 is actually drawn in using Insert, Shapes.

If you are not trying to use your chart to mislead the chart reader, you can usually accept Excel’s decision on the vertical axis scale. However, if you need to override the setting, follow these steps:

1. Select the chart.
2. Select Layout, Axes, Primary Vertical Axis, More Primary Vertical Axis Options. Excel displays the Format Axis dialog. The axis scale options are in the Axis Options category.
3. To override the minimum value on the axis, select the Fixed option button for Minimum. You can then type a value for the minimum value to show on the axis.

Figure 2.25 shows the options that are available in the Format Axis dialog. Note the settings Major Unit and Minor Unit in Figure 2.25. These values are used to control the placement of horizontal gridlines on the chart.

Figure 2.25 Control the minimum and maximum values along the vertical axis by using this dialog.

Displaying and Formatting Gridlines

Gridlines help the reader locate data on a chart. Without gridlines, it is difficult to follow the plotted points over to the vertical axis to figure out the value of a point.

Gridlines work in conjunction with the Major Unit and Minor Unit settings in the Format Axis dialog (refer to Figure 2.25). The built-in options in the Layout tab enable you to turn on major, minor, major and minor, or no gridlines.

Figure 2.26 The built-in choices allow you to display major gridlines, minor gridlines, both, or none.

Figure 2.26 shows four versions of the same chart. In this chart, the major axis unit is 50, and the minor axis unit is 10.

Choosing to display minor gridlines, as in the chart in the upper right of Figure 2.26, causes Excel to draw 25 horizontal lines on the chart. This may seem like overkill because it is difficult to follow the gridlines across the chart.

Initially, both major and minor gridlines are formatted as 0.75-point lines. This means that selecting Major & Minor Gridlines looks the same as selecting just Minor Gridlines. To make the major gridlines stand out, as in the lower-right chart in Figure 2.26, you need to format the major gridlines.

Creating Unobtrusive Gridlines by Using Format Gridline

Gridlines start out as 0.75-point lines. If you plan to display major and minor gridlines on a chart, you might want to format the gridlines differently.

Each Format Gridlines dialog box has three categories in the left navigation bar:

• Line Color—A solid-color line can be chosen in any color, a gradient line in a variety of colors, or an automatic line. When a gradient line is chosen, Excel applies a slightly different shade to each gridline.

• Line Style—The width of each gridline can be controlled in 0.25-point increments. You can choose Dash Type to make the gridlines appear as dots, dashes, or various combinations.

• Shadow—A shadow can be added to your gridlines. Settings allow you to change the shadow color, transparency, size, blur, angle, and distance. Nothing shouts “chartjunk” more than a shadow on gridlines.

Figure 2.27 Make the gridlines as subtle or prominent as you want.

Figure 2.27 shows four varieties of horizontal gridlines and the Line Style category of the Format Gridlines dialog box.

Controlling Placement of Major and Minor Gridlines

Settings in the Format Axis dialog can be used to control the placement of major and minor gridlines. For the vertical axis, you can change the Major Unit and Minor Unit settings to control the spacing of the horizontal gridlines. Even though you are allowed to choose something else, for best results, the major unit should be a multiple of the minor unit.

Figure 2.28 By changing the units on the Format Axis dialog, you can control the spacing between horizontal gridlines.

The top and left charts in Figure 2.28 show various settings for the major and minor units and their impact on the gridlines.

To control the placement of vertical gridlines, you have to format the horizontal axis. There is a setting called Interval Between Tick Marks. In the lower-right chart shown in Figure 2.28, the interval has been increased to 5. This causes Excel to display a vertical gridline after every five points.

Formatting the Plot Area

Thankfully, Excel 2010 creates a simple white plot area as the default. In legacy versions of Excel, the plot area always started as a gray background, which looked horrible when the chart was printed on a monochrome printer.

When the plot area was gray, I almost always customized the plot area to do away with the gray. Now you can decide if you want to change the white to something a bit fancier.

The built-in choices on the Layout menu are to either turn off the plot area, resulting in a white background, or use a fill of the default color, as defined by the chart style selected on the Design tab. In many cases, this default is white, so both choices lead to the same result.

To have better control over the plot area, you need to choose the More Plot Area Options from the built-in menu. Format Plot Area allows you to choose No Fill, Solid Fill, Gradient Fill, or Picture or Texture Fill.

Some of the cool plot area options available in Excel 2010 involve changing the transparency of the plot area. This actually works in conjunction with the formatting of the chart area. If you make the plot area 90 percent transparent, you can see the formatting of the chart area. If you format the chart area to be transparent, then you see through to the spreadsheet.

Using a Gradient for the Plot Area

Setting up a simple two-color gradient has become far more difficult in Excel 2010 than it used to be. Microsoft offers many more choices for controlling the gradient, but this causes the process of setting up the gradient to be more difficult.

The first set of choices involves selecting whether the gradient should be linear, radial, or rectangular, or whether it should follow the path of the shape. Within the linear gradients, you can specify a direction such as 90 degrees for top to bottom or 180 percent for right to left. With the radial and rectangular gradients, you can specify whether the gradient radiates from the center or from a particular corner. The path type creates a gradient that is relative to the shape of the bounding object. Figure 2.29 shows a variety of gradient types.

The next choice involves the number of colors. You can choose a predefined color scheme from the Preset drop-down or you can specify your own color scheme by defining a number of gradient stops. Each stop is assigned a color, transparency, and position ranging from 0 percent to 100 percent. Excel 2010 offers an improved Gradient Stop bar that allows you to visualize the position of each stop in the gradient. Click a stop to move it or adjust the settings for that stop. Use the + or – buttons to the right of the bar to add or remove stops. You can edit only one gradient stop at a time.

Figure 2.29 The gradient types can be linear, radial, or rectangular.

Figure 2.30 Creating a two-color gradient requires many more steps in Excel 2010 than in legacy versions.

Using a Picture or Texture for the Plot Area

You have many options available when you want to use a texture or a picture for the plot area. When choosing Picture or Texture Fill, a Texture drop-down appears, with the same two dozen textures that have been in Office for years. To use a picture instead of a texture, click the File button and browse your drives to locate a picture to insert.

When you choose a picture, it is stretched or shrunk to fill the plot area completely. This might cause your logo to be a little squashed, as shown in the top-left chart in Figure 2.31. To make your picture keep its correct aspect ratio, select the Tile Picture as Texture check box in the Format Plot Area dialog.

When you choose to tile the picture, a number of choices are available in the Tiling Options section. In the top-center chart in Figure 2.31, the Scale X and Scale Y options are set to 10 percent to create a repeating pattern of logos. The first logo is aligned with the top left of the plot area.

You can choose alternating tiles of the image to be mirror images of the first. However, this is not appropriate when using logos. Every other column can show horizontal mirror images, as in the top-right chart in Figure 2.31. You can also have every other row show vertical mirror images or turn on both horizontal and vertical mirror images, as shown in the bottom-right chart in Figure 2.31.

To lighten the picture, adjust the Transparency slider to the right. The bottom-left chart in Figure 2.31 has an 80 percent transparency.

Figure 2.31 When choose a picture, clip art, or texture, you can choose to show a single stretched image or to tile the image at various sizes.

Mixing the Plot Area Color with the Chart Area Color

The Transparency slider on the Format Plot Area dialog allows you to see through the plot area to view whatever formatting has been applied to the chart area.

Notice that there is not an icon on the Layout tab for formatting the chart area. Select the chart area by clicking any whitespace just inside the border of the chart. Click Format Selection in the Layout tab, press Ctrl+1. Alternatively, select Format from the right-click menu to access the Format Chart Area dialog.

In the top three charts in Figure 2.32, the chart area includes the default opaque white formatting. If you specify that the black formatting of the plot area be 75 percent transparent, you have a plot area that is 25 percent black and 75 percent white. If you choose a black plot area that is 100 percent transparent, you see through the plot area, to the white chart area background.

Figure 2.32 Increasing the transparency of the plot area allows the formatting of the chart area to show through.

The transparency options become more interesting if you first format the chart area. In the bottom-center chart in Figure 2.32, the chart area has been formatted with a wood grain effect. This is achieved by making the plot area black with a 60 percent transparency.

In the bottom-right chart in Figure 2.32, the chart area has been formatted with the None option or using a solid color with 100 percent transparency. When the plot area is formatted as 100 percent transparent, you can see through the plot area and the chart area to the underlying cells in the spreadsheet.

Formatting the Chart Walls and Floor of a 3-D Chart

With a 3-D chart, you can format the chart wall and chart floor instead of the plot area. Drop-downs on the Layout tab offer built-in choices to turn on the formatting of either the chart wall or chart floor. Three different components can be formatted: the back wall, side wall, and chart floor.

If you use the Current Selection drop-down on the left side of the Layout tab, you can select the floor, back wall, side wall, or walls. Selecting walls is the same as selecting both the back wall and the side wall.

After you have selected an element, use the Format Selection button under the drop-down to format the element. As with the plot area, you can specify a solid fill, a gradient, a texture, or a picture.

Figure 2.33 illustrates the various elements that can be individually formatted.

Controlling 3-D Rotation in a 3-D Chart

A number of options are available for rotating a 3-D chart. Of the available options, you are most likely to change the X rotation and leave the other angles alone:

• X rotation—Choose a value from 0 to 359.9 degrees to rotate the floor of the chart counterclockwise. The chart at the top-right of Figure 2.34 has been rotated 50 degrees from the default rotation. This rotation forces the series names to move clockwise to the left. In some cases, it might be easier to see a short series by rotating the x-axis.

• Y rotation—Choose a value from –90 degrees to 90 degrees to change the height of the viewer with respect to the chart. In the default 15 percent Y rotation, it appears that the reader is slightly above the chart. For a 0-degree rotation, the view appears that the reader is at about eye level with the chart, as shown in the chart at the top left of Figure 2.35. At a 90-degree rotation, the reader appears to be looking straight down on the chart, which makes it impossible to judge the height of any columns, as shown in the chart at the bottom right of Figure 2.35. Negative rotations create a view where the reader is actually looking up at the chart from underneath a transparent floor, as shown in the bottom-left chart in Figure 2.35. For best results, you should use values from 0 to 20 degrees for the Y rotation.

Figure 2.33 In a 3-D chart, you can format the walls individually or as a single unit.

Figure 2.34 Rotating the x-axis turns the floor of the chart clockwise.

• Perspective—Choose a value from 0 to 120 to distort a chart further. If you have used a wide-angle lens on a camera, you might have noticed that items in the foreground appear unusually large, while items in the background appear unusually small. Increasing the perspective is similar to using a wide-angle camera. The chart at the top right of Figure 2.36 has an increased perspective. Note how the Q4 bars seem to shrink in this view.

Figure 2.35 Your angle of a view can be changed by using the Y rotation.

Figure 2.36 Settings for perspective, depth, and height let you twist and stretch a chart.

• Depth—Choose a value from 0 percent to 2000 percent of the base. Decreasing the ratio of depth to the base creates a chart where the columns become wide rectangles, as shown in the chart at the lower left of Figure 2.36.

• Height—Clear the Autoscale check box to enable the Height setting. You can choose a value from 0 percent to 500 percent of the base. The bottom-right chart in Figure 2.36 shows a chart with an increased height:width ratio.

Forecasting with Trendlines

A trendline attempts to fit existing data points to a formula and extend that formula into the future. When a trendline is added to a chart, Excel uses least-squares regression to find the best line to represent the data points. Excel can either draw the trendline for existing points to indicate if the points are trending up or down, or it can extend the trendline into the future to project whether you will meet a goal.

In Figure 2.37, actual progress toward a goal is tracked in Column B. Note that because the project is in process, several points in the dataset are not yet filled in.

Adding a trendline allows you to project when the project will finish if you continue working at the current pace. To add a trendline, select Layout, Trendline, Linear Trendline. Excel asks to which series it should add a trendline. If you choose the actual series, Excel adds a new virtual series to the chart. This series is plotted out to the end of the data series. In Figure 2.38, you can see that the linear trendline is projecting that you will have only 50 units complete by the 20th day.

Although creating a trendline requires only a few clicks, several trendline options are worth learning. These are described in the following subsections.

Figure 2.37 This chart shows the actual points and a goal line that indicates how much progress should be made each day in order to reach the project completion.

Formatting a Trendline

To format a trendline, right-click the trendline and select Format Trendline. Options in the Format Trendline dialog allow you to change the line color and the line style. I usually change the dash type on the Line Style category to show a dashed line. This indicates that it is not actual data, but a computer projection. I also change the color, often to red. When a project is falling behind schedule, the red dashed trendline becomes a call to action. The trendline says, “You need to pick up the pace of production—not just for one day, but for several sustained days.”

Figure 2.38 The trendline indicates that based on your current run rate, you will severely miss the deadline.

Say that you suddenly do 10 units of production on the 11th day. This brings the project back on track with 42 units complete, when 41.25 are needed to stay headed toward the goal. However, when the trendline looks at your average daily production, it sees that you spend 10 days only averaging 3 units a day. The trendline is not convinced that just because you had one good day, you will make the final goal. In this case, the trendline adjusts the projection from 50 to about 57. This makes the trendline seem like a bit of a cynic. However, in reality, the trendline is just using simple math to analyze your behavior so far when working on this project.

Adding the Trendline Equation to a Chart

To help understand how a trendline is calculated, you can display the equation on the chart.

In the Trendline Options category of the Format Trendline dialog, select Display Equation on the Chart. For a linear trendline, Excel displays a trendline in the form of y = mx + b.

Alternatively, you can choose to force the y-intercept to be zero or display the R-squared value on the chart. R-squared is a measure of how closely the trendline fits the existing points. While R-squared can range from 0 to 1, values closer to 1 indicate that the trendline is doing a good job of representing the data points.

In Figure 2.39, the equation predicts that you magically started with 6.8 units done on Day 0 and then have averaged 2.5 units a day thereafter. This is pretty good because in this case, several units were finished in the previous month but did not make up a full case pack, so their totals were added to Day 1 of the current month. The R-squared of 0.85 says that the trendline is a pretty good fit. The bottom line is that you need to focus on production for the next nine days if you want to make the goal.

Figure 2.39 An equation can be added to a chart to see how Excel is fitting the chart to the line.

Choosing a Forecast Method

In business, a linear trendline is used most often, which assumes a constant rate of progress throughout the life of the chart. Several other forecast methods are available for trendlines:

• Exponential trendline—Used most often in science. Describes a population that is rapidly increasing over successive generations such as the number of fungi in a Petri dish over time.

• Logarithm trendline—Results when there is an initial period of rapid growth that levels off over time.

• Polynomial trendline—Can describe a line that undulates due to two to six external factors. When you specify a polynomial trendline, you have to specify which order of polynomial. For example, in a third-order polynomial, the line is fit to the equation y = b + c1x + c2x2 + c3x3.

• Power trendline—Fits the points to a line, where y = cxb. This describes a line that increases at a specific exponential rate over time.

• Moving average trendline—Used to smooth out data that fluctuates over time. A typical trendline would use a three-month moving average.

Adding Drop Lines to a Line or Area Chart

Area and line charts are great at showing trends, but it is often difficult for the reader to figure out the exact values for data points. Since the line is floating in space, you are counting on the viewer’s eye being able to travel from the horizontal axis straight up to the data series line.

A drop line is a vertical line that extends vertically from a line or area chart and extends down to the horizontal axis. As shown in the lower chart in Figure 2.40, the drop lines help you see that the March point is exactly at 300. This would be difficult to discern in the chart shown at the top of Figure 2.40.

To add a drop line, select the chart, and then select Layout, Lines, Drop Lines.

Figure 2.40 Drop lines help you visualize the exact value of each point along the horizontal axis.

Adding Up/Down Bars to a Line Chart

If you have a line chart with two different data series, you might want to compare the series at each point along the horizontal axis. There are two different options for this comparison that are available in two locations along the Layout tab of the tab.

In the lower chart in Figure 2.41, high-low lines extend from one line to the other line. These are created using Layout, Lines, High-Low Lines.

In the chart at the right in Figure 2.41, up/down bars extend from one line to the other line. These bars appear in contrasting colors, depending on which line is higher at that particular point. To add up/down bars, you select Layout, Up/Down Bars, Up/Down Bars.

Figure 2.41 High-low lines and up/down bars help show the relationship between two series at specific points along the x-axis.

Showing Acceptable Tolerances by Using Error Bars

I used to run monthly sales and operations planning meetings. In these meetings, the leaders of the sales, marketing, engineering, and manufacturing departments would decide on the production plan for each sales model. During the next month’s meeting, a chart would compare the previous month’s forecast to actual demand. If the demand was more than 20 percent above or below the plan, someone had to present the root causes for that variance. The discussion was often lively: Did the east region really have no clue that Customer XYZ was about to order 1,000 units or was it sandbagging so it could have a lower quota?

To figure out which products required this scrutiny, both the forecast and actual performance were plotted for each product line. Error bars showing the acceptable 20 percent tolerance were added to the forecast line. If the actual line was within the 20 percent tolerance, no discussion was needed.

Excel 2010 makes it easy to add error bars: Select one series in the chart and then select Layout, Error Bars, More Error Bar Options. In the Format Error Bars dialog, choose if the error bars should extend up, down, or both from the line. You can add a cap or have no cap. In the lower section, specify one of five methods for calculating the size of the error bar. Based on the standard deviation, specify that the error bars should extend a fixed number of units. In the sales and operations planning meetings, it was appropriate to add an error bar that was 20 percent of each data point. Therefore, if someone had forecast 200 units of a product, anything from 160 to 240 was an acceptable demand. For the products with a forecast of 8,000, the acceptable tolerance was 6,400 to 9,600.

Figure 2.42 shows a chart with 20 percent error bars.

Figure 2.42 The error bars show an acceptable tolerance or a margin of error around each point.

Formatting a Series

It is somewhat strange that there is not a button on the Layout tab for the most popular charting element: the Series element.

To format the series, you can use the Current Selection drop-down on the left side of either the Layout tab or Format tab, and then Choose the appropriate series from the list to select the series. Click the Format Selection button to access the Format Data Series dialog.

The Series Options category allows you to plot a series on a secondary axis. This is useful if one data series contains data that is of a different order of magnitude than the other series. For example, you might chart revenue and gross profit percent. By moving the gross profit percent to the Secondary Axis, you can see detail of revenue in thousands and gross profit percentages in the 50 percent range. When you move one or more series to the secondary axis, you should change the chart type of those series; otherwise, the column markers are drawn on top of each other. Figure 2.43 shows an example where GP% is plotted as a line chart using the secondary axis.

Figure 2.43 GP% is moved to the secondary axis and then changed to a line chart.

Using the remaining categories, you can edit the Marker style for a series including the marker color, line color, line style, shadow, and 3-D formatting.

Formatting a Single Data Point

The first click on a data series selects the entire series. If you pause for a moment and then perform a second click on a data point, the Current Selection drop-down indicates that you have selected an individual data point. The drop-down might say Series “Profit” Point “2011.”

It is interesting that the only way to perform this selection is by performing two single-clicks on the chart. The Current Selection drop-down does not offer a list of all the data points.

After selecting a data point, use the Format Selection button located on the left side of the Layout or Format tab. You can then change the color or marker style of a single point.

Using the Format Tab

When you first install Excel 2010, all the new chart options allow you to create fresh and new-looking charts. If you stay on the Design tab, you can create 21,600 different charts. If you expand to the Layout tab, you can create 601 billion different charts. If you include the 20 themes on the Page Layout tab, you can create 12 trillion different chart types. If this is not enough variability for you or if you get tired of these built-in options, you can head to the Format tab and have microcontrol over the formatting for any element of the chart.

There actually are a few very cool settings on the Format tab. If you venture there, you can create amazing translucent charts to impress your coworkers who have become bored with the standard Excel 2010 charts.

Converting Text to WordArt

The WordArt Styles group on the Format tab is used to apply WordArt styles to any text on a chart. You are not allowed to use the type-twisting options in the Transform menu, but you can add shadow, glow, or reflection to the 20 styles in the WordArt menu.

Using the Shape Styles Gallery

The Shape Styles gallery contains 36 built-in styles that can be applied to any shape. There are really six styles for each of the six accent colors in the current theme. The six styles progress from simple on the first row of the gallery to extreme on the last row of the gallery.

To format a particular element, click that element and choose a new style from the gallery. When using the Shape Styles gallery, keep these tips in mind:

• The colors and effects available in the gallery change if you select a new theme from the Page Layout tab.

• The first time you click a column, select the entire series. A second click selects only one column or data point.

• If you find it difficult to select a particular item on a chart, you can select the item from the Current Selection drop-down at the left edge of the Layout or Format tabs.

• Live Preview works in this gallery. Even though you see the effect after hovering over a tile in the gallery, you actually have to click the tile to apply the style.

Figure 2.44 shows the Shape Styles gallery and six charts that represent the six styles available when the Office theme is active.

Using the Shape Fill and Shape Effects

Some of the best effects are not available in the built-in charts. However, you can access myriad other effects by using the Shape Fill, Shape Outline, and Shape Effects drop-downs on the Format tab. Figure 2.45 shows a chart in which each bar has a different effect from these drop-downs applied.

Figure 2.44 The Shape Styles gallery offers six effects for each of six accent colors.

Figure 2.45 These effects are available on the Format tab.

Using Preset Shape Effects

The Preset menu on the Shape Effects drop-down offers 12 built-in combinations of shadow, reflection, glow, soft edges, bevel, and rotation. Figure 2.46 shows the 12 preset thumbnails plus several charts created with the various presets.

For more control of the effects, choose the 3-D Options selection at the bottom of the menu. In the 3-D Format category, the Material drop-down offers 11 textures. The final three textures are translucent textures that give a glass appearance when applied to a bar. In Figure 2.47, the five bars are formatted as wire frame, powder, translucent powder, clear, and plastic.

Figure 2.46 The 12 charts on the sheet match the sequence of built-in presets on the menu.

Figure 2.47 These bars were formatted by changing the surface material in the Format Data Point dialog.

Replacing Data Markers with Clip Art or Shapes

Instead of using columns or cylinders for your data markers, you can replace the column with either some clipart or even a shape from the Shapes gallery. To achieve this effect, change the fill of a data series, and then turn off the border around the data point.

Using Clip Art as a Data Marker

Office Online offers thousands of free clip art images. If you have an Internet connection, you can insert any of these images in a chart to replace the data markers. Follow these steps to create a pictograph:

1. In a 2-D column chart, select a data series. Right-click the data series and select Format Data Series.
2. In the Format Data Series dialog that appears, select the Fill category in the left panel. In the right panel, choose Picture or Texture Fill.
3. Click the Clip Art button. Excel displays a shortened version of the clip art pane, called Select Picture.
4. Select the Include Content from Office Online check box.
5. In the Search Text box, type a keyword to describe the clip art and then click Go.
6. Browse through the returned images. You are looking for something that is cartoonish and narrow. Rather than clip art with a detailed background, look for clip art where only the character appears. When you find an acceptable image, click OK.
7. Click the Border Color category and select No Line.
8. Click Close to close the Format Data Series dialog.

The result is a chart similar to the bottom chart in Figure 2.48. The clip art is stretched to indicate the height of the bar.

Using a Shape in Place of a Data Marker

Replacing a data marker with one of the 175 shapes in the Shape dialog box is slightly more difficult than replacing a data marker with clip art. The fill settings on the Format Data Series dialog do not allow you to specify a shape. However, they allow you to import a shape from the Clipboard.

Figure 2.48 Replace the data markers with shapes or clip art for visual interest.

Follow these steps to create the top chart in Figure 2.48:

1. Create a 2-D column chart.
2. Select a cell in the worksheet. From the Insert menu, choose an upward arrow from the Shapes drop-down.
3. Click and drag to draw an arrow on the worksheet.
4. Right-click the shape and then select Copy.
5. Click the data series in the chart. Right-click and select Format Data Series.
6. In the Format Data Series dialog that appears, choose the Fill category in the left panel. In the right panel, choose Picture or Texture Fill.
7. In the Insert From section, click the Clipboard button. Excel replaces the columns with the arrows.
8. Click Close to close the Format Data Series dialog.

This technique works best with 2-D charts. If you attempt to apply a shape to a 3-D column, the shape is pasted to each vertical face of the column.

To watch a video of using shapes in place of columns, search for MrExcel Charts 2 at YouTube.

Next Steps

In Chapter 3, “Creating Charts That Show Trends,” you will see examples of charts that show trends such as column and line charts. You will also learn how to use a trendline to project a trend into the future.