Formulas
Aside from organizing data efficiently, spreadsheets are great for manipulating data. You can use “formulas” to do mathematics like addition and multiplication, take statistics such as averages, manipulate text, and a lot more. Formulas allow you to calculate and analyze thousands of cells almost instantly. Done well, it can seem a lot like magic!
Using formulas may sound complicated at first, but it’s really pretty easy. First, you tell the cell it is getting a formula by using the = key. Then you use tell the cell what to display by using cell addresses, math symbols (such as + or *), and functions. For example, to add the contents of cells A1 and A2, you can use the formula = A1 + A2. To add 5 to what is in cell B15, you would use the formula = B15 + 5.
Some of the magic of spreadsheets happens when you start using “functions,” or pre-defined operations (such as AVERAGE, to average numbers). Take a look at some of the functions available using the Sum icon (∑). There are hundreds!
Most functions need some information, and they will tell you what information they need if you hover over them. You tell the formula what to information act on by putting it in parentheses. If you want to average the contents of cells A1 to A5, you can use the formula = AVERAGE(A1:A5). The equals sign tells the spreadsheet a formula is coming, AVERAGE is the formula used to average numbers, and the address range in parentheses tells the spreadsheat what to use the function on.
Functions and text
Functions aren't just for numbers! There are also functions for dealing with text. For example, you can combine text using CONCATENATE (or its friendlier shortcut '&'). The formula ="CATS" & " " & "AND" & "DOGS" would produce CATS AND DOGS. There are also functions to take just the leftmost (or rightmost) characters of a cell. Or split the contents of a cell into two separate cells. There are also functions to deal with time, dates, web information, and all kinds of things. Fortunately, a dozen or so functions will probably do almost everything you need. See the list below for the ones I find most useful.
Copying Formulas
Once you've got a formula written, you can copy the formula to more cells to automatically calculate the values for those cells as well. That means you can write a formula once, and use it to instantly perform hundreds of operations at once!
By default, when you copy a formula, the formula changes some values. So if your formula in A3 is = A1 + A2, when it is copied to B3, the formula changes to = B1 + B2. When it is copied to C3, the formula becomes = C1 + C2. That saves a lot of time. If you want a cell to remain the same when it is copied, you can use the dollar sign. For example, = B1/$A$1, when copied to a column of numbers, would produce B1/A1, C1/A1, D1/A1, and so on. Without the dollar sign, the same formula would produce B1/A1, C1/B1, D1/C1, and so on. Sometimes this can get a little tricky, but it gets easier with just a little practice.
