Chapter 19. Learning Advanced Charting

IN THIS CHAPTER

Understanding chart customization

Changing basic chart elements

Working with data series

Discovering some chart-making tricks

Excel makes creating a basic chart very easy. Select your data, choose a chart type, and you're finished. You may take a few extra seconds and select one of the prebuilt Chart Layouts, and maybe even select one of the Chart Styles. But if your goal is to create the most effective chart possible, you probably want to take advantage of the additional customization techniques available in Excel.

Customizing a chart involves changing its appearance as well as possibly adding new elements to it. These changes can be purely cosmetic (such as changing colors modifying line widths, or adding a shadow) or quite substantial (say, changing the axis scales or adding a second Value Axis). Chart elements that you might add include such features as a data table, a trend line, or error bars.

The preceding chapter introduced charting in Excel and described how to create basic charts. This chapter takes the topic to the next level. You learn how to customize your charts to the maximum so that they look exactly as you want. You also pick up some slick charting tricks that will make your charts even more impressive.

19.1. Selecting Chart Elements

Modifying a chart is similar to everything else you do in Excel: First you make a selection (in this case, select a chart element), and then you issue a command to do something with the selection.

You can select only one chart element (or one group of chart elements) at a time. For example, if you want to change the font for two axis labels, you must work on each set of axis labels separately.

Excel provides three ways, described in the following sections, to select a particular chart element:

• Mouse

• Keyboard

• Chart Elements control

19.1.1. Selecting with the mouse

To select a chart element with your mouse, just click the element. The chart element appears with small circles at the corners.

Some chart elements are a bit tricky to select. To ensure that you select the chart element that you intended to select, view the Chart Element control, located in the Chart Tools Format Current Selection group of the Ribbon (see Figure 19.1).

When you move the mouse over a chart, a small chart tip displays the name of the chart element under the mouse pointer. When the mouse pointer is over a data point, the chart tip also displays the value of the data point.

If you find these chart tips annoying, you can turn them off. Choose File Options and click the Advanced tab in the Excel Options dialog box. Locate the Display section and clear either or both the Show Chart Element Names on Hover or the Show Data Point Values on Hover check boxes.

Some chart elements (such as a series, a legend, and data labels) consist of multiple items. For example, a chart series element is made up of individual data points. To select a particular data point, click twice: First click the series to select it and then click the specific element within the series (for example, a column or a line chart marker). Selecting the element enables you to apply formatting to only a particular data point in a series.

You may find that some chart elements are difficult to select with the mouse. If you rely on the mouse for selecting a chart element, you may have to click it several times before the desired element is actually selected. Fortunately, Excel provides other ways to select a chart element, and it's worth your while to be familiar with them. Keep reading to see how.

19.1.2. Selecting with the keyboard

When a chart is active, you can use the up-arrow and down-arrow navigation keys on your keyboard to cycle among the chart's elements. Again, keep your eye on the Chart Elements control to ensure that the selected chart element is what you think it is.

• When a chart series is selected: Use the left-arrow and right-arrow keys to select an individual item within the series.

• When a set of data labels is selected: You can select a specific data label by using the left-arrow or right-arrow key.

• When a legend is selected: Select individual elements within the legend by using the left-arrow or right-arrow keys.

19.1.3. Selecting with the Chart Element control

The Chart Element control is located in the Chart Tools Format Current Selection group and also in the Chart Tools Layout Current Selection group. This control displays the name of the currently selected chart element. It's a drop-down control, and you can also use it to select a particular element in the active chart (see Figure 19.2).

The Chart Element control also appears in the Mini toolbar, which is displayed when you right-click a chart element.

The Chart Element control enables you to select only the top-level elements in the chart. To select an individual data point within a series, for example, you need to select the series and then use the navigation keys (or your mouse) to select the desired data point.

Draft Mode for Charts If you create complex charts with lots of formatting, you may find that screen updating slows down. If so, that's a good time to turn on Draft mode. NOTE The Draft Mode charting option is new to Excel 2010. Select the chart, and choose Chart Tools Design Mode Draft. This command toggles Draft mode for the selected chart. This Ribbon button also has a drop-down list, which has commands to apply Draft mode to all charts. A Draft Mode indicator appears in the lower-right corner. Click this indicator to switch from Draft mode to Normal mode. When a chart is displayed in Draft mode, some formatting may be hidden. For example, dashed and dotted lines appear solid, shadows are hidden, gradients display as solid colors, and transparent elements are not transparent. When you edit a chart in Draft mode, you'll notice that some formatting commands appear to have no effect. For example, if you apply a shadow to a chart element, the shadow does not appear. However, if you set the chart to Normal mode, the formatting will appear. Therefore, I recommend formatting your charts using Normal mode, not Draft mode. In the unlikely event that you would like Draft mode to be the default for all charts, choose File Options, click the Advanced tab, locate the Charts section, and select the Insert Charts Using Draft Mode check box.

