Chapter 10: Charts and Drawing Objects (Shapes)

Charts constitute the central feature of many Excel applications. This chapter gives a brief overview of the charts supported by Excel and also shows how you can create and print out your own charts under program control. A lengthy example on the subject of data recording demonstrates various programming techniques.

A further topic of this chapter is that of drawing objects ( Shapes ), available since Excel 97, with which both charts and ordinary worksheets can be ornamented.

10.1 Charts

Fear not! You are not about to be subjected to an extensive introduction to the use of charts. This topic is exhaustively (in the literal sense of the word) dealt with in countless books on Excel. The goal of this section is rather to describe, without much concern about the details of how they are used, the possibilities for designing charts, and to name the various elements of charts and explain their functions. This information will provide you with the requisite knowledge for entering the world of programming charts, a world swarming with various ChartXxx objects.

Fundamentals

Chart Sheets Versus Charts Embedded in Worksheets

In Excel you can either embed charts in worksheets or present them in their own chart sheets. The first variant has the advantage that the chart can be printed out with its associated data. Furthermore, very small charts can be created that take up only part of a page.

The Chart Wizard

Usually, the path to a new chart goes by way of the chart wizard. This wizard is automatically summoned when you create a new chart (with InsertChart or click on the chart wizard tool.)

In the first step of the chart wizard you select the desired chart type. In the second step you choose the data range. Here there is no problem with indicating a range of cells that is the union of other cell ranges. In further steps you can determine various options for the format of the chart. The chart wizard can also be called up to help with preexisting charts if you wish to change certain formatting details.

Further Processing of Charts

Charts that have been created with the chart wizard frequently do not quite meet your requirements. Therefore, the fine details of layout often begin after the chart wizard has been terminated .

In order for you to be able to edit the chart, you have to activate it with a mouse click. As soon as the chart is active, you can click on most of the chart elements within the region of the chart: the legend, the axes, individual data series (which are represented in the form of lines, bars, etc.), the background of the chart, and so on. For each of these chart elements there exists a pop-up menu that usually offers an extensive array of formatting options. You can access the most important setting dialogs with a double click on the corresponding chart element.

If you are working with charts for the first time, you will often encounter the problem that you do not know which element to click on to carry out a specific change. You have two alternatives: Suffering through the user 's guide and experimentation.

Chart Types

There are over seventy types of chart in Excel (though many of them are similar to one another). A complete list can be found in the chart wizard (where the charts are organized by group ) or in VBA help under the keyword ChartType .

Combination Charts

Combination charts are charts in which several chart types are combined (for example, a line chart and column chart). Combination charts can be created either with the help of a user-defined chart (see below) or by changing the chart type of a single data series (not the entire chart).

Charts can be combined only if they are based on the same coordinate system. Therefore, the range of combination possibilities is relatively narrow. Three-dimensional charts cannot be combined at all.

Pivot Charts

Pivot charts are new in Excel 2000. These are not actually a new chart type, but a new way of linking data between a chart and a pivot table. What is special about pivot charts is that categories for structuring data can be created dynamically (that is, by means of listboxes in charts). The chart is immediately revised. For the chart itself almost all the chart types listed above can be used. Pivot charts will be described within the framework of pivot tables in Chapter 13.

User-Defined Chart Types (Autoformat)

There are two ways to format a chart: You can select one of the standard types, or you can employ a so-called user-defined type (formerly autoformat). Among these types are stored numerous formatting details, so that you can very quickly create a wide variety of different charts. The name "user-defined" is somewhat confusing, since Excel recognizes an entire palette of predefined (integrated) types.

The user-defined formats in Excel give a very good overview as to what is available. The formats are located in Officedirectory\Office\n\Xl8galry.xls , where n is a language code (for example, 1033 is the number of the American version).

More important is the possibility of adding your own user-defined formats and using them in the future. For this you format a chart to your specifications, open the "Chart Type" dialog with the right mouse button, switch into the page "Custom Types," and click on the option button "User-defined." Then click the Add button and save your format as a new chart type. New (personal) chart types are saved in the file Userprofile\Application Data\Microsoft\Excel\Xlusrgal.xls .

Chart Elements (Chart Objects) and Formatting Options

For the detailed layout of charts as well as for programming charts it is necessary to know the distinctions made by Excel among various chart objects. Assistance in your experimentation is offered by the chart toolbar. There, in the left listbox is shown the object that was just clicked on, such as "axis n ," "gridlines n ," "Series n ," and so on.

• Chart Area : This is the object ChartArea , which is responsible for the background of the entire chart (that is, the region that is visible behind the plot area, the legend, and so on). The type style that is input here holds for all text of the chart that is not otherwise specially set.

• Plot Area : The plot area ( PlotArea ) represents a rectangle around the graphic region of the chart. The plot area contains the actual chart, but not the title, legend, etc. With most two-dimensional charts even the axes are not part of the plot area. If, for example, you specify the background color green for the plot area and red for the chart area, the labels for the axes will be underlaid with red.

• Floor , Walls : These two objects exist only for three-dimensional charts and describe the appearance of the floor and walls of the two vertical border surfaces of the chart. The plot area in this case is considered to be only the rectangular region outside of the chart itself.

• Corners : Even the corners exist as an independent object in three-dimensional charts. Corners cannot be formatted. But they can be grabbed with the mouse and turned in three dimensions. This is often more convenient than setting the viewpoint and perspective via the dialog Chart3D-View.

• Data Series : A data series describes a related unit of data (usually the values of a column from the underlying table; only if you select "Series in Rows" in step 2 of the chart wizard will data series be organized by rows). For example, a data series is reprenseted by a line. The formatting data of data series affect the graphic representation of this data series, that is, color, markers, line style, etc.

