STEM Education

### A Beginner's Guide to Spreadsheets

Elliot Shiro
December 27, 2022

Spreadsheets are a key tool in many fields. In most cases, when there are large amounts of numbers to do calculations with, any spreadsheet program is required. Today we are going to go over the basics of spreadsheets using google sheets. If at any point you feel lost, need more explanation or want to play around with spreadsheets yourself, feel free to refer to our example spreadsheet and make a copy of it by clicking File, then Make a copy. Navigate through the many examples using the page selector at the bottom of the screen.

If you don’t already know, a new sheet can be made by going to drive.google.com and pressing the big button that says new in the top left and selecting Google Sheets. Most of what is in this tutorial also applies to Microsoft Excel.

Let's start with the basics. A spreadsheet is a stack of little boxes called cells. These cells are divided into rows (horizontal) and columns (vertical). Every column is lettered A-Z, and every row is numbered (1, 2, 3, etc…). This way, every cell has its own unique “name,” its column’s letter followed by its row’s number. For example, the cell in the first row and first column (top left) is called A1. In the example picture, the cell A1 is selected, so it says A1 above the spreadsheet. Directly next to it is the content of the cell; in this case, the cell has some text in it.

There are two ways of typing into a cell. The first way is to just type in text or numbers the way you would expect. The second way is to start by typing =. This will make everything you type after it a formula. There are many ways to utilize this. The simplest way to use this feature is to do simple arithmetic. Reference the “names” of the cell separated by an operator to perform arithmetic. + for addition, - for subtraction, * for multiplication and / for division and ^ for exponents. See below for examples

These formulas also follow the order of PEMDAS which is: parentheses, exponents, multiplication and division (from left to right), addition and subtraction (from left to right). Notice that in the example below, a combination of cell names and real numbers are used.

Another thing you can do with formulas is call functions. Functions are like pre-made formulas that google sheets offers you. This is where the differences between sheets and excel start. In sheets, if you want to quickly add up a range of numbers, you start with = and then the function name, which in this case is: SUM followed by parentheses ().

There are two ways of putting numbers into a function. The first way is by listing every cell you want to be included separated by commas. As shown here:

That method is used if there are other numbers in the way because the faster method is to just select a range of cells. This method only works if you want to select all the data in a rectangular shape. All you have to do is type the “name” of the first cell (top left) and last cell (bottom right). Like this:

Also works for just one row or column like this:

Other useful functions are:

=COUNT()

This function will tell you how many cells with different number values there are in your range.

=COUNTA()

COUNTA works the same as count except it includes cells with text as well.

=AVERAGE()

Finds the average value of all the cells selected. It does this by adding all the values together and dividing by the number of cells.

=MIN()

Finds the smallest value in the range

=MAX()

Finds the largest value in the range

Now, let's apply some of those formulas to football. Let’s say that you have a list of 9 offensive passing plays; there are many formulas you could use to analyze the data set. Here we have four examples.

The first formula you could use is the sum formula to calculate the total yards thrown in the 9 plays. Just like before, select the cell in which you want the final number to be shown, and type =SUM followed by the range of cells you want to be added up (in this case, D2:D10), and press enter. The number will be automatically calculated.

By just replacing the word SUM in this formula there are infinite other possibilities for analyzing the data. Below arecjust  a few examples we came up with.

The average pass length using the AVERAGE formula

The shortest pass using the MIN formula

And the longest pass using the MAX formula

Feel to go to the Football Application page of the example spreadsheet and try other formulas to best analyze the data provided