If you do a lot of work with charts, you may want to add the Chart Element control to your Quick Access toolbar. That way, it will always be visible regardless of which Ribbon tab is showing. To add the control to your Quick Access toolbar, right-click the down arrow in the control and choose Add to Quick Access Toolbar.

19.2. User Interface Choices for Modifying Chart Elements

You have three main ways of working with chart elements: the Format dialog box, the Ribbon, and the Mini toolbar.

19.2.1. Using the Format dialog box

When a chart element is selected, use the element's Format dialog box to format or set options for the element. Each chart element has a unique Format dialog box that contains controls specific to the element (although many Format dialog boxes have controls in common). To access the Format dialog box, use any of these methods:

• Double-click the chart element.

• Right-click the chart element and then choose Format xxxx from the shortcut menu (where xxxx is the name of the element).

• Select a chart element and then choose Chart Tools Format Current Selection Format Selection.

• Select a chart element and press Ctrl+1.

Any of these actions displays a tabbed Format dialog box from which you can make many changes to the selected chart element. For example, Figure 19.3 shows the dialog box that appears when a chart's legend is selected.

The Format dialog box is a modeless dialog box, so you can leave it open while you're working on a chart. If you select a different chart element, the Format dialog box changes to display the options appropriate for the new element.

19.2.2. Using the Ribbon

When a chart element is selected, you can also use the commands on the Ribbon to change some aspects of its formatting. For example, to change the color of the bars in a column chart, use the commands from the Chart Tools Format Shape Styles group. For some types of chart element formatting, you need to leave the Chart Tools tab. For example, to adjust font-related properties, use the commands from the Home Font Group.

The Ribbon controls do not comprise a comprehensive set of tools for chart elements. The Format dialog box usually presents options that aren't available on the Ribbon.

19.2.3. Using the Mini toolbar

When you right-click an element in a chart, Excel displays a shortcut menu and the Mini toolbar. The Mini toolbar contains commonly used formatting commands, plus a copy of the Chart Elements control (which you can use to select another chart element).

Commands that are inappropriate for the selected chart element are disabled in the Mini toolbar. For example, if you select an element that doesn't contain text, the text-related tools are disabled.

19.3. Modifying the Chart Area

The Chart Area is an object that contains all other elements in the chart. You can think of it as a chart's master background or container.

The only modifications that you can make to the Chart Area are cosmetic. You can change its fill color; outline; or effects, such as shadow and soft edges.

Note that if you set the Chart Area of an embedded chart to use No Fill, the underlying cells are visible. Figure 19.4 shows a chart that uses No Fill and No Outline in its Chart Area. The Plot Area, Legend, and Chart Title do use a fill color. Adding a shadow to these other elements make them appear to be floating on the worksheet.

The Chart Area element also controls all the fonts used in the chart. For example, if you want to change every font in the chart, you don't need to format each text element separately. Just select the Chart Area and then make the change from options of the Home Font group or by using the Mini toolbar.

Resetting Chart Element Formatting If you go overboard formatting a chart element, you can always reset it to its original state. Just select the element and choose Chart Tools Format Current Selection Reset to Match Style. Or, right-click the chart element and choose Reset to Match Style from the shortcut menu. To reset all formatting changes in the entire chart, select the Chart Area before you issue the Reset to Match Style command.

19.4. Modifying the Plot Area

The Plot Area is the part of the chart that contains the actual chart. More specifically, the Plot Area is a container for the chart series.

If you set the Shape Fill property to No Fill, the Plot Area will be transparent. Therefore, the fill color applied to the Chart Area will show through.

You can move and resize the Plot Area. Select the Plot Area and then drag a border to move it. To change the size of the Plot Area, drag one of the corner handles.

Different chart types vary in how they respond to changes in the Plot Area dimensions. For example, you can't change the relative dimensions of the Plot Area of a pie chart or a radar chart. The Plot Area of these charts is always square. With other chart types, though, you can change the aspect ratio of the Plot Area by changing either the height or the width.

Figure 19.5 shows a chart in which the Plot Area was resized to make room for a Shape that contains text.

In some cases, the size of the Plot Area changes automatically when you adjust other elements of your chart. For example, if you add a legend to a chart, the size of the Plot Area may be reduced to accommodate the legend.

Changing the size and position of the Plot Area can have a dramatic effect on the overall look of your chart. When you're fine-tuning a chart, you'll probably want to experiment with various sizes and positions for the Plot Area.

19.5. Working with Chart Titles

