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

Figure 20.1. The Chart Element control displays the name of the selected chart element. In this example, the Vertical (Value) Axis is selected.

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.

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 only to 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.

When a chart is active, you can use the up-arrow and down-arrow keys on your keyboard to cycle among the chart’s elements. Again, keep your eye on the Chart Elements control to ensure that the chart element that’s selected 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. Similarly, when a set of data labels is selected, you can select a specific data label by using the left-arrow or right-arrow key. And when a legend is selected, you can select individual elements within the legend by using the left-arrow or right-arrow keys.

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

Figure 20.2. Using the Chart Element drop-down control to select a chart element.

This control lists 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 left and right arrow keys (or your mouse) to select the desired data point.

When a chart element is selected, you can access 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 either of these methods:

Any of these methods displays a tabbed Format dialog box that enables you to make many changes to the selected chart element. For example, Figure 20.3 shows the dialog box that appears when a chart’s legend is selected.

Figure 20.3. Use the Format dialog box to set the properties of a selected chart element—in this case, the chart’s legend.

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 in 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 in the Home Font Group.

The Ribbon controls do not comprise a comprehensive set of tools for chart elements. The Format dialog box presents many additional options. But then again, the Format dialog box is missing some formatting options. For example, if you want to apply Glow or Soft Edges formatting, you need to use the Ribbon commands.

Figure 20.8 shows the Axis Options tab of the Format Axis dialog box when a Value Axis is selected. The other tabs in the dialog box deal with cosmetic formatting.

Figure 20.8. These options are available for a Value Axis.

In the Axis Options tab, the four sets of option buttons at the top determine the scale of the axis (it’s 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 20.9 shows two line charts,, which depict the same data. The top chart uses Excel’s default axis scale values, which extend from 8,000 to 9,200. In the bottom chart, the Minimum scale value was set to 0, and the Maximum scale value was set to 10,000. The top chart makes the differences in the data seem more prominent. The lower chart gives the impression that there is not much change over time.

Figure 20.9. These two charts show the same data, but use a different Value Axis scales.

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.

Another option in the Format Axis dialog box is Values In Reverse Order. The top chart in Figure 20.10 uses default axis settings. The bottom 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.

Figure 20.10. The bottom chart uses the Values In Reverse Order option.

If the values to be plotted cover a very large range, you may want to use a logarithmic scale for the Value Axis. A log scale is most often used for scientific applications. Figure 20.11 shows two charts. The top chart uses a standard scale, and the bottom chart uses a logarithmic scale.

Figure 20.11. These charts display the same data, but the lower chart uses a logarithmic scale.

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

Figure 20.12. The lower chart uses display units of millions.

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 are 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 20.13.

Figure 20.13. Various ways to display axis labels and crossing points.

Figure 20.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.

Figure 20.14. These options are available for a Category Axis.

Excel chooses how to display category labels, but you can override its choice. Figure 20.15 shows a column chart with month labels. Excel displays the labels at an angle. If you make the chart wider, the labels will then appear horizontally.

Figure 20.15. Excel determines how to display Category Axis labels.

You can adjust the Interval Between Labels settings to skip some labels and cause the text to display horizontally. Figure 20.16 shows the chart with an Interval Between Labels setting of 3.

Figure 20.16. Changing the Interval Between Labels makes the labels display horizontally.

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 20.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.

Figure 20.17. Excel recognizes dates and creates a time-based category axis.

You can override Excel’s decision to use a time-based category axis by choosing the Text Axis option for Axis Type. Using a time-based category axis presents a truer picture of the data.

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

In some situations, you may need to add another data series to an existing chart. You can re-create the chart and include the new data series, but adding the data to the existing chart is usually easier, and your chart retains any customization that you’ve made.

Figure 20.19 shows a column chart that has two data series (Jan and Feb). The March figures just became available, and now the chart needs to be updated to include the new data.

Figure 20.18. This chart needs a new data series.

Figure 20.19. Changing a chart’s data series by dragging the range outline.

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

You may find that you need to modify the range that defines a data series. For example, you may 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.

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 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 20.20

Figure 20.20. The Edit Series dialog box.

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.

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

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

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

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 options.

Figure 20.21 shows a chart with data labels.

Figure 20.21. This chart uses data labels.

Use 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.

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.

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

Figure 20.22. Three options for dealing with missing data.

To specify how to deal with missing data for a chart, choose Chart Tools Design Data Select Data. In the Select Data Source, 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.

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 only for area, bar, column, line, and XY charts.

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 fine-tune the error bar settings using the Format Error Bars dialog box. The types of error bars are

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

Figure 20.23. This line chart series displays error bars based on percentage.

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 20.24 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.

Figure 20.24. This linear trendline forecasts sales for three additional time periods.

For more control over trendlines, use 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 20.25 shows a chart that uses a moving average trendline.

Figure 20.25. The dashed line displays a 10-interval moving average.

3-D charts have a few additional elements that you can customize. For example, most 3-D charts have a floor and walls, and the 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 3-D charts differ from Excel’s 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 20.26 shows two versions of 3-D column chart with two data series. The top chart is the original, and the bottom 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.

Figure 20.26. A 3-D column chart, before and after changing the series 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 20.27 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.

Figure 20.27. Four different views of the same 3-D column chart.

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 simply 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 20.28 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.

Figure 20.28. The Precipitation series is barely visible.

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

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

Figure 20.29. The Precipitation series is now visible.

Figure 20.30 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.

Figure 20.30. A five-way combination chart.

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 20.31 shows a chart with a data table.

Figure 20.31. This combination chart includes a data table that displays the values of the data points.

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.

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

Figure 20.32. The Chart Area contains a photo.

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’ve copied your image first. The other two options prompt you for the image.

Figure 20.33 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.

Figure 20.33. The top chart uses clipart, while the bottom chart uses a Shape that was 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.

You’re probably familiar with a “thermometer” type display that shows the percentage of a task that’s 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 20.34 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

Figure 20.34. This chart displays progress toward a goal.

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:

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

Figure 20.35 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).

Figure 20.35. This chart resembles a speedometer gauge and displays a value between 0 and 100 percent.

One slice of the pie—the slice at the bottom—always consists of 50 percent, and that slice is hidden. (The slice uses No Fill and No Outline.) The other two slices are apportioned based on the value in cell B1. The formula in cell 44 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 cell B1, that displays the percent completed.

You may have noticed that the Fill tab of the Format Data Series dialog box has an option labeled Vary Colors By Point. 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 20.36 shows such a chart (it’s more impressive when you see it in color). The data used to create the chart is in range A1:F14.

Figure 20.36. The color of the column depends varies with the value.

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, then 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.

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

Figure 20.37. A comparative histogram.

Here’s how to create the 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 fairly easy. The key is getting your data set up properly.

Figure 20.38 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:

An XY chart is useful for plotting various mathematical and trigonometric functions. For example, Figure 20.39 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.

Figure 20.39. This chart plots the SIN(x).

The function is expressed as:

y = SIN(x)

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

=SIN(A2)

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 20.40 shows a surface chart that plots the value of z for 21 x values ranging from 1 to 4, and for 21 y values ranging from 1 to 4. Both x and y use an increment of 0.15.

Figure 20.40. Using a surface chart to plot a function with two variables.

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

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