Lab Part 2

Principles of Chemistry I Laboratory Montgomery College Rockville Campus
Making Graphs for Scientific Reports

The easiest way to make graphs for a scientific report is by using a spreadsheet program like Excel. Free
spreadsheet programs can be obtained from Google Docs or openoffice.org.
These instructions are for Excel on a PC, but Excel on a Mac and other spreadsheet programs should be
very similar. Simply browse through the menus and options in your program until you find what you
need. You can also do a web search to find tutorials and answers to your questions. For in-person help,
see the tutors in the Math/Science Center. You are also welcome to come to office hours or make an
appointment with me for help.
Let’s start by making a graph with data from a Boyle’s Law (Pressure-Volume) experiment. Open a new
spreadsheet and enter your data into two columns: one column for Volume and one column for
Pressure. Be sure to include units!
Highlight cells containing the data and right click. This will give you a menu where you can choose
Format Cells. You can then format the cells as numbers (with a specific number of decimal places), text,
scientific notation, etc. For this example, I formatted the cells containing the data as Number with one
decimal place.
1 of 7
Highlight the data and go to the top menu. Choose Insert  Scatter from the main menu to insert a
scatter plot. A scatter plot should appear on your screen. Be careful not to pick a line graph or any other
kind of graph. You want a scatter plot so that you can do a regression fit later.
The plot shown here has Volume (first column) on the x-axis and Pressure (second column) on the y-axis.
This is the default for Excel. The first column of data will be the y-axis and the second column will be the
x-axis. You can change the axes later if you need to.
Look at the top right area of the menu – you will see a green tab labeled Chart Tools. Use Layout  Axis
Titles to position and name the horizontal and vertical axes. Be sure to include units.
2 of 7
Use Legend to edit or delete the legend. For simple graphs like this you don’t need a legend. Please note
that you do not need a title for the graph if you are including a caption in your report. Click the graph
title that automatically appears when the graph is generated and delete it. Graphs in scientific reports
don’t usually have titles on them, but they do have detailed captions.
3 of 7
Now you have a graph of your data, and you can try to fit the data to a trendline. Click one of the data
points and then choose:
Chart Tools  Layout  Trendline  Linear

or
Chart Tools  Layout  Trendline  More Trendline Options  Power
Here, I chose Trendline   More Trendline Options  Power and a black line appeared on the graph.
To get the equation for this trendline, right click the black line itself. Choose Format Trendline. In the
box that pops up, choose the type of fit you want (linear, power, etc.) and check the box for Display
Equation and Display R-squared. The equation and R-squared value will now appear on the chart.
Exponential fits are great for some data, but for ideal gases, pressure and volume are inversely
proportional. It is therefore useful to plot P vs. 1/V. To get started with this new plot, create a new
column of data for 1/V.
Select the first cell of your 1/V column. Here it is cell E2.
Calculate the value for 1/V by typing in: = 1/C2
The “=” sign tells Excel to do a calculation.
For =1/C2, the program will divide 1 by the value in cell C2.
E2 should now have the value of 0.05 in it. This is 1 divided by 20. You will need to select these cells,
4 of 7
format them as Number, and choose the appropriate number of decimal places. I chose four decimal
places for this example.
Copy cell E2 and paste it in E3, E4, E5, E6, E7, and E8.
The program copies the formula and does the 1/V calculation for each of the cells.
Now make a scatter plot of P vs. 1/V. Select the pressure data points in cells D2 through D8. Hold the
Ctrl key and select the 1/volume data points in cells E2 through E8. Your table should now have those
values highlighted.
5 of 7
As before, go to the top menu choose Insert  Scatter  Scatter Plot. The linear relationship between
P and 1/V is clear from this plot.
Take a look at the axes of this graph, though. By convention, the x-axis is usually the independent
variable (Volume or 1/Volume) and the y-axis is the dependent variable (Pressure). There are various
ways to switch the axes within the program (Chart Tools  Design  Select Data).
In this case, for simplicity, I copied the P (atm) column and pasted it in the column to the right of 1/V.
Then, when I made a scatter plot 1/V was the x-axis and P was the y-axis.
Just like before, go the right side of the menu bar and choose Layout  Trendline  Linear Trendline.
Right click on the line generated and choose Format Trendline. Check the boxes for Display Equation
and Display R-squared value. These should appear on your graph. The closer you R2 value is to 1.000,
the better your data fit the equation of the line. You should always aim for an R2 value of 0.98 or 0.99.
6 of 7
Make sure the axes of the graph are labeled properly. Don’t forget to include units!
You can right click on many parts of the graph, including the axes to edit/format them. You can control
the number of decimal places, the major/minor tick marks, and many other aspects of the plot. Play
around with it until you get something you like.
You now have a plot showing the linear relationship between P and 1/V and the equation of a line
relating P to 1/V. Remember that the equation of a line has the general format:
y = mx + b
where m is the slope and b is the y-intercept.
For the plot above:

y = 9.1983x + 0.0685
y = Pressure (atm)
x = 1/Volume (mL-1)
m = slope = 9.1983
b = y-intercept = 0.0685
7 of 7

Lab Part 2

Uploaded by

Copyright:

Available Formats

Lab Part 2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lab Part 2

Uploaded by

Copyright:

Available Formats

Principles of Chemistry I Laboratory Montgomery College Rockville Campus

Making Graphs for Scientific Reports

Chart Tools  Layout  Trendline  Linear

where m is the slope and b is the y-intercept.

For the plot above:

You might also like