A chart can have several different types of titles:

• Chart title

• Category (X) axis title

• Value (Y) axis title

• Second category (X) axis title

• Second value (Y) axis title

• Depth axis title (for true 3-D charts)

The number of titles that you can use depends on the chart type. For example, a pie chart supports only a chart title because it has no axes.

To add a chart title, activate the chart and choose Chart Tools Layout Labels Chart Title. To add a title to one or more of the axes, choose Chart Tools Layout Labels Axis Titles. These controls are drop-down lists, and each has several options.

After you add a title, you can replace the default text and drag the titles to a different position. However, you can't change the size of a title by dragging. The only way to change the size of a title is to change the font size.

Adding Free-Floating Text to a Chart Text in a chart is not limited to titles. In fact, you can add free-floating text anywhere you want. To do so, activate the chart and choose Chart Tools Layout Insert Text Box. Click in the chart to create the text box and enter the text. You can resize the text box, move it, change its formatting, and so on. You can also add a Shape to the chart and then add text to the Shape (if the Shape is one that accepts text). Many people prefer to use a text box in place of a chart's "official" title elements. Resizing a title is not possible (except by changing its font size). If you use a text box, though, you can resize it by dragging its corners, change the text alignment, and even rotate it.

The chart title or any of the axis titles can also use a cell reference. For example, you can create a link so the chart always displays the text contained in cell A1 as its title. To create a link, select the title, type an equal sign (=), point to the cell, and press Enter. After you create the link, the Formula bar displays the cell reference when you select the title.

19.6. Working with a Legend

A chart's legend consists of text and keys that identify the data series in the chart. A key is a small graphic that corresponds to the chart's series (one key for each series).

To add a legend to your chart, choose Chart Tools Layout Labels Legend. This drop-down control contains several options for the legend placement. After you add a legend, you can drag it to move it anywhere you like.

If you move a legend from its default position, you may want to change the size of the Plot Area to fill in the gap left by the legend. Just select the Plot Area and drag a border to make it the desired size.

The quickest way to remove a legend is to select the legend and then press Delete.

You can select individual items within a legend and format them separately. For example, you may want to make the text bold to draw attention to a particular data series. To select an element in the legend, first select the legend and then click the desired element.

If you didn't include legend text when you originally selected the cells to create the chart, Excel displays Series 1, Series 2, and so on in the legend. To add series names, choose Chart Tools Design Data Select Data to display the Select Data Source dialog box (see Figure 19.6). Select the series name and click the Edit button. In the Edit Series dialog box, type the series name or enter a cell reference that contains the series name. Repeat for each series that needs naming.

In some cases, you may prefer to omit the legend and use callouts to identify the data series. Figure 19.7 shows a chart with no legend. Instead, it uses Shapes to identify each series. These Shapes are from the Callouts section of the Chart Tools Layout Insert Shapes Gallery.

Copying Chart Formatting You created a killer chart and spent hours customizing it. Now you need to create another one just like it, but with a different set of data. What are your options? You have several choices: Copy the formatting. Create your new chart with the default formatting. Then select your original chart and choose Home Clipboard Copy (or press Ctrl+C). Click your new chart and choose Home Clipboard Paste Paste Special. In the Paste Special dialog box, select the Formats option. Copy the chart; change the data sources. Press Ctrl while you click the original chart and drag. This creates an exact copy of your chart. Then choose Chart Tools Design Data Select Data. In the Select Data Source dialog box, specify the data for the new chart. Create a chart template. Select your chart and then choose Chart Tools Design Type Save as Template. Excel prompts you for a name. When you create your next chart, use this template as the chart type.

19.7. Working with Gridlines

Gridlines can help the viewer determine what the chart series represents numerically. Gridlines simply extend the tick marks on an axis. Some charts look better with gridlines; others appear more cluttered. Sometimes, horizontal gridlines alone are enough although XY charts often benefit from both horizontal and vertical gridlines.

To add or remove gridlines, choose Chart Tools Layout Axes Gridlines. This drop-down control contains options for all possible gridlines in the active chart.

To modify the color or thickness of a set of gridlines, click one of the gridlines and use the commands from the Chart Tools Format Shape Styles group.

If gridlines seem too overpowering, consider changing them to a lighter color or use one of the dashed options.

19.8. Modifying the Axes

Charts vary in the number of axes that they use. Pie and doughnut charts have no axes. All 2-D charts have two axes (three, if you use a secondary-value axis; four, if you use a secondary-category axis in an XY chart). True 3-D charts have three axes.

Excel gives you a great deal of control over these axes, via the Format Axis dialog box. The content of this dialog box varies depending on the type of axis selected.

19.8.1. Value axis

