Creating Graphs with Excel 2003

Creating Graphs with Excel 2003

These instructions are for a Windows platform. All mouse operations will be made with the left button, unless otherwise stated. Do not change any Default settings, unless otherwise stated.

Open MS OFFICE EXCEL 2003

Inputting Data

Record data from your lab notebook into the spreadsheet, using the 1st (A) column for the x data and the 2nd (B) column for the y data.

Modifying Data

In some cases, you will have to modify your raw data (e.g. take the square root). It is easier to use Excel to do identical calculations to many data points than to use a calculator. Here’s how you can add an equation to one single cell of data and then apply it to the rest of the column:

Applying a Calculation to a Column of Data

1.Select the first cell of a new empty column (e.g. if you wish to apply a calculation to A1, then choose C1 as the location for the result).

2.Click on the text box directly above the spreadsheet (to the right of the fx button).

a)To add your own formula, first type the equal sign (=). This tells Excel that you are writing an equation and not plaintext. Then, type in your equation. Say you wanted to multiply cell A1 by 3. You would type “=3*A1”. Press Enter. The calculated value should now appear in the cell.

b)To use a pre-defined formula in Excel, click on fx to the left of the text box. A window will pop up with a list of pre-defined formulas. Select the desired formula. Say you wanted to take the square root of A1. Select SQRT() from the list. Then another pop up window will ask you for the arguments. Type “A1” in the Number field. Select OK.

Applying the Calculation to the Rest of the Data in the Column

1.Click on the new cell you just created. Say this is C1. Click and hold onto C1 and drag the mouse so that you highlight the rest of the desired cells.

2.Go to Edit in the MenuBar.

3.Select Fill and Down. The entire column should now be filled.

Graphing Data

Here we show you how to graph a series of data (x,y) in Excel.

1.Use the mouse to highlight the rows and columns you wish to plot.

2.Go to Insert in the Menu Bar.

3.Select Chart. The Chart Wizard window will pop up asking for the type of chart you want. In Chem 37X, you will only need to create scatter plots.

4.Select XY (Scatter) as the Chart type.

5.Click on Next. 1Describes how to check that correct values have been plotted for X and Y Values.

Presentation of Graphical Data in CHEM 37X

The presentation of your graphical data is a critical element in your lab report. You will be graded on your data as well as the presentation of your graphs. Almost every lab will require some graphing, so it is best to learn the requirements now.

Excel will only provide you with its default formatting, so you will be required to modify how the data is displayed (e.g. axis labels, gridlines, title, etc.).

1.Returning to the Chart Wizard where we left off:

a)Click on Next again.

b)Select the Titles tab. Type in the name of the chart. Your chart should have a descriptive title, e.g., V vs h of Cylinder. Type in the labels for your axes with units, e.g. V (mL) and h (cm).

c)Select the Gridlines tab. Checkmark all 4 types of gridlines.

d)Select the Legend tab. Deselect Show legend.

2.Click on Next.

You must place the new chart onto its own page.

3.Select As new sheet. Give it a new name if you wish.

4.Click on Finish.

The above options can be accessed at any time by going to Chart on the MenuBar and selecting Chart Options. To switch between editing your numerical data and editing the graph, use the tabs found at the bottom of your screen. However, there are some more customizations that are required for the graph.

A valuable feature in Excel but not always a default is the Chart Toolbar. It is very useful for formatting the look of the graph. Move your mouse to a grey portion of the window near where the MenuBar is located. Use the Right mouse button and click on the area.

A list of toolbars available should appear (Standard, Formatting, Chart, Borders, etc.). Check that Chart has been selected. Now the ChartToolbar should be visible, usually on the left-hand side below the Menu Bar.

In order to edit one of the properties of the Chart, select the one you want with the drop-down box on the Chart Toolbar, then click on the button to the right of the drop-down box; the appropriate window will pop up.

1. Select Plot Area from the Chart Toolbar & click the button. A pop-up window will appear.

a)Select None for Area.

b)Click on OK. The plot area will now not be greyed.

2. Select Value (X) Axis from the ChartToolbar & click the button. A pop-up window will appear.

a)Click on the Patterns tab. Change the Weight of the Lines to be the 2nd heaviest line. Change the Tick mark labels to Low.

