Editing and Formatting Chart Elements

Although the default chart settings are often good enough to get you started, Excel offers a broad range of chart options that give you complete control over the look of the chart and plot area. The easiest way to change many chart options is to rerun the Chart Wizard. The wizard's dialog boxes pick up your current chart settings and let you change chart types, edit the source data, apply new formatting, or change the location of your chart.

To adjust individual chart objects, first select the object (the chart title or the category axis, for example), and then change its properties.

Excel also lets you add an enormous number of attention-getting elements in the drawing layer on top of a chart. For example, you can add text boxes to data markers to explain anomalies in your data or call attention to key numbers. If you select the chart or plot area and start typing, Excel begins creating a text box immediately. After you've added the desired text, you can then move it anywhere on the chart and reformat it to your liking.

→ To add, edit, and format text boxes, AutoShapes, callouts, and other drawn elements in charts, use the Office drawing tools; see "Using Office Drawing Tools".

Adjusting Chart Options

Step 3 of the Chart Wizard displays a tabbed dialog box that lets you adjust various chart options. After you create a chart, you can display the same dialog box by right-clicking the chart area or plot area and selecting Chart Options from the shortcut menu. The following six categories of options might be available, depending on the chart type.

Titles

Create titles that appear on the top of the chart or next to any axis. If the nature of data along each axis isn't immediately apparent, you can add explanatory text here, too. Click this tab and enter the text for the chart title and any available axes. In both locations, titles are nothing more than text boxes. The default font size for titles and other text objects on a chart is 10 point. Typically, that results in chart titles that are too small and legends that are too large. Use the Font tab on the Format dialog box to adjust the size of each object.

Axes

Check Automatic to allow Excel to format and display the axes that are appropriate for the chart type you've chosen. Normally, Excel is capable of analyzing the data type and using the correct settings. If your category axis includes dates, the Format Axis dialog box allows you to set the options shown in Figure 24.17. Adjust the Minimum and Maximum settings if you want to restrict the charted portion of your data; for example, in a list of daily sales results that encompasses several years, you might want to see just a few months'results. Change the Base Unit option to chart time data using a different scale. For example, in a list that includes data for many days, select Month(s) or Year(s) to let Excel group the data before plotting.

Use the Category and Time-scale options to solve a particularly annoying charting problem: If you select a time series that skips some dates, Excel might leave gaps in the category axis. If you construct a stock chart using daily high, low, and closing prices, for instance, your time series will be missing values for Saturdays, Sundays, and holidays, when the market is closed. Unfortunately, Excel insists on adding phantom markers for those days, messing up the smooth line you expect to see.

The solution is to convert the Time-scale axis to a Category axis. Right-click the chart axis and select Chart Options. Click the Axes tab and select the Category option under the primary Category (X) Axis. When you close the dialog box, Excel changes the display of data to a simple series, with no gaps. If you open the Format Axis dialog box, you'll see a different set of options, as shown in Figure 24.18.

This dialog box also lets you control the placement of tick marks, which are the small lines that indicate where each item on the category axis is plotted.

Gridlines

Gridlines help readers see where data points cross category or value axes. You can set major and minor gridlines for each axis. Normally, Excel does a decent job of setting intelligent defaults, but you often can clean up a chart and make it easier to read by adjusting these settings. In general, you should try to use as few gridlines as you can get away with. Watch the Preview window to see the effect as you add or remove gridlines.

Legend

A chart legend identifies each data marker according to its color or pattern on a chart. Options on this tab let you move or reformat the legend. If you don't need to show a legend (perhaps because you want to label each column or pie slice individually), uncheck the Show Legend box. The Placement options control where the legend first appears within the chart: Bottom, Corner, Top, Right, or Left. You can drag to position it more precisely later.

Data Labels

Use data labels when you want to display charted worksheet values, category labels, or percentages next to each point in a data series. Check the Legend Key Next to Label box to add a color-coded key at the beginning of each label. The options in this dialog box control the placement and appearance of data labels for every data series. However, if you want to add labels for just one series, or even a single point, you can do so. Skip the Chart Options dialog box and instead open the Format dialog box for the series or point you want to label. Check the appropriate option on the Data Labels dialog box for that item.

Data Table

Display a worksheet-style table directly in your chart to show the plotted worksheet data alongside the chart itself. Each row in the data table represents a data series. If your chart includes a relatively small amount of data, a data table can make an effective addition, as the example in Figure 24.19 shows.

Changing Number Formats

Use the right-click shortcut menus to change the number format of any item on a chart; to adjust the value axis, for example, right-click and select Format Axis. Click the Number tab in the Format Axis dialog box, and then select a format from the Category list. You can select a built-in number format or create a custom format, just as you can when formatting worksheet data. Click OK to apply the new format to your chart data.