To change a value axis, right-click it and choose Format Axis. Figure 19.8 shows the Axis Options tab of the Format Axis dialog box. The other tabs in the dialog box deal with cosmetic formatting.

On the Axis Options tab, the four sets of option buttons at the top determine the scale of the axis (its minimum, maximum, and intervals). By default, Excel determines these values based on the numerical range of the data, and the settings are set to Auto. You can override Excel's choice and set any or all of them to Fixed and then enter your own values.

Adjusting the scale of a value axis can dramatically affect the chart's appearance. Manipulating the scale, in some cases, can present a false picture of the data. Figure 19.9 shows two line charts that depict the same data. The chart on the left uses Excel's default (Auto) axis scale values, which extend from 7,400 to 9,400. In the chart on the right, the Minimum scale value was set to 0, and the Maximum scale value was set to 10,000. The first chart makes the differences in the data seem more prominent. The second chart gives the impression that there is not much change over time.

The actual scale that you use depends on the situation. There are no hard-and-fast rules regarding setting scale values except that you shouldn't misrepresent data by manipulating the chart to prove a point that doesn't exist.

If you're preparing several charts that use similarly scaled data, keeping the scales the same is a good idea so that the charts can be compared more easily.

Another option in the Format Axis dialog box is Values in Reverse Order. The left chart in Figure 19.10 uses default axis settings. The right chart uses the Values in Reverse Order option, which reverses the scale's direction. Notice that the Category Axis is at the top. If you would prefer that it remain at the bottom of the chart, select the Maximum Axis Value option for the Horizontal Axis Crosses setting.

If the values to be plotted cover a very large numerical range, you may want to use a logarithmic scale for the value axis. A log scale is most often used for scientific applications. Figure 19.11 shows two charts. The left chart uses a standard scale, and the right chart uses a logarithmic scale.

If your chart uses very large numbers, you may want to change the Display Units settings. Figure 19.12 shows a chart that uses very large numbers. The chart on the right uses the Display Units as Millions settings, with the option to Show Display Units Labels on Chart.

The Major and Minor Tick Mark options control how the tick marks are displayed. Major tick marks are the axis tick marks that normally have labels next to them. Minor tick marks fall between the major tick marks.

Excel lets you position the axis labels at three different locations: Next to Axis, High, and Low. Each axis extends from −10 to +10. When you combine these settings with the Axis Crosses At option, you have a great deal of flexibility, as shown in Figure 19.13.

19.8.2. Category axis

Figure 19.14 shows the Axis Options tab of the Format Axis dialog box when a category axis is selected. Some options are the same as those for a value axis.

Excel chooses how to display category labels, but you can override its choice. Figure 19.15 shows a column chart with month labels. Because of the lengthy category labels, Excel displays the text at an angle. If you make the chart wider, the labels will then appear horizontally. You can also adjust the labels from the Alignment tab of the Format Axis dialog box.

In some cases, you really don't need every category label. You can adjust the Interval between Labels settings to skip some labels (and cause the text to display horizontally). Figure 19.16 shows such a chart; the Interval between Labels setting is 3.

When you create a chart, Excel recognizes whether your category axis contains date or time values. If so, it uses a time-based category axis. Figure 19.17 shows a simple example. Column A contains dates, and column B contains the values plotted in the column chart. The data consists of values for only 10 dates, yet Excel created the chart with 30 intervals on the category axis. It recognized that the category axis values were dates and created an equal-interval scale.

You can override Excel's decision to use a time-based category axis by choosing the Text Axis option for Axis Type. Figure 19.18 shows the chart after making this change. In this case, using a time-based category axis presents a truer picture of the data.

Don't Be Afraid to Experiment (But on a Copy) I'll let you in on a secret: The key to mastering charts in Excel is experimentation, otherwise known as trial and error. Excel's charting options can be overwhelming, even to experienced users. This book doesn't even pretend to cover all the charting features and options. Your job, as a potential charting guru, is to dig deep and try out the various options in your charts. With a bit of creativity, you can create original-looking charts. After you create a basic chart, make a copy of the chart for your experimentation. That way, if you mess it up, you can always revert to the original and start again. To make a copy of an embedded chart, click the chart and press Ctrl+C. Then activate a cell and press Ctrl+V. To make a copy of a chart sheet, press Ctrl while you click the sheet tab and then drag it to a new location among the other tabs.

19.9. Working with Data Series

Every chart consists of one or more data series. This data translates into chart columns, bars, lines, pie slices, and so on. This section discusses some common operations that involve a chart's data series.

When you select a data series in a chart, Excel does the following:

• Displays the series name in the Chart Elements control (located in the Chart Tools Layout Current Selection group and also in the Chart Tools Format Current Selection group)

