Excel 97/2000: Charts

So You Want to Create a Chart . . .

You've probably heard the adage, "a picture is worth a thousand words." Sometimes a chart or a graph can say more than a thousand numbers; Excel has the ability to create these visual aids. Before you start a chart, you must first input the data from which the chart will be drawn. Do this just as you would for creating any Excel worksheet. To create the chart, Excel will plot sets of data from your worksheet called data series. In the worksheet example to the right, the columns for median house prices for the U.S. and for Chapel Hill are two data series which could be plotted in a chart. In this example, the data in the first column, the years between 1985 and 1993, will be category labels in your chart.

What Type of Chart Should You Use?

Excel 97 has 16 types of charts that you can select and use. The selection of chart type is usually driven by the data, although there are no hard and fast rules for determining the chart type you should use. Experiment! It is extremely easy to change your chart type selection. Then use the one which displays your data and conveys your message in the simplest way possible. Below is a brief description of chart types and their general use:

Area Good for depicting magnitude of change over time.
Bar Shows the value of two or more items at the same point in time. Good for depicting dramatic difference between positive and negative values.
Column Shows two or more values side by side.
Line Illustrates trends over time.
Pie Represents your data as a percentage of the total.
Doughnut The appearance of a pie, but displaying more than one series.
Radar Depicts frequency and change relative to a central point.
Scatter Depicts two values and tries to show relationships, usually independent of time.
Combination Allows you to layer one type of chart over another.
3-D Dramatic use of some of the above charts, but be careful as they can be hard to read and distort the perspective of your data.
Surface A 3D surface shows trends in values across 2 dimensions in a continuous curve.
Stock Requires 3 series of values in the order of high-low-close.
Bubble Compares three sets of values. It is similar to a scatter plot chart with the 3rd value displayed as a size of a bubble.
Cylinder/Cone/ Pyramid Creates a column chart with a cylindrical, conical, or pyramidal shape.

How Excel Works with Charts

When you create a chart, Excel creates a link between the worksheet data and the chart. When you update or change the data, the chart is automatically adjusted. Also, when you create a chart you can choose from two different placement types: an embedded chart or a chart sheet. You can choose to insert the chart directly into a worksheet as an object (an embedded chart) or you can make it a new sheet (a chart sheet) in the workbook.

Stepping Through Charts with the Chart Wizard

Once you start the creation process, the ChartWizard will walk you through the following four steps. First, click on the Chart Wizard tool button on the right side of Formatting Toolbar, or choose Chart under the Insert menu option.

1. Selecting the Chart Type

The ChartWizard will now allow you to select from 14 different chart types. Select the chart type and sub-type you desire, and then choose the Next button. Below the sub-type options is a box that tells you specifically what the chart type and sub-type are designed for. There is also a button for you to press that will show you a sample of your data in that specific chart type. You also have an option for you to create a custom type of chart. It is advised here that early on in your Excel charting experience you choose from the over 100 options offered to you from Excel before you go out on your own.

2. Defining the Data Range.

In this step you will be asked to define the area or range to be included in the chart. To assist you in this, you can minimize the dialogue box by clicking on the tool button located in the far right of the Data Range Box. If you selected the data you wanted to chart prior to starting the ChartWizard, that area will appear highlighted in the Data Range. From here you can select the Next button to move on to the next step.

The 2nd tab, "Series", allows you to add a series of data to act as your X-axis. The other options in this tab are not necessary if you specified the correct data ranges, names, and labels. However, if you wish to change any of these values this is where you would come. This is most helpful after you have already created your chart and you want or need to come change the data. See also the later section Modifying a Data Series.

3. Displaying the Sample Chart

The Chart Wizard will display a sample chart based upon the selections you made in the previous steps. If you don't like the chart type or format that you've selected, you may change these by using the Back button to return to the previous dialog boxes.

There are 6 options for your manipulation under step three:

If at any time later you want to change the selections you made, just select the Chart Wizard tool button or Chart under the Insert menu option.

4. Chart Location

The final Chart Wizard step lets you decide if you wish to place your newly created chart on its own sheet or as an object in a pre-existing sheet. If you choose "As new sheet", you can name it thusly, or anything else for that matter.

Parts of a Chart

The following diagram points out basic elements of a chart. Use this diagram as a reference for understanding the following sections on chart modifications.

Anything you see in an Excel chart can be modified. Listed below are four different methods for modifying items within a chart.

Remember that if you are working with a embedded chart, before you start making changes, you must double-click on the chart to edit it.

