Download An Introduction to Graphing in Excel - Paper | PHYS 1090 and more Papers Physics in PDF only on Docsity!
An Introduction to Graphing in Excel
This example uses Excel to graph Y vs X. This problem starts as follows: 1.Complete the following table and on graph paper, graph the line for the equation
x – 2y = – 6
Identify the x-intercept and the y-intercept on your graph and indicate their values as ordered pairs below.
x y
Excel will be used to graph the three points and derive the slope-intercept form of the equation of this line.
Entering and formatting data
Open Excel Instructions to click on something to select it refer to a single “Left Click” of your mouse unless instructed otherwise. The phrase “extended options” in some instructions for pull-down menus refers to the double v icon at the bottom of the menu that appears on newer versions of MS software and allows you to access less-used menu options. Clicking on this is not always necessary for a full set of options to appear. It is suggested that you save your work often. Type in Column names along Row 1. Enter the first column name X in cell A1 and Y in cell B1. For clarity, Row numbers and Column letters are shown in Figure 1 below. These would not be typed in by you. Note that in Excel the first column will be graphed on the horizontal axis and the second column on the vertical axis of your graph.
Enter the three values for X in column A. Enter the corresponding values for Y in column B. (Row #) A B C D 1 X Y 2 6 6 3 2 4 4 8 7 5 6 7 Figure 1. The appearance of your spreadsheet after you have entered your data. For clarity the Row numbers as well as the letters designating Columns are shown. See Figure 2 for the appearance of the spreadsheet after formatting.
Formatting
As shown in Figure 1, the headings do not appear over the columns of data. Formatting will make the table of data easier to read on the screen and in your printout. Format headings, bold Click on Row #1 to Highlight Row 1 Click on the icon to bold-face the text Format values and headings, centering
Click on Row #1 to Highlight Row 1. Hold down the Shift key and click on
Row #4. Rows #1 – #4 are now highlighted. Click on the icon to center the text and values Figure 2 shows the results of the formatting on the appearance of your spreadsheet data. (Row #) A B C D 1 X Y 2 6 6 3 2 4 4 8 7 5 6 7 Figure 2. The appearance of your spreadsheet after you have formatted your data. As shown here the labels X and Y have been bold-faced and centered in the columns. The values are centered in the columns.
Graphing
Note: Do not worry at this point about how many lines appear on the sample graph on your screen as you will change the gridline values shortly to make the graph more easily read. Click on “Legend” tab Deselect “Show Legend” by clicking in square box with a check in it. Since only one data set is being graphed, a legend is not needed and you will have a larger area for your graph. These are all of the “Options” changes to be made now
Click Next
Chart Wizard – Step 4 of 4 – Chart Location Leave the default setting of “Place Chart” as object in “Sheet 1”
Click Finish
Your graph appears in your spreadsheet as shown in Figure 3. Notes: As long as the small black squares appear around the border of the entire graph area, as shown in Figure 3, you can click inside the borders when you see the dialog box “Chart Area” and drag the entire graph anywhere you want to position it. This is important if it is covering data that you want to see. It is easy to deselect the chart area in any of the steps below by clicking outside the chart area. To regain selection of the entire chart area move your cursor around inside the graph area slowly without any buttons pushed down until the message “Chart Area” is shown on your screen and Left Click once.
Problem #13 Graph of Y vs X
0 1 2 3 4 5 6 7 8 0 2 4 6 8 10 X Y Figure 3. The appearance of your first graph before the formatting changes are made below. The plot of the data may appear colored on your screen, but will only print out as a shade of gray and may be lost in the gray plot-area background color. The black squares around the periphery of the graph show that “Chart Area” is selected in your spreadsheet.
Formatting the axes
Move your cursor around slowly along the scale of the Y – axis without any buttons pushed down until the message “Value (Y) Axis” is shown on your screen When you see this, Left Click twice quickly A “Format Axis” screen appears Click on the “Scale” tab and you will see the default values set by the computer for your graph. You can change these by double-clicking your left mouse button in the appropriate box. The default values for “Minimum” and “Maximum” are acceptable. You want to change the values of “Major unit” and “Minor unit” as follows: Type in a new “Major Unit” of 2 Type in a new “Minor Unit” of 1
Click OK and your scale for the Y – axis will be more realistic
Problem #13 Graph of Y vs X 0 2 4 6 8 0 2 4 6 8 10 X Y Figure 4. The appearance of your first graph after the formatting changes are made to the scales of the axes and the gray fill is removed from the plot area.
Adding the Trend line and Equation to your Graph
Obtaining the y = mx + b form of your equation
Move your cursor around inside the graph area slowly without any buttons pushed down until the message “Chart Area” is shown on your screen When you see this, Click once to select the “Chart Area” Select Chart Menu Extended Options Add Trend line opens “Add Trend line” window Under “Trend/Regression type” click on “Linear”
Left Click on the “Options” Tab Select “Display Equation on Chart” OK
On your graph appears the equation for the line If you Left Click on the equation and hold down, you can drag the equation to a more convenient location. The equation was repositioned this way in Figure 5.
In the example, the equation is y = 0.5x + 3
Figure 5 shows how your graph appears after completing changes.
Problem #13 Graph of Y vs X y = 0.5x + 3 0 2 4 6 8 0 2 4 6 8 10 Figure 5. The trend line has been added to the graph and its equation appears below the title of the graph in the upper right-hand corner. Since both the graph of the three data points and the trend line are identical lines, the trend line covers the original line and only one line appears on the graph.
Printing from your spreadsheet
Preparation for Printing Your Graph Make sure the “Chart Area” is selected Select the “Print Preview” Icon or select the “File” menu “Print Preview” to see what your graph will look like before you print it Printing Your Graph Make sure the “Chart Area” is selected
Select the “Print” Icon or select the “File” menu “Print” OK
An example of what your graph printout should look like is shown in Figure 5. Your printout should show a full-page graph.
complete data printout from your spreadsheet is to accompany all lab graph submissions in this course.
