Creating Simple Charts


You've imported or generated data and you'd like to plot your data for further analysis.


Use Excel's built-in charting features. Select the data you want to plot. Then select Insert images/U2192.jpg border=0> Chart...from the main menu bar to launch the Chart Wizard . Now let the wizard walk you through the rest of the chart-creation process.


Let's walk through an example to illustrate the basic steps involved in creating a chart in Excel. Figure 4-1 shows the data we'll use in this example.

The data consists of two columns. The first column contains heading angles in degrees and the second contains wind force in kilo-pounds (kips) corresponding to the heading angles. (In case you're curious, this data represents the wind forces exerted on an offshore drilling rig operating in the Gulf of Mexico.)

Figure 4-1. Sample XY data

This is a good example of the sort of data that can be plotted on an XY chart. Basically, the heading data represents abscissa (x-axis) values, while force data represents ordinate (y-axis) data. Plotting the data should reveal how the wind force varies with heading.

To plot the data, click and drag the cell range containing the data. In this case, click and drag from cell A1 to B38. It's okay to include the column headings in the selected range, because Excel will use these as default series names.

After selecting the data, select Insert Chart...from the main menu bar or press the Chart Wizard button on the toolbar (in Figure 4-1, its the eighth button from the left under the main menu bar and has a little bar chart image on it). This activates the Chart Wizard (see Figure 4-2).

Figure 4-2. Chart Wizard: Step 1

Excel offers a variety of built-in chart types, as shown in the "Chart type" list in Figure 4-2. See Recipe 4.2 for a list of other kinds of charts you can create in Excel. Here, we're working with an XY (or scatter) chart, so select XY (Scatter) in the "Chart type" list.

Both Line and XY scatter chart types allow you plot points or lines and curves through data. XY charts allow you to specify both the ordinate and abscissa of each point, which can be at irregular spaces. Line charts plot ordinates versus equally spaced abscissas corresponding to the data point number. XY scatter charts give you greater flexibility when plotting two-dimensional data.

The right side of the Chart Wizard contains images of available sub-types for each major type of chart. XY charts have the five sub-types shown in Figure 4-2. The images are fairly representative of the sub-types. The choices here include simply plotting data points, plotting points with smoothed lines passing through them, plotting just the smooth lines, plotting points with straight line segments passing through them, and plotting just the straight line segments. I've selected smooth lines with point markers for this example.

If you press the button labeled "Press and Hold to View Sample," you'll see a little sample of your data plotted with the selected chart type and sub-type. Click the Next button to go to the second Chart Wizard step, or click Finish to stop here and create your chart, after which you can view and edit it manually.

Figure 4-3 shows the second step in the Chart Wizard.

Figure 4-3. Chart Wizard: Step 2

I included two views of the step 2 dialog box in Figure 4-3. The one on the left corresponds to the Data Range tab , while the one on the right corresponds to the Series tab .

On the Data Range tab, you can change the data range you selected for plotting. Since I selected the data before launching the Chart Wizard, the data range field is already populated with the correct data range. Excel makes an assumption as to whether or not your data is oriented in columns of data or rows of data. In this case, the data is contained in two columns and Excel correctly defaulted to "Series in Columns." Excel gets this right most of the time and usually goofs up only when you don't select headings in your data range and your data range is nearly square.

The Series tab shows the data series that will be plotted on the chart. In this example, the first column in the selected range represents x-values and the second column represents y-axis data. You can have more than one column of y-axis data corresponding to a single column of x-axis data. In such cases you could plot more than one data series. Here, you have the opportunity to add more data series to your chart. Further, you can always access the series dialog to add new series to an existing chart.

Once a chart has been created, you can access the Source Data dialog box to edit, add, or delete series. Right-click on the chart and then select Source Data from the pop-up menu to display the Source Data dialog box.

To add a new series, select each field on the right side of the dialog and fill in the appropriate cell ranges corresponding to the range containing the series name, the series x-axis data (which could be the same for all series on a chart), and the series y-axis data. Pressing the little icon to the right of each range field allows you to temporarily switch back to your spreadsheet and select the ranges by using the mouse instead of trying to remember cell references and manually typing them in.

Again, at this point you can press the Next button to proceed to the third step or press the Finish button to quit the Chart Wizard, thus creating your chart.

The step 3 dialog contains five different tabs, as shown in Figures 4-4, 4-5, and 4-6.

Figure 4-4. Chart Wizard: Step 3, Titles and Axes tabs

The Titles tab allows you to enter titles for major components of your chart, such as the chart title and the axes titles. If you have secondary axes (for plotting multiple series with different scales), you can also specify titles for the secondary axes. (See Recipe 4.6 for more information.) The Axes tab allows you to specify which axes you'd like to show.

Figure 4-5. Chart Wizard: Step 3, Gridlines and Legend tabs

The Gridlines tab allows you to select which, if any, gridlines you'd like to show on the chart. Major gridlines correspond to the major scale divisions for each axis, while the minor gridlines correspond to the minor scale divisions. (See Recipe 4.4 for more information on customizing your chart axes, including setting scale divisions.)

The Legend tab allows you to specify whether or not you want to include a legend on your chart and where you'd like it displayed. For this example, we really don't need a legend, since we're plotting a single data series. Legends are handy when you have multiple data series and would like a key that lets the user know which is which.

The final tab, Data Labels, allows you to specify whether you want data labels plotted next to each data point on your series and which label you want shown. You can choose from the series name itself, the x-value of each point, or the y-value of each point. (As you can see, there are a few other options for some other specific chart types.)

Figure 4-6. Chart Wizard: Step 3, Data Labels tab

At this point I usually just press the Finish button to quit the Chart Wizard and create my chart. However, let's take a look at the final Chart Wizard step by pressing the Next button.

Figure 4-7 illustrates the final step in the Chart Wizard.

Figure 4-7. Chart Wizard: Step 4

In this final step, you tell Excel where you want to create your chart. You can choose between having the chart created on a new chart sheet within your workbook or embedding it within a different worksheet.

Almost 99.9% of the time, I choose to have the chart embedded in the same sheet containing the data I'm plotting. The main reason I do this is that in many cases the data being plotted is generated or manipulated with custom calculations, and by placing the chart on the same pane as the data (and calculations), I can instantly view how changes in the underlying data (and calculations) affect the chart.

If you choose to place your chart on its own sheet, then you can specify the name of that sheet in this dialog box. The drop-down list next to the "As Object in" radio button allows you to select into which worksheet you want to embed your chart.

Now you can press the Finish button to exit the Chart Wizard and see the resulting chart. Figure 4-8 shows the chart created for this example.

Figure 4-8. Newly created chart

You have some flexibility in where to locate your new chart in a worksheet. You can click and drag it anywhere to suit your needs.

You need not worry about skipping steps in the Chart Wizard or changing your mind. You can always go back and reformat your chart. See Recipe 4.3 for more information.

See Also

See Recipe 4.2 to learn about other chart types available in Excel.

Using Excel

Getting Acquainted with Visual Basic for Applications

Collecting and Cleaning Up Data


Statistical Analysis

Time Series Analysis

Mathematical Functions

Curve Fitting and Regression

Solving Equations

Numerical Integration and Differentiation

Solving Ordinary Differential Equations

Solving Partial Differential Equations

Performing Optimization Analyses in Excel

Introduction to Financial Calculations


Excel Scientific and Engineering Cookbook
Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))
ISBN: 0596008791
EAN: 2147483647
Year: N/A
Pages: 206
Authors: David M Bourg © 2008-2020.
If you may any questions please contact us: