Graphing With Excel 2003 – Basic Intro

Rod Rodrigues

 

Click here for Printer-Friendly Version

 

This page is not an introduction to Excel. It is assumed that you know the basic procedures for working with Excel 2003.

 

This page will not show you how to use Excel without using the graphing capability of Excel. This page will show you how to unleash the graphing capability of Excel. When accompanied by the templates for Algebra, Pre-Calculus, and Linear Systems/Linear Algebra, Excel is a powerful ally in the effort to teach and learn mathematics.

 

No matter how you graph, there are always questions you have to ask yourself. You can start with two fundamental questions:

 

1. Which values of the independent variable should I use?
2. Which values of the dependent variable should I allow?

 

There are no fixed answers to these questions, fundamental as they are. Answering these questions is called fixing the view window. The independent variable is usually plotted along the horizontal direction in mathematics, while the dependant variable is plotted using the vertical axis.

 

Fixing the range of values for the independent variable.

 

Students often want to plot values of the independent variable near zero. Further, they often would like to always graph using the values 0, 1, 2, 3 and leave it at that. To decide a good fit, you might ask yourself:

 

• Given the situation, should I expect negative values of the independent variable? Or is this an applied problem in which no negative values are possible, really?
• Considering the context, do I need to get to large values of the independent variable? Furthermore, how much space should there be between values of the independent variable? Should you be graphing for values starting at 5,000 and increasing in increments of 2,000? Should you graph for values starting at 4, in increments of 0.5? In a general context we cannot answer these questions. They have to be decided within context. Very often, the answer is NOT 0, 1, 2, 3.

 

Deciding which values need to be reached in the dependent variable.

 

Look at your independent variables and see what is needed to support the information. Are the values to be plotted small? Large? Adjust your scale to fit the information to make it graphable. Excel, actually, will adjust the scale automatically for you, depending on the values that must be plotted. You are free to change the settings used by Excel.

 

Note: if you set your View Window improperly, you may not HAVE a graph in that region!

 

Getting Started In The Excel 2003 Spreadsheet

 

Suppose we want to list our choices for the values of the independent variable into column A of an Excel spreadsheet, starting in cell A5. Suppose that we want to start with the value 5 and continue in increments of 0.5.

 

Start by entering    x    into cell A4 and   5   into cell A5.

Move to cell A6

Type the expression   =A5+0.5    followed by the Enter button. This tells Excel to take the contents of cell A5 and add 0.5 to it. You will then see 5.5 in cell A6.

 

 

This actually sets the beginning value and the increment.

 

Click on the dot in the lower right corner of A6 and DRAG the mouse pointer down.

 

 

 

Wherever you let go, Excel will fill those cells with continue values generated with the increment you input (0.5). Drag to an appropriate number of cells. Suppose you dragged it to generate the values of the independent variable to go up to 10….

 

 

 

 

By doing this, you have “said” that you want to look at values of the dependent variable for values of the independent values running from 5 to 10 in increments of 0.5.

 

Now position the cursor in cell B4 and enter the letter   y   there. Position the cursor in cell B5 to get ready to enter the relation between the two variables. Suppose you want to graph the relation y = 2x – 5. Into cell B5 enter    =2*A5-4    and press the Enter button.

 

 

 

Then click on the dot in the lower right corner of cell B5 and drag it down to cell B15. Excel will generate all the values corresponding to the values of the independent variable.

 

At this point you will have a table of values. If you had graphing paper, you could set out a grid and graph it manually using these values. But Excel will also do that for you.

 

 

We usually then plot the first column on the horizontal axis and the second column on the vertical axis. This represents a set of 10 points. We plot them and join them.

 

 

Using the Scatter Plot Option

 

The end result here will be the usual Math class crossed-axis plot.  To achieve this, start by highlighting both of the columns.

 

 

Then click on the Insert/Chart option. Choose the connected dots in the xy (Scatter Plot) option.

 

 

 

 

You can simply accept the settings on the next screen. In the scatter plot chart, the left column is the default choice for the independent variable, and the right column is the default column for the dependent variable.

 

 

 

 

 

 

Fill in these blanks as above.  In this same window, click on the Legend Tab .  Uncheck the Show Legend.

 

 

 

 

You can set the Gridlines now or later. For this exercise, we will set it later so that you can see how to re-enter this dialog screen.

 

Click the Next button To get the following screen:

 

 

Then click the Finish button  and place the chart in the spreadsheet

 

Right-click on each of the axes in turn and select Format Axes.

 

 

 

 

Set the Weight Option to heavy and click the OK button. Do this to each of the axes to get a typical math-type graph.

 

If you would like vertical gridlines, you can right-click anywhere on the Plot Area:

 

 

Choose Chart Options

 

Click on the Gridlines Tab

 

Make sure that  Major X and Major Y axes are both checked (This could have been done when creating the chart also).

 

Click OK and you are done.

 

 

Using the Line Chart Option

 

The end result here is called a boxed plot. To achieve this, highlight the second column and click on the chart icon  on the toolbar.

 

 

There are several options that will “work” for you. I have chosen a Line chart, and in the sub-types I have chosen a style that shows the plotted points and the connecting lines. Many prefer the Scatter Plot, and in some cases I do to. But for now, choose the line chart.

 

Click the  button

 

 

Note that, if you highlighted the values BEFORE you started the chart, the data range will already be in place here. The default is set to Columns, and you can leave it that way.

 

Click on the Series Tab.

 

 

 

To fix the labels on the x-axis, we use the Category (x) axis Labels  option. Click on the icon to the right of the this Option

 

 

You will get a small floating window.

 

Highlight the data in column A. and close the small window showing your selection

 

 

Close the floating window:

 

You will return to the chart setup wizard:

 

 

 

Enter “My Graph in the Name line (or any other title you would like).

 

 

Click the  button.

 

Click on the Gridlines Tab and check Major Gridlines to make it look more like a math graph.

 

 

 

Click on the Legend Tab and unclick Show Legend.

 

 

 

Click on the Finish button. Your graph will now be free-floating in your spreadsheet. You can accept all of its settings, or you can change properties. If you have time you can “dress up” the graph a little. This does not add to the graph – it only adds to the appearance. As an example, you can double click in the white area surrounding the graph and select a different background color or pattern.

 

 

 

 

You can repeat by clicking on the gray area and changing IT’s color.

 

 

This completes the basic graphing module. There are indeed many ways to improve on this. Check Part II (Variations on a Theme) for some ideas.

 

Variations on a theme

 

Suppose we had selected a line chart without connecting the points! We would have gotten:

 

 

Each of the points has coordinates. The first point on the left has coordinates (5,6). Note that the point appears over the horizontal position for 5 and to the right of the vertical position 6.  Similarly for (6,8) and all the others.

 

Making the Axes stand out

 

In math, we usually make the axes bolder than the other lines. We can also do this in Excel. To do this, right-click on an axis and select Format Axis.

 

 

On the resulting screen, in the Lines options, choose Custom and set the weight to heavy.

 

 

 

You can do this to both axes, resulting in the graph

 

Now here is where people part company.  If you chose the Line Chart to begin with, the “axes” consist of the line bounding that area. This is called a boxed plot. It is not unusual to see this type of plot in an Engineering course.

 

However, we in the math world grew up with the notion that the line x = 0 and y = 0 should be the lines that stand out in any plot. In order to accomplish this, all you need to do is select Scatter Plot instead of Line Chart (see the instructions above).

 

How do you know which your teacher wants? Ask your teacher.

 

If you want to copy an Excel graph to a Word document, that is easy!