• Data Points : The individual values of a data series are represented by data points. Normally, the format properties of all data points are the same and are preset by the properties of the data series. However, you can set the properties of each data point separately and thereby thrust individual points of a series into prominence, or label points individually, for example. In a pie chart you can shove individual pie slices out from the pie and distinguish them in this way ”that, too, affects the property of the data point. Caution: The vertical position of data points in two-dimensional charts can be changed with the mouse, and this changes the underlying value in the data table!

• Trend Lines : Data series of two-dimensional charts can be associated with trend lines. The trend lines are drawn in addition to the normal representation of the data. Excel recognizes types of trend lines: best-fit curves (five different types) and averaging curves.

• Error Bars : Error bars are another subelement of a data series in two-dimensional charts. They indicate potential error amounts relative to each data marker.

• Coordinate Axes : The coordinate axes have a large number of formatting details, which begin with scaling (minimum, maximum, linear or logarithmic ) and end with the precise arrangement of the axis labeling (which data points are labeled, which are indicated by a tick marks, whether the tick mark is inside or outside, and so on). New since Excel 97 is the possibility of labeling the coordinate axes with text in any orientation (horizontal, vertical, or slant; Format AxisFontAlignment).

  There is also the option to equip a two-dimensional chart with two independent Y-axes, where one is valid for some of the data series and the other for the remaining data series. This is useful when you wish to represent on the same chart two related quantities that have different scales (for example, a voltage and current). In order to employ two Y-axes it is necessary to separate the data series into two groups. The easiest way to accomplish this is by selecting the custom chart type "Lines on 2 Axes."

• Grid Lines : The plot area of a two-dimensional chart or the walls and floor of a three-dimensional chart can be combined with grid lines. The position of grid lines is determined by the tick marks on the coordinate axes. The appearance (color, line style) of principal and secondary grid lines can be set separately (but only for normal charts, not composite charts).

• Title : A chart can be equipped with several titles (for the chart, the axes, etc.). The position, type style, and alignment can be set independently.

• Legend : The legend makes possible a link between the colors used in the chart and the patterns of the data series. The labeling of the legend is taken from the first column or row of the data series. The legend can be placed anywhere in the chart (even beneath the data).

Chart Options in ToolsOptions

With ToolsOptionsChart you have access to a few further chart options. These settings concern only the current chart (and can be changed only when the chart is active).

The option "Plot empty cells as" determines how Excel responds to empty cells in the data series. In the setting "not plotted (leave gaps)" there appears a hole in the chart (that is, bars are missing, a line is broken, etc.). The alternatives to this setting are "zero" (then Excel treats empty cells as if they contained the value 0) and "interpolated" (then Excel attempts to interpolate suitable data values for the empty cells).

The check box "Plot visible cells only" determines how Excel deals with hidden rows and columns: If the box is activated, then data in invisible rows or columns are not displayed. In the chart the data are simply ignored (rather than a hole appearing). This setting is of interest primarily when the chart data come from a filtered database.

The check box "Chart sizes with window frame" is of interest only for chart sheets. When the box is activated the chart is fit to the current size of the window. Otherwise, only one print page is shown. To make the entire chart visible, the zoom factor may have to be changed (ViewZoom).

Trend Lines, Data Smoothing

With line charts you can select the option "Smoothed line" in the formatting settings for the data series. This has the effect of rounding the edges of an otherwise angular course.

Other possibilities for providing a best-fit curve or averaging curve are offered by the command ChartAdd Trendline. Excel can approximate a data set with five different types of best-fit curves: straight line, polynomial curve (up to sixth degree), logarithmic curve, exponential curve, power curve. With the options in the dialog Format Trendline you can specify whether and to what extent the curve should be extended beyond the current data and whether the formula for the curve should be given.

A sixth type of curve can be specified in the Trend Line dialog: an averaging curve based on a running average. Here every point on the curve is calculated from the average of the n preceding points. This has the effect of smoothing statistical errors of measurement. Averaging curves, in contrast to best-fit curves, cannot be extended beyond the range of the data.

Some examples of the application of the trend line function are indicated in Figure 10-1. The associated example file Trend.xls is included with the sample files for this book.

Figure 10-1: Three examples of trend lines

Error Indication

Data series in two-dimensional charts can be provided with error indicators (error bars). These are small lines that specify the range in which the actual value of a data point is to be found if statistical error of measurement is taken into account.

Printing

When it comes to printing a chart two variants must be considered: If the chart is embedded in a worksheet, then printing is accomplished by way of printing the worksheet. Here the only problem is that Excel does not give much thought to where page breaks are inserted, and even a small chart might find itself broken into four pieces. It does not hurt to check the page preview before printing. You may find yourself compelled to insert some hard page breaks to optimize printing (InsertPage Break).

On the other hand, if the chart is located in a chart sheet, or if you wish to print an embedded chart that has been selected with a mouse click, then there are some options available in the dialog PAGE SETUP. The most important of these is "Printed chart size" on the CHART page of this dialog. In the standard setting Excel uses the entire page. If the chart does not happen to have the same format as the page, the chart can become completely distorted . Therefore, it is usually better to select the option "Scale to fit page." Excel enlarges the chart only to the extent that the relationship between the length and width does not change (that is, the aspect ratio is preserved). The third variant, "Custom," leaves the size of the chart unchanged.

The option "Print in black and white" allows color charts to be printed on a black and white printer. (Most printers can handle this without the use of this option.) Whether with or without this option, you will achieve usable results on a black and white printer only if you refrain from using color in your chart. Use instead differing line widths and types to distinguish among several data series. Since the standard and custom chart types are generally extremely color friendly, the creation of a satisfactory black and white substitute usually requires considerable effort (say, about 100 mouse clicks for a typical chart). Therefore, if this is a common situation for you, then save black and white charts as a custom chart type.