Graphing with Excel

We will learn to graph data using the chart wizard. We will label axes, insert a trendline (best fit line)

and obtain an equation for the best fit line.

Part 1

We will graph simple (X,Y) data points

To do this we need two columns x and y where the x column is always to the left of the y column

label the columns x and y and put the data in so that the columns look like:

To graph this left click on x and drag the pointer across to y and down to

cover x value 4 and y value 16.

The two columns and 5 rows will be highlighted.

With the data highlighted go to the toolbar and find the chart wizard (little chart)

Select the Chart Wizard.

On page one of the chart wizard, select a scatter plot (XY Scatter) with no lines. Select "Next"

On page two of chart wizard just select "Next" and go to page three.

On page three we can label our graph and make it usable.

Put in a chart title (Graphing with Excel)

Value (X) axis is what we use to label the x axis (X axis)

Value (Y) axis is what we use to label the y axis (Y axis)

Select legend tab and turn off "show legend"

Select gridlines tab and add major and minor gridlines

select "Next"

On page four just select "Finish" and your chart will appear on your spreadsheet.

You can grab graph your chart and move it with your mouse.

Part 2

This data is not linear. Take the square root of the y data and regraph.

sqrt y

We want to graph columns 1 and 3. (Remember the x axis must always be on the left of the y axis.)

Left click on x and drag down to 4. Release the left button and hit and hold down the control button.

With the ctrl button down, left click on sqrt y and drag down to 4. Columns 1 and 3 are now selected.

Go to chart wizard and graph just like in part : except label the graph SQRT Y

We now have a nice linear graph. Now put in a best fit line (Called a trendline here).

Left click exactly on a data point. All of the data points should turn yellow.

Immediately right click and some options should become available, Select "Add a trendline"

On the page with the "Type" tab. Select "linear". Select the "Options" tab.

Select "Display the equation on chart". Select "OK".

Your graph should like the one above with the line and the "y=x" equation by the line.

Select the "y=x" equation and click and drag it out up by the title.

Part 3:

Real data

We will graph vapor pressure and temperature data to find the heat of vaporization of a liquid.

Data: For nitrobenzene

T (deg C)

VP (torr)

84.9

115.4

139.9

100

185.8

400

In order to graph this data we need to convert the Temperture to Kelvins and take 1/T. .

Take the LN of the VP

Put the data in two new columns (see below) remember to put the x axis on the left.

1/T (K)

LN (VP)

0.0027941

2.302585

0.0025747

3.688879

0.0024219

4.60517

0.0021796

5.991465

Graph the data just like in part 1 using "Vapor Pressure of Nitrobenzene" as the title.

1/T (Kelvins) for the x axis.

LN (VP) for the y axis. You do not need gridlines for this graph.

The graph is really ugly with wasted space between x = 0 and x = .002.

We need to remove the excess space and add a trendline.

To remove the excess space, move the pointer to the x axis (it will say "value (X) axis.")

Right click on the x axis and go to "Format axis". Select the "Scale" tab.

Change the minimum from 0 to .002

Your graph should now look like the graph below.

(Add a trendline and display the equation on the chart.)

The last thing we want to do is calculate the heat of vaporization for nitrobenzene. Remember that

the slope ( -5999) = - deltaH/R

delta H = - ( - 5999)(8.314) = 49876 J/mole or 49.9 kJ/mole