• Displays the Series formula in the Formula bar

• Highlights the cells used for the selected series by outlining them in color

You can make changes to a data series by using options on the Ribbon or from the Format Data Series dialog box. This dialog box varies, depending on the type of data series you're working on (column, line, pie, and so on).

The easiest way to display the Format Data Series dialog box is to double-click the chart series. Be careful, however: If a data series is already selected, double-clicking brings up the Format Data Point dialog box. Changes that you make affect only one point in the data series. To edit the entire series, make sure that a chart element other than the data series is selected before you double-click the data series.

19.9.1. Deleting a data series

To delete a data series in a chart, select the data series and press Delete. The data series disappears from the chart. The data in the worksheet, of course, remains intact.

19.9.2. Adding a new data series to a chart

If you want to add another data series to an existing chart, re-create the chart and include the new data series. However, adding the data to the existing chart is usually easier, and your chart retains any customization that you've made.

Figure 19.19 shows a column chart that has two data series (Jan and Feb). The March figures just became available and were entered into the worksheet in row 4. Now the chart needs to be updated to include the new data series.

Excel provides two ways to add a new data series to a chart:

• Activate the chart and choose Chart Tools Design Data Select Data. In the Select Data Source dialog box, click the Add button, and Excel displays the Edit Series dialog box. Specify the Series Name (as a cell reference or text) and the range that contains the Series Values.

• Select the range to add and press Ctrl+C to copy it to the Clipboard. Then activate the chart and press Ctrl+V to paste the data into the chart.

If the chart was originally made from data in a table (created via Insert Tables Table), the chart is updated automatically when you add new data to the table. If you have a chart that is updated frequently with new data, you can save time and effort by creating the chart from data in a table.

19.9.3. Changing data used by a series

You may find that you need to modify the range that defines a data series. For example, say you need to add new data points or remove old ones from the data set. The following sections describe several ways to change the range used by a data series.

19.9.3.1. Changing the data range by dragging the range outline

If you have an embedded chart, the easiest way to change the data range for a data series is to drag the range outline. When you select a series in a chart, Excel outlines the data range used by that series (see Figure 19.20). You can drag the small dot in the lower-right corner of the range outline to extend or contract the data series.

You can also click and drag one of the sides of the outline to move the outline to a different range of cells.

In some cases, you'll also need to adjust the range that contains the category labels as well. The labels are also outlined, and you can drag the outline to expand or contract the range of labels used in the chart.

If your chart is on a chart sheet, you need to use one of the two methods described next.

19.9.3.2. Using the Edit Series dialog box

Another way to update the chart to reflect a different data range is to use the Edit Series dialog box. A quick way to display this dialog box is to right-click the series in the chart and then choose Select Data from the shortcut menu. Excel displays the Select Source Data dialog box. Select the data series in the list, and click Edit to display the Edit Series dialog box, shown in Figure 19.21.

You can change the entire data range used by the chart by adjusting the range references in the Chart Data Range field. Or, select a Series from the list and click Edit to modify the selected series.

19.9.3.3. Editing the Series formula

Every data series in a chart has an associated Series formula, which appears in the Formula bar when you select a data series in a chart. If you understand how a Series formula is constructed, you can edit the range references in the Series formula directly to change the data used by the chart.

A Series formula has the following syntax:

=SERIES(series_name, category_labels, values, order, sizes)

The arguments that you can use in the Series formula include

• series_name: (Optional). A reference to the cell that contains the series name used in the legend. If the chart has only one series, the name argument is used as the title. This argument can also consist of text in quotation marks. If omitted, Excel creates a default series name (for example, Series 1).

• category_labels: (Optional). A reference to the range that contains the labels for the category axis. If omitted, Excel uses consecutive integers beginning with 1. For XY charts, this argument specifies the X values. A noncontiguous range reference is also valid. The ranges' addresses are separated by commas and enclosed in parentheses. The argument could also consist of an array of comma-separated values (or text in quotation marks) enclosed in curly brackets.

• values: (Required). A reference to the range that contains the values for the series. For XY charts, this argument specifies the Y values. A noncontiguous range reference is also valid. The ranges addresses are separated by a comma and enclosed in parentheses. The argument could also consist of an array of comma-separated values enclosed in curly brackets.

• order: (Required). An integer that specifies the plotting order of the series. This argument is relevant only if the chart has more than one series. Using a reference to a cell is not allowed.

• sizes: (Only for bubble charts). A reference to the range that contains the values for the size of the bubbles in a bubble chart. A noncontiguous range reference is also valid. The ranges addresses are separated by commas and enclosed in parentheses. The argument can also consist of an array of values enclosed in curly brackets.

Range references in a Series formula are always absolute (contain two dollar signs), and they always include the sheet name. For example

