PHYSICSPHENOMENA.COM

"Feimer's Physics"

Student Information

GRAPHING DATA / USING MICROSOFT OFFICE EXCEL SPREADSHEET

The following guidelines are to help you in graphing your data.


Graphing Data
Ask yourself "What am I graphing?". By convention, the independent variable goes on the x-axis. The dependent variable goes on the y-axis. In a two column data table, the x-variable data goes in the left column, while the y-variable data goes in the right column.

The independent variable is the one that you manipulate in your experiment. The dependent variable is the one that you measure. All other variables that might affect the outcome must be controlled by the experimenter.

Sometimes it may be unclear which is which. So always think in terms of cause and effect. The change in the variable that is manipulated by the experimenter, is the cause and therefore is the x-variable, while the variable experiencing the effect of the change is the y-variable. An exception is when time is the x-variable. Time is not usually manipilated, but simply moves forward while observations such as measurements are being made.


 
 
Constructing the Graph
As you construct the x and y axes you must ask yourself: 

"What scale do I need?"
"Do either of your axes need to go to zero?" 

Also, think about what information you need from your graph. Many times you do not need to go to zero. Use as much of the page as possible. Graphs that are too little are extremely difficult to read. A graph should be large enough for accurate interpolation and extrapolation.

Then, if your data goes from values of 50-75 on the x-axis, don't number your axis from 0 to 250. Label your axes with an appropriate rise and run. And ask "What did I measure and what are the units of measure?" This question helps with writing good labels and creating a title for the graph.

Finally, plot the data points carefully.


 
 
Determining Best Fit (Called a Trend Line in Excel)
Circle or box the points. This makes them stand out.

Draw the "best fit" straight line (or curved line when appropriate). 
Do not "connect the dots", unless you are absolutely sure that your data definitely belongs on a straight line.. If your not sure of this, it is always safest to go with a scatter plot form of a graph.

In a real world experiment, your line may not go through some of the data points. Sometimes it may not go through any. 

Drawing the best fit line means splitting the difference between higher and lower values. Which means leaving about the same number of points above and below the line.


 
 
Working with Paper and Pencil (pen)
When constructing a graph with paper and pencil (or pen), always use a straight edge. A clear ruler works great.

When determining the slope, use two points on the line. These may not necessarily be your data points. The best points to choose are ones that lie exactly at the intersection of two grid lines on the graph paper. That way it is easier to determine the x and y values on the axes. Also, pick two points that are far apart. The further apart they are, the more precise your slope determination will be.

Give your graph a title.  Usually "y versus x for _____" is appropriate. 
An example of a title is, "Volume vs. Temperature for the warming of 0.010 mole of Argon at 1.00 atm".


 
 
Microsoft Excel Spreadsheet / Making a Trend Line
1. Open excel. 

2. In the spreadsheet, enter the data for the x-axis in column A, and the data for the y-axis in column B. 
Point to cell A1 with the mouse, then click and hold the mouse button down and drag the mouse to the bottom most cell in column B that contains data for the graph you want to construct. Note: It isn't necessary to start with cell A1 and cell B1, because what you choose to highlight will be what the Chart Wizard will select as the default data to be used in the process of constructing a graph.

3. Drag the pointer over your data to highlight these cells to be used for the graph and release the mouse button. 

4. Point to the toolbar at the top of the screen and click on the icon that looks like a column chart. In the pop up menu window that appears, choose X-Y (Scatter) for the type of chart. Make sure the first chart sub-type at the right is selected (the one with no lines through the points); if not, select it by clicking on it. 
Click Next at the bottom of the window. 

5. In  the next pop up window just click Next at the bottom of the window. This indicates to chart wizard that you are satisfied with the selected cells. If at this point you discover that the selected cells are not the ones you wanted to use, either type in the correct cell information or close the chart wizard window and start over. Starting over will only require an additional minute of your time at this point.

6. In the next pop up window, enter the chart title, as well as the title for the x-axis and y-axis (don't forget units) in the appropriate fields. 

7. At the top of the window select the Grid lines tab. Remove check marks in all boxes by clicking on them. 
Click the Legend tab, and remove the check in the Show Legend box. Click Finish at the bottom of the window; you're chart appears in your spreadsheet. If you don't like the position of the graph (the chart) click on it so it becomes highlighted (it may already be highlighted) and drag it to the position where you want it to appear.

8. To add a best fit line, point to any one of your data points and click once, and they should all become highlighted. 

9. Go to the Chart menu at the top of the screen and choose Add Trend line... In the pop up window that appears, choose the type of fit you want for your data by clicking on it (It will usually be Linear). Next Click on the Options tab. Then near the bottom of the options window, click in the box next to Display equation on chart to place a check mark in it. This will place the equation of the best fit graph line right on the graph (chart). Click OK at the bottom of the window. 

10.Your graph is finished.  Go to the File menu at the top of the page and choose Print..., if you desire a print out of the graph. In the pop up menu that appears, make sure that the dot next to Selected Chart is filled in. If not, click on it. Click Print to make a copy of the graph.


 
 
An example using MS Excel Spreadsheet
Distance vs Time Data
Time (s)
0
1
2
3
4
5
6
7
8
9
10
Distance (m)
0
0.9
1.8
2.9
4.1
5.3
5.9
6.7
8.2
9.1
10.2