Using Excel to Graph Data

1. Using Excel to Graph Data

2. Objectives • Today, you will learn how to: • Make a constant value a cell instead of a number • Create a scatter plot for your data • Insert a trendline into your graph • Format your graph with gridlines and axes titles

3. Preliminaries • First, you will enter your given set of data into an Excel spreadsheet. • Make sure you put your mass value in it’s own cell. We will use this value as a constant in your equation.

4. Entering Constants • Usually, when you copy a formula, it will change the row/column as you copy the formula. • To prevent this, put a $ in front of both the column letter and the row number (ex. $A$4) • Enter the force equation using the mass from the cell instead of typing the number

5. Entering Constants • As you can see in the picture below, the $ does not let the cell with the $ change:

6. Average Value • To calculate an average in Excel, you will want to type AVERAGE(), and put the cells you would like to average inside the parentheses.

7. Graphing • Excel also allows us to create graphs for data analysis • To graph, start by highlighting two columns of data • Click on the “Insert” tab, then click the scatterplot button.

8. Graphing • The first column you highlight will appear on the horizontal axis of the graph. • The second column you highlight will appear on the vertical axis of the graph • For the majority of your graphs, you will want to graph points, not lines.

9. Graphing • Your graph should look like this: • You should delete both instances of “Force (N)” from the graph, since you will put titles under graphs. (Only delete the title if you have multiple sets of data on a single graph)

10. Graphing – Inserting Axes Titles • Click on your graph • The “Chart Tools” menu will show • Select the “Layout Tab” • Click on the “Axes Titles” button to add horizontal and vertical axes titles.

11. Graphing – Inserting Axes Titles • For the horizontal axis, your title should read “Radius(m)” • For the vertical axis, your title should read “Force (N)”

12. Graphing – Inserting Gridlines • You will also want to add both major & minor gridlines for both the horizontal and vertical axes • In the same menu, click the “Gridlines” button, then insert the appropriate gridlines on your graph

13. Graphing – Inserting Gridlines • Your graph should now look like this:

14. Graphing – Adding a Trendline • Generally, when we take a set of data, we like to find an equation to model the data. • To add a trendline, right click on a data point on your graph. Select “Add Trendline”.

15. Graphing – Adding a Trendline • A menu will pop up. For this graph, you will select: • A linear fit • Show equation on chart Do NOT select the “Set Intercept” button.

16. Graphing – Adding a Trendline • Select close and move your equation so it can easily be read. • Your graph should look like this:

17. Graphing – Adding a Trendline • Your trendline is given as a generic y vs. x graph, since Excel does not know the variables you used to produce the graph. • When you present your data in your lab report, you should replace the y and x with the appropriate variables for your experiment. • Example  F = 34.12r + 0.562

18. Graphing - Background • Your graph should have a white background when you submit your report. • This should be a default setting on Office 2007. However if you have a grey or another color background, you need to change the color to white. • You can do this using the “Design” tab under the “Chart Tools” menu. Select a design with a white background.

19. Word • Just like your data table in your spreadsheet, you will want to copy and paste your graph into a Word file when you write your lab report. • Make sure your graph utilizes as much space as possible (~ ½ page) by clicking on a corner and dragging the image so it is larger. • Make sure you type and center a title below the graph. • Figure 1 – A graph of centripetal force vs. the radius of the hanging mass.

20. The End 