=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$7,1)

You can substitute range names for the range references. If you do so, Excel changes the reference in the Series formula to include the workbook name. For example if you use a range named MyData (in a workbook named budget.xlsx), the Series formula looks like this

=SERIES(Sheet1!$B$1,,budget.xlsx!MyData,1)

19.9.4. Displaying data labels in a chart

Sometimes, you may want your chart to display the actual numerical values for each data point. You specify data labels by choosing Chart Tools Layout Labels Data Labels. This drop-down control contains several data label positioning options.

Figure 19.22 shows three minimalist charts with data labels.

To change the type of information that appears in data labels, select the data labels in the chart and press Ctrl+F1. Then use the Label Options tab of the Format Data Labels dialog box to customize the data labels. For example, you can include the series name and the category name along with the value.

The data labels are linked to the worksheet, so if your data changes, the labels also change. If you want to override the data label with other text, select the label and enter the new text.

Often, the data labels aren't positioned properly — for example, a label may be obscured by another data point. If you select an individual data label, you can drag the label to a better location. To select an individual data label, click once to select them all and then click the single data label.

As you work with data labels, you discover that the Excel data labels feature leaves a bit to be desired. For example, it would be nice to be able to specify an arbitrary range of text to be used for the data labels. This capability would be particularly useful in XY charts in which you want to identify each data point with a particular text item. Despite what must amount to thousands of requests, Microsoft still hasn't added this feature to Excel. You need to add data labels and then manually edit each label.

19.9.5. Handling missing data

Sometimes, data that you're charting may be missing one or more data points. As shown in Figure 19.23, Excel offers three ways to handle the missing data:

• Gaps: Missing data is simply ignored, and the data series will have a gap. This is the default.

• Zero: Missing data is treated as zero.

• Connect Data Points with Line: Missing data is interpolated, calculated by using data on either side of the missing point(s). This option is available for line charts, area charts, and XY charts only.

To specify how to deal with missing data for a chart, choose Chart Tools Design Data Select Data. In the Select Data Source dialog box, click the Hidden and Empty Cells button. Excel displays its Hidden and Empty Cell Settings dialog box. Make your choice in the dialog box. The option that you choose applies to the entire chart, and you can't set a different option for different series in the same chart.

Normally, a chart doesn't display data that's in a hidden row or column. You can use the Hidden and Empty Cell Settings dialog box to force a chart to use hidden data, though.

19.9.6. Adding error bars

Some chart types support error bars. Error bars often are used to indicate "plus or minus" information that reflects uncertainty in the data. Error bars are appropriate for area, bar, column, line, and XY charts only.

To add error bars, select a data series and then choose Chart Tools Layout Analysis Error Bars. This drop-down control has several options. You can then fine-tune the error bar settings from the Format Error Bars dialog box. The types of error bars are

• Fixed value: The error bars are fixed by an amount that you specify.

• Percentage: The error bars are a percentage of each value.

• Standard Deviation(s): The error bars are in the number of standard deviation units that you specify. (Excel calculates the standard deviation of the data series.)

• Standard Error: The error bars are one standard error unit. (Excel calculates the standard error of the data series.)

• Custom: You set the error bar units for the upper or lower error bars. You can enter either a value or a range reference that holds the error values that you want to plot as error bars.

The chart shown in Figure 19.24 displays error bars based on percentage.

A data series in an XY chart can have error bars for both the X values and Y values.

19.9.7. Adding a trendline

When you're plotting data over time, you may want to plot a trendline that describes the data. A trendline points out general trends in your data. In some cases, you can forecast future data with trendlines. A single series can have more than one trendline.

To add a trendline, select the data series and choose Chart Tools Layout Analysis Trendline. This drop-down control contains options for the type of trendline. The type of trendline that you choose depends on your data. Linear trends are most common, but some data can be described more effectively with another type.

Figure 19.25 shows an XY chart with a linear trendline and the (optional) equation for the trendline. The trendline describes the "best fit" of the height and weight data.

For more control over a trendline, right-click it and choose Format Trendline to open the Format Trendline dialog box. One option, Moving Average, is useful for smoothing out data that has a lot of variation (that is, "noisy" data).

The Moving Average option enables you to specify the number of data points to include in each average. For example, if you select 5, Excel averages every five data points. Figure 19.26 shows a chart that uses a moving average trendline.

19.9.8. Modifying 3-D charts

3-D charts have a few additional elements that you can customize. For example, most 3-D charts have a floor and walls, and true 3-D charts also have an additional axis. You can select these chart elements and format them to your liking using the Format dialog box.

One area in which Excel 3-D charts differ from 2-D charts is in the perspective — or viewpoint — from which you see the chart. In some cases, the data may be viewed better if you change the order of the series.