Changes to the Chart

If you wish to make a change to the entire chart, such as changing the fonts throughout it or copying the chart to another document, you need to select the chart area. You can select the chart area by clicking with the left mouse button anywhere outside of the axis area. Black handles will appear around the chart area. Double-clicking on the chart area will bring up the Format Chart Area dialog box where you can make changes to fonts and patterns.

Changes to the Plot Area

The plot area is the area inside and bounded by the axes. You may choose the plot area by clicking in any blank areas inside the boundaries. Black handles will appear on the area within the axis. You may also double click on the plot area to make changes to the borders, colors, or patterns in that area.

Working with Chart Text

There are two basic types of text used in Excel charts--attached and free-floating. The chart title and axis titles are examples of attached text. If you did not create these titles in Step 3 of the ChartWizard, you can go back and insert them.

Free-floating text may be inserted anywhere on the chart. It is often used to add explanations or comments to a chart. To add free-floating text to your chart, just start typing and then press Enter when you are finished. The text will appear in the middle of the chart and you can then move it to the desired place on the chart. Unlike attached text, free-floating text may not appear where you want it if you resize or re-position the text or if you add additional data series to your chart.

Any text may be easily formatted by either double-clicking on the text to display the format dialog box or single-clicking with the right mouse button to bring up the formatting short-cut menus. Either of these methods will allow you to make changes to font, alignment and pattern selections.

Using the Chart Toolbar

Again, anything you see can be modified within your chart. Using the drop down menu from the Chart Toolbar and selecting the first tool button will allow you all options regarding that facet of the chart. The following buttons are explained here.

Changing the Chart Type

If you wish to change your chart's type, you can use the Chart Type icon on the Chart Toolbar to select from a drop-down list of charts. Two things to take note of:

Adding or Modifying a Legend

If you wish to add a legend to your chart, the easiest way to do this is to use the Legend tool on the Chart Toolbar. This Legend icon is a toggle button which can be used to either create or remove the legend. The legend is generally built from the information stored in the first column or row, depending on the shape of the selected area to be charted, unless you specify otherwise. The chart is reduced in size to accommodate the legend, so this may sometimes not be an acceptable choice. You can format the legend by double-clicking on it and making the necessary or appropriate changes to the font, placement, or pattern selections

Adding a Data Table

If you wish to add a data table below your chart, select this button from the Chart Toolbar. You can also excise this option under step 3 of the Chart Wizard.

Using Data Series in Rows or Columns

If you wish to view your data series by rows rather than columns (or again, vice versa), you can toggle between the next two button found on the Chart Toolbar.

The Angle of the Text

The last two buttons on the Chart Toolbar angle the text found on the axes. You must first select an axis in the chart area or choose the appropriate axis from the drop down menu on the Chart Toolbar to use these buttons. Each button is a toggle unto itself (`on' or `off") unlike the Data Series tool buttons.

Closing Thoughts

Modifying a Data Series

It is easy to add a new data series to you chart, delete an existing series or format one or more series.

Adding a New Series

You may select from any of the following methods when inserting a new data series in your chart:

Deleting a Series

First select the series and then either hit the Delete key or choose Clear and Series from the Edit menu.

Formatting Data Series

As with other chart modifications, you may double-click on a chart series or single-click with the right mouse to bring up formatting options for data series. The Format Data Series dialog box will provide you with options to redefine the data series names and values, data labels, patterns, axis, X values and Y error bars.

Adding Arrows and Other Graphics

To add arrows or other drawing objects to your chart, you will need to activate the Drawing Toolbar. To do this, select Toolbars from the View menu and then choose Drawing. To add an arrow click the Arrow icon and then drag with the mouse until the arrow is in the correct position and is the desired length.

Dragging a Data Marker

As mentioned before, there is a direct link between the chart and its corresponding worksheet. Because of this it is easy (and also somewhat dangerous if you're unaware of it!) to change the data in your worksheet by selecting an individual data point in a chart series and then dragging it to a new position in the plot. This will adjust the value in the worksheet to match the new value on the chart.

This document is a publication of Academic Technology & Networks at The University of North Carolina. It may be copied for individual or non-profit use. Please send comments about this publication to CB# 3450, 402 Hanes Hall, Chapel Hill, NC, 27599-3450 or email atndocs@unc.edu. Author: Bill Estes. Editor and Web Editor: Christopher Osmond. Revision date: February 20, 2001. ATN Document dwe28