How to Plot Data Using Microsoft Excel

By: Tom Bensky - Cal Poly Physics Dept. - Fall 2001 - tbensky@calpoly.edu

Say you are doing some work, like in a physics lab, and you need to make a professional looking, two-dimensional XY-plot of some data on the computer. This document will show you how you can do this using the spreadsheet program, Microsoft Excel.

Before beginning, be sure you have your data organized and know what data should go on the X-axis, and what data should go on the Y-axis. Here is some sample data we'll use here:

To make a graph of this data using Microsoft Excel, follow these steps:

1. Run Excel from the Windows desktop. If you don't see an icon on the screen, you can find it under the Start button.

2. Next, you need to type your data into Excel. Excel works with data best if a given data set is in a vertical (up/down) column. So, each data set (like X-axis data, or Y-axis data) should be put into a single column. For the data shown above, we'll use two columns: one for the X-axis data, and one for the Y-axis data. Further, when producing XY plots, it's easiest place the X and Y-axis data in adjacent columns, with the Y-axis data to the right of the X-axis data. In the following figure, the sample data above has been typed into Excel:

Notice here that the X-axis data is in its own column, as is the Y-axis data. Also notice that the columns are right next to each other, and the Y-axis data is in the column just to the right of the X-axis data.

A few notes on using Excel: You can use the arrow keys or mouse to point the cell pointer (the heavy rectangle) to a given cell. Press [Enter] after you type each number to enter it into a given cell. This will also automatically move the cell pointer down one cell, in preparation for the next number. Mistakes, etc. can be changed at any time by simply moving the cell pointer to a given cell and retyping the number. You can also press [F2] to edit the cell directly. Notice that the spreadsheet is sort of like a two-dimensional grid. Each cell has a letter referring to its column, and number referring to its row. The upper, leftmost cell is A1. A2 is below it, and B1 is next to it, etc.

3. Now, you need to tell Excel that this is the data you want to plot. Do this by moving the mouse pointer over the upper leftmost number in your data set. In this case, it will be the 1.2 at column A1. Next, while holding down the left mouse button, move the mouse to the lower rightmost data point (the 27.9, at cell B5). This is called "dragging the mouse pointer." You can also do this using the arrow keys. Move the cell point to cell A1. While holding the [Shift] key down, use the arrow keys to move the cell points to the lower, rightmost cell, in this case, cell B5.

   Either way, you should see the numbers be highlighted in black or blue as you do this, as shown in the figure.

4. Now that the data to be plotted is highlighted, click on the chart wizard icon to begin creating creating your graph. Even though everyone calls these things graphs or plots, Microsoft likes to call them Charts. The icon is shown circled in this figure:

The icon may be at different spot on your screen, depending on what version of Excel you're using, but should look the same. After you click on it, the following window should pop up, giving you different types of graphs to choose from. You are encouraged to experiment with all graph types to see what they do. For this type of data though, click on the XY (scatter) plot. The window will change to the following:

The selections to the right show different types of XY plots you can make. They mainly involve what you would like to see on your plot, where each data point exists. Should points only be plotted? Lines only? Points and lines? The choice is up to you. One note here: having the computer connect your data with line is not the same as fitting a line to your data. It looks nice, but in some cases, these connecting lines can hide and or confuse important trends in the data you might be trying to see. In other situations, the lines might help.

Make a selection. You can press the "Press and Hold to View Sample" button to quickly see what your plot will look like. When done, click "Next>"

5. The next window that will pop up has a few options. Namely, you can tell Excel if your data is in rows or columns (it already knows it's in columns). If you click the "Series" tab, you can change the name of your data from "Series 1" to "Gravity Data" or something more useful and relevant. Generally, if some sort of title or label appears in your plot, be sure the name means something that is relevant to your plot. When done, click "Next>"

6. The next window is very useful, and is shown here:

It contains all kinds of options for setting the labels on your X and Y axes, whether or not you want the legend, numbers, tick marks, lines, etc. Be sure to click on all of the tabs at the top of the window and try out the options. At minimum, give your X and Y axes meaningful labels. Click "Next>" when done. Generally the legend is not needed if you are only graphing one set of data.

7. The next window asks where you want your chart to be placed, in a new sheet or in the existing sheet.

Do what you want here. For this example, we'll choose to put the chart in the current spreadsheet. Click "Finish" and viola! A graph of your data will appear in your spreadsheet.

8. You may now proceed to print the chart by clicking somewhere in the white area surround the chart. If you do this correctly, a rectangle should appear around the chart that looks like this:

Once the chart is selected like this it is ready to be printed. You can pull down the "File ®Print" option to print your plot.

10. There is one more set of useful options once your chart has been created: changing

the X and Y scales. The scales refer to the minimum and maximum numbers that appear on the axes of your plot. Excel will do this for you, but at times you might want to

zoom in on certain portions of your plot, or otherwise set your own plot range(s). To change the X and/or Y-axis scales, double click somewhere on axis whose scale you want to change. A window will pop up that looks like this:

From this window, you can change the line thickness, etc. If you click on the "Scale" tab, you should see this:

Here, you can check or uncheck the "Auto" mode for a given the scale attribute. The Y-axis was double clicked on in this case. If you uncheck a given minimum or maximum, you can fill in your own value, and the plot will instantly update.

Fitting a straight line to data

Frequently, if your data is linear, you might be asked to find the slope and y-intercept of a straight line that approximates your data. Typically the slope and y-intercept have some crucial meaning. You can have Excel find the slope and y-intercept.

As mentioned above, telling the computer to connect your data points with a line is not the same as fitting a line to your data. If you have linear data and are trying to figure out the slope and/or y-intercept of your data, it is strongly recommended that you produce your plot with points only (no connecting lines). The artificial connecting lines will only confuse the plot.

Here is how you get Excel to find the slope and y-intercept:

1. Pull down the "Tools® Data Analysis" menu. Scroll down and select "Regression."You'll get a window that looks like this:

2. Click the cursor into the "Input Y Range" box, then drag the mouse over the Y-axis data. Next, click the cursor into the "Input X Range", then drag the mouse over the X-axis data. Click OK, and a new sheet will be created with the results. There is a lot of information that will pop up. Here is what popped up for this data:

For the slope, look for the "X Variable" (recall in the formula for a line, y=mx+b, m, the slope, the coefficient of the x-variable). For the Y-intercept, look for the "Intercept" label. For this data, the slope is 5.79, and the Y-intercept is -8.42.

It is nice to put the straight line fit and your data on the same plot, so you can actually visualize the fit. If you want to do this, generate a table of some data points from your newly found linear equation. In this case it's: y=5.79x-8.42. If you look at your spreadsheet, the data you typed in is in columns A and B. Put the data you generate from the linear equation into column C, then highlight all three columns. If you regenerate your plot using the chart wizard, you'll see your data and the linear fit all on the same plot! For this type of plot, you should tell the computer to plot the line data with lines only, and the data with points only.