→ To learn more about altering Excel's number formatting, see "Setting Number Formats".

Normally, numbers that appear in Excel charts use the same format as the source data in the worksheet to which they're linked. If you change the format of the numbers in the chart, you break the link to the format in the worksheet. Under many circumstances you'll want to do exactly that—for example, if numbers in your worksheet use the Currency format with two decimal places, but you don't want to see a dollar sign or decimals in your chart. To reestablish the link so the data on the chart uses the same number format as the data on the worksheet, select the chart object (for instance, the Value axis), select its Format option, click the Number tab, and check the Linked to Source box.

Changing Text Formats

You can change the appearance of any text item on a chart. As with worksheet cells, Excel lets you change fonts as well as font sizes and character attributes. You can choose different colors for the text and its background. To keep labels from running into one another on any axis, rotate text to an angle.

To adjust font options for all text in your chart, right-click the chart area and select Format Chart Area. Click the Font tab of the resulting dialog box and adjust formatting as necessary. This dialog box is a great way to apply the same font to all text in your chart, but avoid the temptation to choose a standard size as well. In most cases, you'll want to specify different font sizes for different items, such as the chart title, axes, and legend.

To change text formatting for any text object on the chart, right-click the object and select its Format option. Click the Font tab and adjust options as desired.

Are the category axis labels crowding the axis itself? Use the Offset box to specify the distance between the axis labels and the axis itself; the higher the number, the more distance between the two points.

Adding Background Colors, Textures, and Pictures

The default background for charts is plain white, but you can add background colors, textures, pictures, and gradient fills to an entire chart, to just the plot area, or to individual items such as data markers. In 3D charts, you also can add images to the walls and the floor.

These features use the Drawing tools shared by all Office programs. If you've used Word to design a Web page or created a presentation with PowerPoint, you can use the same backgrounds in Excel charts as in those files and then paste the charts into your Web page or presentation with confidence that they'll match the existing design.

→ To learn how to change background colors in any Office document, see "Changing Background Colors and Line Formats".

→ The Office Drawing tools enable you to easily add interesting visual effects; see "Adding Shadows and 3D Effects" .

Changing the Scale and Spacing of Axes

To make a chart easier to read, you might also want to adjust the scale on the value axis. Normally, the values on this axis start with 0 and extend to a number past the highest number in your data series. You might want to change the scale to start at a higher number, so you can more easily see the difference between data points. You can also adjust the display of large numbers:

Changing the Data Source for an Existing Chart

How do you add, edit, or remove data series, category names, and axis labels when you've already created the chart? Use one of the following three techniques:

• If the data source is simple and straightforward, with easy-to-identify headings for categories and values, right-click the chart area or plot area and select Source Data. Click the Data Range tab of the Source Data dialog box and select the new data range. If necessary, specify whether the series is in rows or columns. Click OK to apply the changes to the current chart.

• To add, remove, or change an individual data series or the range that defines labels or names, click the Series tab on the Data Source dialog box and adjust the options there. For example, if your current chart includes five data series, one for each year from 1997 through 2001, you might want to remove the first two years and add the years 2002 and 2003 to bring the chart up to date. Select the 1997 entry from the Series list and click Remove, and then do the same for the 1998 series. Note that the category name and value labels adjust automatically when you use this option.

• To add or remove a single series from a chart embedded on a worksheet, select the data range that contains the headings and values you want to plot and drop it directly on the chart. Excel adds the data to the plot area, complete with new category labels and legend items, if necessary.

When you select a data series on an embedded chart, the Range Finder displays a colored line around the corresponding range within the data source; the Range Finder also adds a border around the value axis labels and category labels, using different colors for each. Drag the selection by using the rectangular handle in the lower-right corner of each selection to extend or move the data range for each series. On a chart where the data source consists of a single contiguous range, selecting the chart area causes the Range Finder to highlight all the data series in one color, the value axis labels in another color, and category names in still another color.

When using the Range Finder with charts, you should be aware of the following limitations:

• The Range Finder works only with a chart object that is embedded on the same worksheet as the data. It does not work with charts on chart sheets.

• You can drag to expand the data range or a given series to include new data in either direction; however, this technique works only for contiguous series. If any series consists of a noncontiguous range, you must use the wizard or the Data Source dialog box to select the data.

• When you click an individual data point, the Range Finder highlights the series that contains that point.

Advanced Chart Options

Excel's advanced chart options let you add details that help you spot trends more easily. For example, in a line chart that plots daily closing stock prices over time, you can add a trendline and a moving average that smooth out some of the peaks and valleys in the data. You can do the same with a column chart to show a smooth trend over time. Select the series, right-click, and select Add Trendline. For charts that project data, you can also add error bars that define the upper- and lower-error limits of your projections by using standard statistical measures. You'll find these options on the Error Bars tab of the Format Data Series dialog box.