Figure 19.27 shows two versions of 3-D column chart with two data series. The left chart is the original, and the right chart shows the effect of changing the series order. To change the series order, choose Chart Tools Design Data Select Data. In the Select Data Source dialog box, select a series and use the arrow buttons to change its order.

Fortunately, Excel allows you to change the viewing angle of 3-D charts. Doing so may reveal portions of the chart that are otherwise hidden. To rotate a 3-D chart, choose Chart Tools Layout Background 3-D Rotation, which displays the 3-D Rotation tab of the Format Chart Area dialog box. You can make your rotations and perspective changes by clicking the appropriate controls.

Figure 19.28 shows four different views of the same chart. As you can see, you can accidentally distort the chart to make it virtually worthless in terms of visualizing information. If accuracy of presentation is important, a 3-D chart is hardly ever the best choice.

19.9.9. Creating combination charts

A combination chart is a single chart that consists of series that use different chart types. A combination chart may also include a second value axis. For example, you may have a chart that shows both columns and lines, with two value axes. The value axis for the columns is on the left, and the value axis for the line is on the right. A combination chart requires at least two data series.

Creating a combination chart involves changing one or more of the data series to a different chart type. Select the data series to change and then choose Chart Tools Design Type Change Chart Type. In the Change Chart Type dialog box, select the chart type that you want to apply to the selected series. Using a second Value Axis is optional.

Figure 19.29 shows a column chart with two data series. The values for the Precipitation series are very low — so low that they're barely visible on the Value Axis scale. This is a good candidate for a combination chart.

The following steps describe how to convert this chart into a combination chart (column and line) that uses a second Value Axis.

Figure 19.30 shows the modified chart. The Precipitation data appears as a line, and it uses the Value Axis on the right.

Figure 19.31 demonstrates just how far you can go with a combination chart. This chart combines five different chart types: Pie, Area, Column, Line, and XY. I can't think of any situation that would warrant such a chart, but it's an interesting demo.

19.9.10. Displaying a data table

In some cases, you may want to display a data table, which displays the chart's data in tabular form, directly in the chart.

To add a data table to a chart, choose Chart Tools Layout Labels Data Table. This control is a drop-down list with a few options to choose from. For more options, use the Format Data Table dialog box. Figure 19.32 shows a combination chart that includes a data table.

Using a data table is probably best suited for charts on chart sheets. If you need to show the data used in an embedded chart, you can do so using data in cells, which provide you with a lot more flexibility in terms of formatting.

19.10. Creating Chart Templates

This section describes how to create custom chart templates. A template includes customized chart formatting and settings. When you create a new chart, you can choose to use your template rather than a built-in chart type.

If you find that you are continually customizing your charts in the same way, you can probably save some time by creating a template. Or, if you create lots of combination charts, you can create a combination chart template and avoid making the manual adjustments required for a combination chart.

To create a chart template

To create a chart based on a template

19.11. Learning Some Chart-Making Tricks

This section describes some interesting (and perhaps useful) chart-making tricks. Some of these tricks use little-known features, and several tricks enable you to make charts that you may have considered impossible to create.

19.11.1. Creating picture charts

Excel makes it easy to incorporate a pattern, texture, or graphics file for elements in your chart. Figure 19.33 shows a chart that uses a photo as the background for a chart's Chart Area element.

To display an image in a chart element, use the Fill tab in the element's Format dialog box. Select the Picture or Texture Fill option and then click the button that corresponds to the image source (File, Clipboard, or ClipArt). If you use the Clipboard button, make sure that you copied your image first. The other two options prompt you for the image.

Figure 19.34 shows two more examples: a pie chart that uses Office clipart as its fill; and a column chart that uses a Shape, which was inserted on a worksheet and then copied to the Clipboard.

Using images in a chart offers unlimited potential for creativity. The key, of course, is to resist the temptation to go overboard. A chart's primary goal is to convey information, not to impress the viewer with your artistic skills.

Using images, especially photos, in charts can dramatically increase the size of your workbooks.

19.11.2. Creating a thermometer chart

You're probably familiar with a "thermometer" type display that shows the percentage of a task that has been completed. Creating such a display in Excel is very easy. The trick involves creating a chart that uses a single cell (which holds a percentage value) as a data series.

Figure 19.35 shows a worksheet set up to track daily progress toward a goal: 1,000 new customers in a 15-day period. Cell B18 contains the goal value, and cell B19 contains a simple formula that calculates the sum. Cell B21 contains a formula that calculates the percent of goal:

=B19/B18

As you enter new data in column B, the formulas display the current results.

