# How to Make Really Nice Graphs Using Microsoft Excel 2011

Only available on StudyMode
• Published : September 14, 2012

Text Preview
How to Make Really Nice Graphs Using Microsoft Excel 2011

Data Entry: Enter your data into Excel in column form such as the following: Horizontal Variable Name x1 x2 x3 x4 x5 x6 Uncertainties ∆x1 ∆x2 ∆x3 ∆x4 ∆x5 ∆x6 Vertical Variable Name y1 y2 y3 y4 y5 y6 Uncertainties ∆y1 ∆y2 ∆y3 ∆y4 ∆y5 ∆y6

For example, if you are measuring the length of a spring under the application of a force, the table might look like this: Position (m) ∆Position (m) 0.595 0.612 0.631 0.649 0.667 0.686 0.001 0.001 0.001 0.001 0.001 0.001 Force (N) 0.49 0.98 1.47 1.96 2.45 2.94 ∆Force (N) 0.02 0.04 0.05 0.06 0.06 0.08

Make sure you have used proper units in the table so that the results will also come out in proper units. If the units need changing, there is no need to re-enter the data. Use Excel to do the work for you. Once you have the data in proper form for graphing, go on to the next step.

Creating the Basic Graph: Click on the Chart tab just under the top row of icons in the Excel window. From the graph types presented when you click on the chart type icon on the far left, select "Scatter" with no lines connecting data points. Now, under the Chart tab, look for the Data subtab and choose “Select” so you can select your data. You will then see the Select Data Source window. Click on “Add” to define a new data series to graph. Now you need to specify the cells that contain the "X" values of this data series and which cells contain the "Y" values. Click on the small square icon located just to the right of the text box for X-values. The Source Data window will collapse and the program is waiting for you to indicate the range of cells containing the "X" values. Using the cursor, select the entire set of X values by first locating the cursor over the first X value and then holding down the mouse button while dragging over the remaining X values. When the correct cells are highlighted, release the mouse and click on the small box again. The Select Data Source window will return to full size. Then click on the small square icon located just to the right of the text box for Y-values and select the Y data in the same sequence of step you used for X. Additional data sets may be graphed on the same graph by defining additional Series in the Select Data Source window. When you have completed defining the data series, click “OK”.

MSK 9/12/11

page 1

Select the graph you have made and click on the “Chart Layout” tab just under the row of icons at the top of the Excel window. This tab will not appear if you do not have a graph selected. Click on the Chart Title icon and enter a correct and useful title. Click on the Axis Titles” icon and add correct and useful names for the X and Y variables including units in parentheses. Click on the Gridlines icon and turn on major gridlines for both X and Y axes. Depending on how many data points the graph has, you may also want to turn on minor gridlines. For some graphs, you may want to exert control over the spacing of the gridlines. This can be redefined at Axes/Horizontal Axis/Axis Options/Scale and Axes/Vertical Axis/Axis Options/Scale. Your graph should have a legend only if you display more than one set of data pairs on a single graph. Because all measured quantities have uncertainty, a graph showing or based upon measured quantities will almost always need error bars on one or both variables. Adding error bars is discussed below. Sometimes the data shown in a graph appear to form a straight line or they may be expected to have a linear relationship for theoretical reasons. In such cases, you will often want to fit a line to the data. Adding a fit line is discussed below. Now move the graph to its own sheet. (They are easier to look at and print out much nicer that way.) Select the chart and select “Move Chart” from the Chart menu. In the dialog window that opens, select “New sheet” and click “OK”. The graph should appear in a new sheet, nice and large. (Excel will now resize the graph it to...