Google Sheets Tutorial
This is a tutorial for how to use Google Sheets (or Excel) for the Stony Brook Physics Introductory Labs.Hoveroverthese!
If you know how to use spreadsheets already, reading will take longer than calculating (and you're still likely to learn some new tricks in the process). If not, it'll take a bit longer, but that time will be well-spent - you'll more than make it up in efficiency increases on later labs.
Setup: Making Your Own Copy of the Data Sheet
Begin by opening the template for the tutorial, available here.
The first step, before you can even make edits, is to make a copy for yourself.1 To do this, click on "File->Make a Copy." This will make a copy in your own Google Drive.1
Modifications to work in Excel (click to expand)
In general, we standardize on Google Sheets in this class, because (at a minimum) every student has a Google account through the university. Plus, everything is already in the cloud, so there is no risk if something happens to your computer.
However, this tutorial can be done entirely in Excel (or any other spreadsheet program) instead of Google Sheets, if you like. To get this document in Excel, click "File->Download As->Microsoft Excel (.xlsx)".
The only major caveat is in Part III: LINEST is more difficult to use in Excel than Google Sheets, in two ways:
- You need to press "Ctrl-Shift-Enter" instead of just "Enter" when you type it in, because it's an "array formula."
- It won't automatically update when you change your base data, unlike other formulas (or LINEST in Google Sheets).
For these reasons (along with a few others), we recommend doing at least this tutorial in Google Sheets, to make your life easier. If you want to work in Excel, though, everything else should work identically.
To check that you have made a copy, try editing the title (to something of your preference). If you can do so, it's in your drive.
Part I: Using Basic Formulas
We're going to begin by learning how to use Sheets as a simple calculator.
Why to use Google Sheets (click to expand)
While you can, in principle, take your numbers, do your calculations by hand, and type those numbers back in, you'll find your life easier if you learn to do everything in Sheets instead. Some advantages:
- Reduced chance of typos: if you're not copying numbers back and forth, there's less chance of a mistyped digit anywhere.
- Higher precision: Since Google Sheets doesn't round until around the sixteenth decimal place (by default), you won't have rounding in middle steps. This prevents errors due to rounding from seeping into your results.
- Easier-to-trace logic: if you write your formulas, your TA can more easily see what you did than by looking at your calculator screen (especially seeing as you regularly erase your calculator screen!). This can often enable them to help you more effectively.
- Automatic updating: as we'll see, you don't have to "re-calculate" everything if you make a mistake - Sheets will do that for you.
- Simplifying repeated computations: as we'll see, Sheets has several tools that reduce the extent to which you'll have to do the same (trivial) calculation over and over again.
- (Potentially) less writing: Follow your TA's guidance on this one, but typically, your TA won't need written calculations if you do your calculations in Sheets and upload that to Blackboard (in a .xls(x) format), because they can directly see the formulas you typed in.
With that said, let's get started.
Begin by looking to the letters at the top and numbers at the left of your data table - these specify a unique reference for every cell in your sheet. For instance, the cell which currently says "Part I" is cell B1.
Let's begin by supposing that our quantity A has the value 2, and B has the value 5. Record these values.21
Now, let's say we want to calculate A+B. The way to do this, in this case, is to type "=B3+C3" into the relevant cell (D3). Press Enter, and it should automatically fill with the sum of our A and B values.
Let's break that formula down into its components:
- The "=" sign at the beginning tells Sheets that we're doing a calculation; you have to do that whenever you want to calculate something.
- The "B3" and "C3" are the names that Sheets knows for the cells that contain the values of our quantities A and B.2
- The "+" tells it to add those quantities, of course.
Fill out the rest of the cells in this row, up to "A^2"; they should all be straightforward extensions of the above logic.3
Alright, we've got a bunch of calculations done now. But suppose now we realize that, oops, we mis-measured (or mis-calculated) B, which should actually be 6, not 5!
Fortunately, we're using Sheets for our calculations, not our calculator. Change the value of B (in cell C3) to 6, and note what happens to the next five cells. Everything else automatically updates!4
Now, we want to evaluate a square root, in cell I3. The way to do this is (as written in the cell header) using the function SQRT(...), which evaluates to the square root of whatever is inside the parentheses.5
One last calculation we have to do: let's multiply A by π. You could approximate pi with 3.14 (or 3.14159...), but there's a better way. Simply write "PI()" (extra parentheses required) and Excel will use its value for π, which gives you lots of digits very easily!62
Part II: Using Autofill
Now, we're going to learn the feature that really makes Sheets (or Excel) shine above and beyond your calculator: doing repeated computation.
We'll begin with some quick data entry. In our hypothetical experiment, we varied the quantity C from one to ten (in that order), so we want to enter the numbers 1-10 into our data table.
Let's not do this entirely by hand, though - while that's fine for two data points, you'd hate to do it for 100, or when you're not dealing with one-digit integers.
First, type in 1 and 2 into B6 and B7. This is sufficient for Sheets to be able to figure out the pattern we're making.
Highlight those two cells. Click and drag the blue square at the bottom-right of that highlighted region down to B15, and watch as your cells are automatically populated with increasing numbers 3-10!3
This "auto-fill" is able to recognize a reasonable number of patterns (although nothing too exotic). We're not going to primarily be using it with numbers, though - we'll be doing it with formulas.
Let's apply autofill to compute C^3. Begin by typing in the first cell, in D6, as before.7 Then, instead of clicking and dragging, double-click on the same blue square. This autofills down exactly as far as the previous column is filled (without overwriting anything).
Calculate the next column with autofill on your own (either with click-and-drag or double-click).
Now, hopefully you're used to using the "standard" autofill, so now let's break it (and then fix it) by trying to refer to a fixed cell (the value for B).
Begin by trying to evaluate column F exactly as you did before - enter in the formula into F6 [be careful about parentheses and PEMDAS!], and double-click or drag to autofill the rest of the column. You should start to see problems, if you look at your results! What happened?
Well, let's look at our second entry, F7 (which should yield zero). You can immediately see the issue - our value for B, which was supposed to stay fixed as C3, automatically changed to follow the "pattern" to C4!
Let's fix that. Delete that autofill, leaving only cell F6. In that cell, change the "C3" to a "C$3"; then, auto-fill as before.
Now, everything should work. The dollar sign told Google Sheets to "hold the 3 constant."8
This is a key trick that'll make your life easier, and is essentially the last trick you need to automate most of your calculations!
Part III: Using LINEST
Unfortunately, there's one last thing we do in our labs that we don't have yet: using the plotting tool to make a best-fit line.
While Google Sheets' plotting utilities, unfortuntely, lack features we need (and Excel's are lackluster on similar grounds), we can still get a number for our best fit line straight out of our data table, if we want, with the advanced function LINEST.
This formula will automatically fill up a five-column-high, two-column-wide region. Begin by clicking the top-left cell in the table for this part, cell I6.
Begin with "=LINEST(" to fill out our formula. Either Sheets will come up with a pop-up to help you, or a little blue question mark will appear; click on that question mark to make the help appear.
This tells you the details of the function (what goes where), which will be helpful if you ever aren't sure which input was "y data" and which input was "x data." Here, we'll walk you through it, though.
The first argument is the y-data, for which we'll use the data of D. Highlight cells C6 through C15 (which should cause "C6:C15" to be entered into your formula; or you can type that directly).
Type a comma, and then our x-data, "B6:B15" to have all data input. (Sheets' fit doesn't use error bars.
For the third argument (after another comma), type "TRUE" (or "1") in order for it to fit a line of the form "y=mx+b" (instead of just "y=mx").
For the fourth and final argument (after another comma), type "TRUE" (or "1") again, in order to have it tell you the uncertainties in your slope and intercept. Close the parenthesis.
Your text should now read "=LINEST(C6:C15,B6:B15,TRUE,TRUE)" if you entered it correctly. If you are working in Excel, type "Ctrl-Shift-Enter"; if in Sheets, just type enter as usual.
You should observe (if you typed it correctly) that the entire data table fills out immediately, with a variety of numbers. The important numbers for our purposes are the first two rows, which display the slope and intercept and their uncertainty.
Beyond that are a variety of statistical measures of some interest at levels beyond this class. You might try looking at the first column, third row, which contains the "R^2" value (or "correlation coefficient"). This quantity will be close to 1 for very linearly-correlated data, and close to 0 for not-linearly-correlated data. You can, if you like, use this as a metric for seeing if your data is at least reasonable (in addition to the slope and/or intercept themselves).
Now, for the unfortunate bit for PHY133/134 students: the plotting tool does something statistically more detailed than this linear fit, and so we'd prefer you to use those values over the LINEST ones, ultimately. It'll still give a reasonable first estimate, so you can still easily see if your data is reasonable (during class time, without needing to copy-paste over all your numbers), but for your final results, use the values the plotting tool gives you.
[PHY121/122 students, your plotting tool is simpler, and should match LINEST exactly, to within rounding. Hence, you can use LINEST as your exact value - although that doesn't excuse you from making the plots themselves!]
Part IV: Drop-Downs [Data Validation]
Not all observations can be recorded as numbers - some are text, because they are qualitative.
In the interest of standardizing, our text answers on data sheets are generally in a "drop-down" format. The cells in this part have a little arrow on them; clicking on that arrow indicates a few specific answers ("No" and "Yes").
If you feel that the answers in any drop-down do not contain your true observation, feel free to type something else (it will let you, it will just flag the cell to make sure you know you did that).
Choose appropriate answers from the drop-down for this part (see the Guide to Uncertainty Propagation and Error Analysis if you need more information on how to compare numbers with uncertainties).
Here's a quick review of the key points this tutorial teaches:
- To type a formula into a cell, begin your cell with an equals sign, followed by the formula. To reference another cell, use the letter and number corresponding to its location.
- To automatically fill out a column, either click-and-drag or double-click. Use dollar signs to make something not change.
- To get a best-fit line automatically, use the function LINEST.
- Some cells (with only a few answers) will have drop-downs for you. Click and select from the standard set of options.
There's more to using Google Sheets (and/or Excel) effectively, but you can learn more as you go along. Happy computing!
For your convenience, we color-code all the cells in our data sheet. The goal here is to make it easy for you to understand the "flow" of the experiment just by looking at the data sheet.
The meanings of the different colors are as follows:
- Gray cells are header cells (for names of quantities or their units). You are responsible for filling out the relevant cells with the units you are using.
- Yellow represents raw data - the kind of things that you should directly measure/observe. You need this before you leave lab.
- Blue represents calculated quantities - i.e., they should be filled with a formula, not with a direct measurement.
- Green represents quantities/formulas that are given to you. You don't need to edit these, unless your TA says otherwiseREPLACE.
- Red represents unneeded cells, which you can just leave blank. (They show uncertainties we're not using, etc.)
- Orange represents results pulled directly from plots (or LINEST).
- Purple represents final results of our experiment (if calculated), including but not limited to questions about agreement of results.
- Pink (only used in the Plotting Tool) is used to set certain "options" (things you have a choice about).
Here's a little toy exercise if you want more practice:
- Begin by extending the "number" down a significant degree - say, 100 terms.
- Then, try calculating the Fibonacci sequence. Begin with the first two entries (1,1), then figure out how to use cell references and autofill to continue calculating arbitrarily more values. Extend this down as far as you took the previous column.
- Some theoretical mathematics tells us that the \(n\)th Fibonacci number is approximately \(\frac{1}{\sqrt{5}}\left(\frac{1+\sqrt{5}}{2}\right)^n\). Calculate this "approximate Fibonacci number" in the third column.
- In the last column, calculate the difference between the Fibonacci numbers and our approximation. Consider: how quickly does our approximate formula become precise?
Hovering over these bubbles will make a footnote pop up. Gray footnotes are citations and links to outside references.
Blue footnotes are discussions of general physics material that would break up the flow of explanation to include directly. These can be important subtleties, advanced material, historical asides, hints for questions, etc.
Yellow footnotes are details about experimental procedure or analysis. These can be reminders about how to use equipment, explanations of how to get good results, troubleshooting tips, or clarifications on details of frequent confusion.
You are "locked out" of this version, because all students see this "common" version! You'll have to do this for every data sheet template we give you.
You may have noticed, as typing, that these pieces of text became colored. You may also have noticed that the respective cells became highlighted as you typed. This is a good way to keep track of your cell references, and making sure you are using them properly!
To make your life easier: you don't always need to type the cell references. Once you've typed the "=" sign, if you click on another cell, it will automatically input that cell's coordinates into your formula. E.g., you could type "=", then click on cell B3, then type "+", then click on cell C3 to get "=B3+C3". This is often easier than tracking which cells you are using.
If you're ambitious, this automatic updating opens up a nice possibility: you can fill out all your formula bubbles before class! That lets you have all your results as soon as you take the required data, and see how your experiment is going as you work!
The capitalization is not required; lowercase is OK too (in technical terms, Google Sheets is not case-sensitive.
A bit of technical explanation: in coding, quantities like SQRT(x) or ADD(x,y) are known as functions, which take in arguments (inputs) x and y, and does something with them (outputs the square root or sum, respectively). PI() is also a function, it just takes zero inputs and outputs pi. It's a little strange, but Sheets only likes to refer to letters in three ways: as a part of a cell reference, as a "string" (i.e., as the text itself), or as a part of a function name.
One cell is actually sufficient here for Sheets to infer a pattern. Sheets views this less like "cube B6" and more like "cube the cell two to the left of me," which is exactly the pattern we want to extend. (This same assumption impacts copy-paste as well, which you can use to your advantage sometimes.)
Note: you can also click-and-drag up/left/right to autofill in those directions. If you want to hold just the column fixed in these types of extension, you can type "$C3"; if you want to keep both row and column fixed, type "$C$3" (this is most common to see).
You will need to be logged in to a Google account to do this. Your university account will suffice.
Even if you haven't used a spreadsheet program at all before, this should be fairly intuitive: in cell B3, type "2" (and press enter). Similarly write "5" in C3.
If the fact that the formatting becomes wrong (because the lowest cell loses its bottom boundary) bugs you, here's a fix: Google Sheets has an option called "paste formatting only" that you can either get with a right-click (under "Paste Special") or by using Ctrl-Alt-V instead of Ctrl-V. Just open the original data sheet template, and copy-paste formatting where required (or just copy-paste the entire sheet's formatting at the end).
This might happen if you are using some "unusual" piece of equipment, which perhaps has different parameters than a "usual" approach.
See below for explanation of the colors.
There are many more functions present in Google Sheets. To see others, see here. One worth noting is SUMSQ(...), which sums the squares of the numbers you enter; you may find this useful for error propagation.