To make the thermometer chart, select cell B21 and create a column chart from that single cell. Notice the blank cell above cell B21. Without this blank cell, Excel uses the entire data block for the chart, not just the single cell. Because B21 is isolated from the other data, only the single cell is used.

Other changes required are to

• Select the horizontal category axis and press Delete to remove the category axis from the chart.

• Remove the legend.

• Add a text box, linked to cell B21 to display the percent accomplished.

• In the Format Data Series dialog box (Series Options tab), set the Gap width to 0, which makes the column occupy the entire width of the plot area.

• Select the Value Axis and display the Format Value Axis dialog box. In the Axis Options tab, set the Minimum to 0 and the Maximum to 1.

Make any other cosmetic adjustments to get the look you desire.

19.11.3. Creating a gauge chart

Figure 19.36 shows another chart based on a single cell. It's a pie chart set up to resemble a gauge. Although this chart displays only one value (entered in cell B1), it actually uses three data points (in A4:A6).

One slice of the pie — the slice at the bottom — always consists of 50 percent. I rotated the pie so that the 50 percent slice was at the bottom. Then I hid that slice by specifying No Fill and No Border for the data point.

The other two slices are apportioned based on the value in cell B1. The formula in cell A4 is

=MIN(B1,100%)/2

This formula uses the MIN function to display the smaller of two values: either the value in cell B1 or 100 percent. It then divides this value by 2 because only the top half of the pie is relevant. Using the MIN function prevents the chart from displaying more than 100 percent.

The formula in cell A5 simply calculates the remaining part of the pie — the part to the right of the gauge's "needle":

=50%-A4

The chart's title was moved below the half-pie. The chart also contains a text box, linked to cell B1, that displays the percent completed.

19.11.4. Displaying conditional colors in a column chart

You may have noticed the Vary Colors by Point option on the Fill tab of the Format Data Series dialog box. This option simply uses more colors for the data series. Unfortunately, the colors aren't related to the values of the data series.

This section describes how to create a column chart in which the color of each column depends on the value that it's displaying. Figure 19.37 shows such a chart (more impressive when you see it in color). The data used to create the chart is in range A1:F14.

This chart displays four data series, but some data is missing for each series. The data for the chart is entered in column B. Formulas in columns C:F determine which series the number belongs to by referencing the bins in Row 1. For example, the formula in cell C3 is

=IF(B3<=$C$1,B3,"")

If the value in column B is less than the value in cell C1, the value goes in this column. The formulas are set up such that a value in column B goes into only one column in the row.

The formula in cell D3 is a bit more complex because it must determine whether cell C3 is greater than the value in cell C1 and less than or equal to the value in cell D1:

=IF(AND($B3>C$1,$B3<=D$1),$B3,"")

The four data series are overlaid on top of each other in the chart. The trick involves setting the Series Overlap value to a large number. This setting determines the spacing between the series. Use the Series Options tab of the Format Data Series dialog box to adjust this setting.

19.11.5. Creating a comparative histogram

With a bit of creativity, you can create charts that you may have considered impossible. For example, Figure 19.38 shows a chart sometimes referred to as a comparative histogram chart. Such charts often display population data.

Here's how to create the chart:

19.11.6. Creating a Gantt chart

A Gantt chart is a horizontal bar chart often used in project management applications. Although Excel doesn't support Gantt charts per se, creating a simple Gantt chart is possible. The key is getting your data set up properly.

Figure 19.39 shows a Gantt chart that depicts the schedule for a project, which is in the range A2:C13. The horizontal axis represents the total time span of the project, and each bar represents a project task. The viewer can quickly see the duration for each task and identify overlapping tasks.

Column A contains the task name, column B contains the corresponding start date, and column C contains the duration of the task, in days.

Follow these steps to create this chart:

19.11.7. Plotting mathematical functions with one variable

An XY chart is useful for plotting various mathematical and trigonometric functions. For example, Figure 19.40 shows a plot of the SIN function. The charts plots y for values of x (expressed in radians) from −5 to +5 in increments of 0.5. Each pair of x and y values appears as a data point in the chart, and the points connect with a line.

The function is expressed as

y = SIN(x)

The corresponding formula in cell B2 (which is copied to the cells below) is

=SIN(A2)

19.11.8. Plotting mathematical functions with two variables

The preceding section describes how to plot functions that use a single variable (x). You also can plot functions that use two variables. For example, the following function calculates a value of z for various values of two variables (x and y):

z = SIN(x)*COS(y)

Figure 19.41 shows a surface chart that plots the value of z for 21 x values ranging from 1 to 5, and for 21 y values ranging from 1 to 5. Both x and y use an increment of 0.2.

The formula in cell B2, copied across and down, is

=SIN($A2*COS(B$1))