1 Excel 2007 Graphing and Curve Fitting In Class Problems I. Graphing A. Simple Graph, one set of x,y data. 1. Enter the following data into a blank spreadsheet. Table - 1 Pressure- Gas Density Data (Acetic acid vapor) Pressure (Atm) Density (g/L) 0.200 0.522 0.180 0.489 0.160 0.429 0.140 0.368 0.120 0.310 0.100 0.253 0.080 0.198 0.060 0.145 0.040 0.094 Creating the Graph 2. If not already done, center data and headings, by first blocking everything and then using either the format menu or click on the icon on the toolbar. 3. Click on the “Insert” tab. 4. Click on the “Scatter” button and choose the “Scatter with only Markers” option. 5. A plot may be done automatically. 6. If not, a plot can be made manually in two ways. a) If not right click on the empty plot and choose the “Select Data” option. b) Click on the spreadsheet icon to the right of the “Chart data range” box. c) Select the data you want to plot by dragging the cursor over the appropriate data array. d) Click “OK”. OR2 a) Click on the empty plot and choose the “Select Data” option. b) Click on the “Add” button. c) The “Series Name” can be entered. (Experimental data, calculated data two of many options that can be chosen. d) The “Series X values” and the Series Y values” can be selected by clicking on the spreadsheet icon to the right of each box and dragging the cursor over the appropriate data range. e) Click “OK”. You now have a graph; but, it will need more work before it is a finished product that worthily communicates the ideas that you wish to impart to your reader. Adjusting Plotting Features on the Excel Graph To get your graph into an attractive final form is tedious but Excel does make it fairly easy. Each major feature (titles, legend, x-axis, y-axis etc) can be modified by using the “Layout” tab in Excel. To view the “Layout” tab, you must select the plot by clicking on it. You may want to modify the default options on the plot, especially in regards to the following options: Chart Title You will want to choose the “Above Chart” option if that is not chosen by default. Axis Titles Add axis titles, including units, for the quantities plotted. For the “Primary Vertical Axis Title”, you’ll have the option of a “Rotated”, “Vertical” or “Horizontal” title. Each has it’s uses, though the rotated title will usually look best. Legend “Show Legend at Bottom” yields the neatest plot usually. Data Labels Unless there is particularly instructive reason for including data labels, the “None” option should be applied. Otherwise your plot is likely to be “too busy”. Axes Usually choosing the “Show Default Axis” for the “Primary Horizontal Axis” and “Primary Vertical Axis” is best. You may wish to choose “More Primary Horizontal Axis Options” or “More Primary Vertical Axis Options” - “Axis options” will you allow to change the axis interval. - “Number” will allow you the number display (perhaps to communicate SF properly).3Data Labels Unless there is particularly instructive reason for including data labels, the “None” option should be applied. Otherwise your plot is likely to be “too busy”. Gridlines Gridlines are rarely necessary. (Your reader is very unlikely to use them.) Maybe major gridlines would be useful once in a while. Using minor gridlines is almost always distracting. Get rid of gridlines unless you have an argument for using them. Options for each of the plot elements change also be changes by right clicking on the elements and selecting “Format _________”. Moving Plot to its Own Worksheet. Sometimes it will be convenient to move your chart to a new worksheet to fit the chart on an 8½ × 11 paper. 1. Select plot by clicking on it. 2. Select “Design” tab. 3. At the far right of the toolbar, click on “Move Chart” 4. Select “New Sheet”. 5. Click “OK”. Page Layout Options The “Page Layout” tab allows you to change the orientation or size of the plot. We’ll almost always prefer landscape plot. Saving the spreadsheet Go the “Office button” in the far left-hand corner of the Excel window. Select “Save” or “Save As” — Select the appropriate storage device. Use a file name that is descriptive and will allow you to later find your work. Printing the spreadsheet or the graph Go the “Office button” in the far left-hand corner of the Excel window. Select to select “Print” Æ “Print Preview”. If the plot looks good, select “Print” Æ “Print”.4Practice selecting each separate component and getting the menu available for each component. If you have done so already, go ahead and customize your graph for: Text including title, axis labels and legends (content, font size, font style, location) Axes (range, spacing of numbers, major tick marks and minor tick marks) The graph (symbol only, line only, symbol color, line color, line type, symbol type and size) Be sure to save your work to your flash drive or MyFolder.. B. Simple graph, two sets of x, y data A single spread sheet can contain more than one set of data to be plotted. Each set is known as a series. Each series can be plotted on its own graph or multiple series can be plotted on the same graph. If multiple series are plotted, the scaling of the X and Y axes will have to be big enough to handle the greatest range to be plotted. For this exercise you will be adding the data in Table 2 to the spreadsheet you created in part A. Both data sets will then be plotted on the same graph. It is possible to save more than one graph in the same spreadsheet. For this problem you could either add the second plot to the first graph and save it under the same name or a different name or you can create a new chart from scratch with a new name. We will use this second approach for this exercise. Table-2 - SO2(g) Density Data Pressure (Atm) Density (g/L) 1.00 2.93 0.50 1.45 0.10 0.286 0.010 0.0286 0.0010 0.00286 0.00010 0.0002865 Figure 1 - Spreadsheet setup for two data sets A B C D E 1 Pressure Acetic acid (atm) Density HoAc (g/L) Pressure SO2 (atm) Density SO2 (g/L) 2 3 0.04 0.0939 1 2.93 4 0.06 0.145 0.5 1.45 5 0.08 0.198 0.1 0.286 6 0.1 0.253 0.01 0.0286 7