Graphing in Microsoft Excel 2007
Data Table
Input your data into the spreadsheet. You should be putting your data in the format of independent variable first, then dependent variable. So in this case, time is my independent variable so it is the first column.
Make sure to include labels with units. Do not include units for each number since the units in the header covers them.
Adding a Graph (Scatter Plot)
Click on the Insert tab. Then click the Scatter chart menu.
Whenever you graph data in this class, you should be using the Scatter with only Markers type. This will allow you to graph one variable against another (like position vs. time).
Selecting your Data
If your graph that comes up is blank like below, you need to select your data.
You can find the select data in Chart ToolsDesign. Click on Select Data.
This should bring up an input box like this:
This is where you can select your data to graph on your horizontal and vertical axes. If your graph didn’t start out blank, you should still look at your series data to make sure that you are graphing the data on correct axes.
To select data for the horizontal axis, click on this button:
You should get an “Edit Series” input box. Don’t worry about typing into the box, just select the data that you want to go on the horizontal axis. In this case, I want to graph the time data on the horizontal axis. Make sure you pick just the data – you should leave the label out of the
selection.
Then click on this button to return to the previous screen
Adding Titles, Labels, and Gridlines
Click your new graph, and then select the Layout tab at the top of the page under Chart Tools. Select the Axis Titles menu. Also Turn on titles for both your horizontal and vertical axes.
Double click the new axis titles to rename them to something meaningful. Be sure to include units in your axis titles (example, “Position (m)”).
Make sure to add gridlines and choose “Major & Minor Gridlines” as shown above (for both horizontal and vertical).
Your chart title should usually be in the format of “Vertical Variable vs. Horizontal Variable”
Double-click the graph title to rename it to “<dependent variable> vs. <independent value>” (example, “Position vs. Time”).
Moving the Chart to a New Sheet
You should work on your graph as large as possible so I suggest moving your graph to a new sheet. Select your graph window and go to Chart ToolsDesign. At the far right should be a button “Move Chart”.
Select “New Sheet” and click “OK”.
You should wee a new tab at the bottom that has your graph in it.
Changing the Gridline Spacing (along with other Axes Settings)
Right now, my graph has titles, gridlines, and data.
0 5 10 15 20 25 30 35
0 1 2 3 4 5 6
Pos
ition
(m)
Time (s)
The gridlines are spaced every 0.2 s on the
horizontal axis. I want to change it so it is spaced every 0.1 seconds.
To do this, go to Chart ToolsLayout and select “Axes More Primary Axis Options”.
“Minimum” is the value that the axis will start at.
“Maximum” is the maximum value that the axis will extend to.
“Major unit” is the interval where the axis labels (the numbers) will show up. This is also the spacing of the Major Gridlines.
Adding a Best Fit Line
After setting up your graph with the appropriate axes and gridlines (to simulate graph paper), you need to add a Line of Best Fit (or Best Fit Line). You can add this trendline either manually or have Excel fit one for you.
Manually Adding a Best Fit Line
After setting up your graph with the appropriate axes and gridlines (to simulate graph paper), you need to add a Line of Best Fit (or Best Fit Line). For now, you will need to add a best fit line manually.
You can add a line by inserting a line under InsertShapesLine. Your Best Fit Line should match the general trend of your data. You should make sure to extend the line to reach the vertical axis, BUT DON’T FORCE THE LINE THROUGH 0,0!
Finding Slope
http://www.nscollegeprep.cps.k12.il.us/ncphs/depts/science/nharada/Homework/Semester1/Unit 1/Graph.htm
Adding a Trendline in Excel
Note that when Excel inserts a trendline into the graph, the line stops and does not reach the vertical axis. The trendline stops at the lowest horizontal value.
To force the trendline to extend (or forecast) back to the vertical axis go to Chart Tools
Format Choose the Trendline from the dropdown list in the left box (or you can just right-click on the trendline and choose Format Trendline).
Make sure to also click “Format Selection”
In the Format Trendline box that pops up, enter your lowest x-value in the “Forecast Backwards” box to extend your line
back to the vertical axis. In my example, I have “Time” values on the horizontal axis and the lowest value is 1.
The equation is put on as a text box so you can always edit the text to put in the appropriate variables for y and x and the appropriate units for the slope and intercept.