You will want to choose your axes such that you display all your data points but do not leave too much white space (little if at all).

b)Click on the Scale tab. The Minimum, Maximum, Major unit and Minor unit should not be automatically chosen; de-select those boxes.

c)Specify your own values.

i)Choose the Minimum and Maximum according to the range of your x (1 and 3 for this tutorial) and y data (4 and 10 for this tutorial).

ii)The Major unit defines the spacing of your major gridlines and should be chosen to allow for easy analysis (try 0.1 for this tutorial). The Minor unit should reflect the precision of your data (it should be 0.001 for this tutorial, but it will be too difficult to read; try 0.01 instead). The Major unit should be no smaller than 1 cm and the Minor unit no smaller than 0.1 cm.

d)Click on the Number tab. Under category, select Number. Edit the number of decimal places according to the precision of your data (choose 0.001 for this tutorial).

*The precision of your axis should reflect the most common precision of your data e.g. if your x data is 1.2, 2.4, 4.1, 8.11, then choose 1 decimal place. In some cases, you may need to use the category Scientific to display your axes in scientific notation.

e)Click on OK.

3.Repeat Steps 1 & 2 for Value (Y) Axis. However note the new values for Step 2:

c)i)The Minimum and Maximum(4 and 10 for this tutorial).

ii)The Major Unit (try 0.5 for this tutorial) and the Minor Unit (try 0.05 for this tutorial).

4.Select Value (X) Axis Major Gridlines from the ChartToolbar.

a)Click on the Patterns tab.

b)Choose the 2nd Heaviest Weight for the major gridlines.

c)Click on OK.

5.Repeat step 4 for Value (Y) Axis Major Gridlines.

6.Select Series from the ChartToolbar

a)Under the Patterns tab choose the Style and Size of the data points (Marker) with the drop-down boxes.

b)Click on OK.

7.Go to File in the MenuBar.

a)Select Page Setup.

b)Click on the Margins tab.

c)Edit all four margins, header and footer to be 0.

d)Click on OK. Your graphs should print full-size in Landscape format.

8.To add a trendline to your data points, go to Chart in the Menu Bar.

a)Select Add Trendline. In this course any trendline that you will be asked to plot will be linear so click on the Linear display.

b)Click on the Options tab. Checkmark Display equation on chart and Display R-squared value on chart.

c) Click on OK. The trendline should appear.

d)On the graph, click and drag the y=mx+b and R² equations to the top-right corner of your graph (in the blank region).2

9.Save the Excel spreadsheet. If you are in the 37X computer lab, make sure you save the file onto a memory stick or floppy.

10.Print out your graph and paste/staple it onto a left-hand side page of your notebook. Be aware that you will be producing many graphs for this course, so make sure your attached sheets are neatly placed and secure in your notebook.

Footnotes

1a)Click on the Series tab and make sure the X Values and Y Values are correctly selected. Say that there are 4 data points in column A1 that should be on the x-axis. Then X Values should read "=Sheet1:$A1:$A4".

b)If the wrong data was selected, click on the tiny button to the right of the text box. You will now have a view of your spreadsheet data and another window, the Source Data- Value, will pop up.

c)Highlight the appropriate column on the spreadsheet with the mouse (a flashing box should appear around it). The correct range will be displayed on the Source Data- Value window.

d)Click on the tiny button to the right of the text box on the Source Data-Valuewindow. The Series tab will appear again.

e)Make any other changes that you need.

f)Click on Next.

2If you cannot see the whole graph or the equation:

a)Go to View in the Menu Bar.

b)Select Zoom.

c)Select a smaller magnification.

d)Click on OK.

Pointers for Creating (Computer-Generated) Graphs in CHEM 37X

•Label the graph and axes with appropriate names (with units).

•Anything handwritten on a graph must be in pen (except for the drawing of lines).

•When asked to draw a curve through your data points, do so manually with a pencil. The curve should be smooth and does not necessarily have to pass through all the data points.

•Major and minor gridlines should be chosen to allow for ease of analysis.

•Minor gridlines must be small enough to be able to read off to the precision of the data if possible.

•The data should take up most of your page (letter-sized). If not, adjust the max/min of the axes accordingly.

•When a linear regression is requested, also print out the Chem 37X regression template where you entered your data. This must be taped into any appropriate place in your writeup.