Google Sheets - Basics of Using Formulas
Here are some basics you should know about when working with formulas in Google Sheets.
1. Formulas start with an equal sign (=)
You must use an equal sign at the beginning of your formula. Otherwise, no math will happen. You might note that the menus go gray as soon as you type the equal sign - .
2. Filling with a Formula
Just as you can "fill" for quick data entry, you can fill when using the same formula multiple times either across or down. Grab the little blue box in the lower right corner of the cell and drag either over or down to apply the formula in other rows/columns. Notice the cursor changes to a crosshair when you are filling -
Cell references are when you use a cell address to refer to the location of the wanted data instead of the actual data. Used in a formula, it allows you to do the following:
- Utilize the fill feature - each instance adjusts to the relative same cells that are referenced - in the example above, when I fill instead of copying the exact same number from J2, it makes J3 =AVERAGE(E3:G3) and J4 =AVERAGE(E4:G4), etc
- Change data only, anywhere else you have referred to the cell address auto-updates to the new data - in the example above, if I change cell G2 from 45 to 50 the AVERAGE in J2 will automatically updated to reflect this change
- Link to data in other worksheets or even other workbooks
- Refer to a range of data instead of each individual cell - in the example above, I can use =AVERAGE(E2:G2) instead of using =AVERAGE(86,86,45)
4. Selecting Sections of Data
Selections of data can be selected by identifying the cell address of the uppermost left cell, colon (:), and lowermost right cell. For example, in the graphic above, if I want to use all the Quiz score, I would refer to that data set as E2:G24, if I only wanted the first Quiz I would refer to E2:E24, etc
5. Selecting Disjoint Sections of Data
Disjoint sections of data are separated by a comma (,). For example, in the graphic above, if I wanted to use both Quiz 1 and Quiz 3, but not Quiz 2, I would refer to the data set as E2:E24,G2:G24
6. Lock to a Referred Cell
You can lock to a particular cell, row or column using a dollar sign ($). If you want it to stay in a particular row, the dollar sign is in front of the number in the cell address - for example B$1 will allow the column referenced to change (move to C, D, etc), but will lock into row 1. If you want it to stay in a particular column, the dollar sign is in front of the letter in the cell address - for example $B1 will allow the row referenced to change (move to 2, 3, etc), but will lock into column B. If you want a specific cell, you need a dollar sign before the row AND column - $B$1.
7. Useful Reference List from Google
Here is a useful reference list from Google.
