LabPart2Graphing Supplement Google Sheets

Making Graphs for Scientific Reports
The easiest way to make graphs for a scientific report is by using a spreadsheet program such as
Microsoft Excel or Google Sheets. The following instructions are for Google Sheets (available online at
https://docs.google.com/spreadsheets), but other spreadsheet programs should be very similar. Simply
browse through the menus and options in your program until you find what you need. Note, however,
that the web-based version of Excel does not include regression analyses, which you will need for your
graphs. 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 attend office hours or make an
appointment with your instructor for help.
Let’s start by making a graph with data from a Boyle’s Law experiment, in which the pressure of a
sample of gas changes with the volume of its container. Your experiment may not be the same as the
example, but you can use the same techniques to graph your own data.
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! You may add or remove decimal places by pressing the
buttons in the menu bar.
Highlight the data and go to the top menu. Choose Insert  Chart or simply click the button in the
menu bar. A graph will appear on your spreadsheet, with the chart editor open on the right. If the chart
editor is missing, you may double-click on the graph to reopen it. Be certain that “Scatter chart” is
selected under Chart Type. You want a scatter plot so that you can do a regression fit later.
1 of 4
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 Google Sheets. 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.
You can customize your graph using the Customize portion of the chart editor. Click Customize  Chart
& axis titles to rename the horizontal and vertical axes if you did not include them in your data range.
Be sure to include units. Please note that you do not need a title for the graph since you will be
including a caption in your report. You can delete the title by deleting the title text in the Chart & axis
titles area or by clicking and deleting the title directly on the graph. Graphs in scientific reports don’t
usually have titles on them, but they do have detailed captions.
Now you have a graph of your data, and you can try to fit the data to a trendline. Select Chart editor 
Series and select the Trendline checkbox. A dropdown menu will appear that will allow you to select the
type of curve fit you desire. For now, we will choose Power. For scientific analysis, it is helpful to know
the equation for your curve and the goodness of your curve fit. In the Label dropdown menu, select
“Use Equation,” and select the “Show R2” checkbox. The closer R2 is to 1, the better your curve fit.
It is best to not have the equation of your curve fit as the title. You may click in it and drag it to the
interior of your chart.
2 of 4
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 C2.
Calculate the value for 1/V by typing in: = 1/A2
The “=” sign tells Sheets to do a calculation. For =1/A2, the program will divide 1 by the value in cell A2.
C2 should now have the value of 0.125 in it. This is 1 divided by 8. You again can alter the number of
decimal places in the cells as appropriate.
Copy cell C2 and paste it in the remaining cells. The program copies the formula and does the 1/V
calculation for each of the cells. Alternately, you can click on the small square in the bottom right of the
cell highlight and drag it down to fill the formula into the subsequent cells automatically.
By convention, the x-axis is usually the independent variable (Volume or 1/Volume) and the y-axis is the
dependent variable (Pressure). Sheets automatically puts the data on the left of the range on the x-axis,
so if we were to plot columns B and C the axes would be switched. The simplest way to make a plot with
the correct axes is simply to copy the pressure data to column D. You can then make a scatter plot of P
vs. 1/V by highlighting the data (click on the top left cell of the range, in this case cell C1, and while
holding down the mouse button drag the mouse to the bottom right cell, D6). Repeat the steps above to
create another scatter plot. This time, select a linear curve fit rather than a power curve fit.
3 of 4
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.
The final step is to create a caption for the plot. A good caption is succinct but contains enough
information that a reader can understand the plot without referring to the main body of the text. In
Google Sheets, you can insert a text box beneath the plot by selecting Insert in the menu bar, then
scrolling down to Drawing.
The Drawing popup window will appear. Click on the button, then in the drawing window to bring
up a new text box. Enter your caption in the text box. If you need to change the size of the text, you can
do so by clicking on the three dots in the menu bar. When you have entered your caption, click “Save
and close.” The caption will appear in your spreadsheet as a movable object which you can position
beneath your plot.
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, the equation is y = 91.5x + (-3.94). y =
Pressure (atm); x = 1/Volume (mL-1); m = slope = 91.5; b = y-intercept = -3.94.
4 of 4

LabPart2Graphing Supplement Google Sheets

Uploaded by

Copyright:

Available Formats

LabPart2Graphing Supplement Google Sheets

Uploaded by

Document Information

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

LabPart2Graphing Supplement Google Sheets

Uploaded by

Copyright:

Available Formats

Making Graphs for Scientific Reports

buttons in the menu bar.

You might also like