Making Graphs Using EXCEL
A.Creating a Graph and Plotting Data
1.Arrange your data in two columns with the data to be plotted on the horizontal axis to the left and the data to be plotted on the vertical axis to the right.
2.Highlight the data to be plotted by clicking and dragging over it. Do not include the quantity and/or unit labels in this selection.
3.Click on the Insert tab on the ribbon, pull down the Scatter menu and choose the Scatter with only Markers chart type. A chart will appear and the Chart Tools menus will be available.
4.In the Design tab click on Change Chart Type. A pop-up window entitled Change Chart Type will appear.
5.Choose Templates and within the My Templates section click on Physics_standard and click OK.
6.In the Design tab click on the Move Chart menu choice and a pop-up window entitled Move Chart will appear. Select the button labeled Object in:and choose one of the available sheets.
If a new worksheet is needed, close the MoveChart window and add one by clicking on the Insert Worksheet tab at the bottom of the workbook before following this step.
7.Put the title of the lab and each partner’s name in the upper left-hand corner of this sheet. Be sure to do this on all sheets that contain a chart.
8.Note that the x- and y-axes intersect at the point (0, 0) by default. If your x-axis data contains negative values you will need to move the point of intersection.
To do so, make your graph the active window by clicking it then go to the Layout tab on the ribbon. Pull down the Axes menu, highlight the Primary Horizontal Axisand chooseMore Primary Horizontal Axis Options... A popup window entitled Format Axis will appear. In the Vertical axis crosses:section select theAxis value:button andenter the minimum value from the horizontal axis of the chart; then click Close.
9.If your y-axis data contains negative values repeat the previous stepfor the vertical axis.
10.In the lower right-hand corner of the worksheet are several buttons that change the view and zoom level. Set the zoom level to 100% then change the view to Page Break Preview, choose the Page Layout tab on the ribbon andset the scale to 100% in the Scale to Fit area.
11.Adjust the width of page 1 by dragging its right-hand border first to the right, then to the left until it becomes a solid blue line, making sure to maintain a scale of 100% while doing so.
12.Adjust the length of page 1 by pulling its lower border down until additional numbered pages appear then pull the lower border up to eliminate these unneeded extra pages.
13.Move the chart to the upper part of its page and change the view to Page Layout in the lower right-hand corner. Make the chart the active window, go to the Format tab on the ribbon and set the size of the chart to 5 inches high by 6 inches wide. Finally, horizontally center the chart on its page near the top by dragging it into position; return the view to Normal.
14.Add graph and axis titles by clicking on each in turn.
15.If the plotted data is non-linear, STOP; no further analysis should be performed. Go to paragraph E for printing instructions.
16.If the plotted data is linear, follow the directions in paragraphs B, C, and D. Go to paragraph E for printing instructions.
B.Adding Trendlinesto Graphs of Linear Data
1.Make the chart the active window and go to the Layout tab on the ribbon.
2.Pull down the Trendline menu and choose Linear Trendline. The Trendline should now be on the graph.
C.Performing Regression Analysis of Linear Data
1.Click on the worksheet that contains the linear/linearizeddata.
2.Go to the Data tab on the ribbon and select Data Analysis. A pop-up window entitled Analysis Tools will appear.
3.Select Regression and click OK; a new pop-up window entitled Regression will appear.
4.Enterthe ranges for your x- and yaxis dataand click OK. A new worksheet will appear containing statistical data, which will be highlighted.
5.Go to the Home tab on the ribbon, pull down the Format menu and choose AutoFit Column Width. Then go to cell A18and change “XVariable 1” to “Slope”; to cell C16 and change “Standard Error” to “SE”; and to cell D16 and replace “t Stat” with “AE”.
6.Enterthese equations “=1.96*C17” and “=1.96*C18” in cells D17 and D18.
7.Highlight cells A16 to D18, pull down the Borders menu in the Font area and choose No Border. Finally, click on Copyin the Clipboard area.
D.Adding 4-Line Summariesto Graphs of Linear Data
1.Return to the worksheet that contains the plot of linear data.
2.Go to the Insert tab on the ribbon, click on the Text Box selection and draw a text box under the chart. Then press the Enter key on the keyboard and the regression information will be pasted into the text box.
3.Change the view to Page Layout in the lower right-hand corner. Make the text box the active window, go to the Format tab on the ribbon and make the size of the text box 2.7 inches high and 6.0 inches wide then center the text box below the chart of linear data. Return the view to Normal.
4.With the text box as the active window, right click and choose Paragraph then Tabs in the menus that appear. Change the Default tab stops: selection to 0.5 inches and click OK twice.
5.Use the Ctrl + A keys to highlight all of the information in the text box then change the font size to 16 in the Font area of the Home tab on the ribbon.
6.Use the Enterkey on the keyboard to arrange the copied data into rows; then use the Tab key to arrange the data into columns. Never use the Tab key to make rows; doing so will cause alignment problems during printing.
7.Using the Enter key, add an empty row below the copied data; another row containing column titles “Slope” and “Intercept” (with their correct units);and four more rows with the labels “Result:”, “Range:”, “Exp. Value:”and “Agree:”.
8.Place properly rounded values for the slope and intercept in the appropriate locations, add expected values and indicate agreement.
9.Check your graph against the example of a properly formatted graph and 4-line summary on the back of this page and make any necessary changes.
10.Delete the sheet containing the regression analysis after you have finished steps 19.
E.Printing
To print spreadsheets:
1.Go to the worksheet that contains the data you want to print.
2.Change the view to Page Break Preview then move the blue page borders until your data is properly paginated. Be careful to eliminateunneeded extra pages.
3.Use the Ctrl + P keys to open the Print Preview window, then click on Page Setupand change the orientation, scale, margins, add or customize Headers and Footers, etc. as desired. You should, at a minimum, choose Center on page Horizontally in the Margins tab and Print Gridlines on the Sheet tab.
4.When satisfied, click OK then press the printer icon to begin printing.
To print charts:
1.Go to the worksheet that contains the charts you want to print. Make sure that the chart is NOT the active window.
2.Use the Ctrl + P keys to open the Print Preview window, then click on Page Setup and choose Center on page Horizontally in the Margins tab.
3.When satisfied, click OK then press the printer icon to begin printing.
Introduction Lab1st Partner’s Name / 2nd Partner’s Name / 3rd Partner’s Name
1