Excel Basics: Formulas and Functions
Using a spreadsheet to organize tables of numbers and text is just scratching the surface of Excel's potential. The real power is doing calculations on the entries automatically.
Formulas: Basic Arithmetic
Formulas begin with an equals sign, and refer to cells on the worksheet by their column letter and row number. The upper left-hand cell is cell "A1"; the lower right-hand cell is "IV65536".
Add, subtract, multiply, divide
Add C6 to B6 Subtract C6 from B6 Multiply B6 by C6 Divide B6 by C6 =B6+C6 =B6-C6 =B6*C6 =B6/C6
You can work on more than two cells at a time: =B6+C6-D6+E6 Use parentheses to group things together: =B6*(C6-D6+E6)
Copying simple formulas
When working with a whole table of things, you only have to type the formula once for the first row or column of the table, then copy the formula for the others. • • Edit -> Copy, then Edit -> Paste Edit -> Fill -> Down or Right
Excel automatically adjusts all the cell addresses for their new location.
Absolute addresses
Sometimes you want to refer to a "constant" cell in formulas; then you need an absolute address. Put a dollar sign in front of the column letter and row number: =E21*D6 becomes =$E$21*D6 Then Excel will leave that address alone when it's copied. Why two dollar signs? Because Excel can also have mixed addresses, where it won't change the column letter (like $E21) or the row number (like E$21) when the formula is copied but will change the other. While entering a cell address, pressing will cycle through the possibilities.
Functions: Mathemagical Black Boxes
Functions are tools to be used in formulas. Think of them as a black box: put something in one end of the box and something else comes out the other end. There are hundreds of functions, for doing things from trigonometry to text manipulation to statistics to looking
up values in a table. You can see the whole selection with the Insert -> Function command. Functions can be used in formulas just like numbers or cell addresses.
Parameters in parentheses
The things we put into the function are called parameters. They show up after the function name in parentheses. If the function wants or allows more than one parameter, you separate them with commas.
Ranges of cells
Some functions operate on groups of cells. We describe those groups in the formula by their upper left-hand and lower right-hand cells separated by a colon like this: B1:D15 (you can also enter this as B1.D15) When you're entering a range you can also click, hold and drag on the range of cells.
SUM, AVERAGE
To add a range of cells, use =SUM(B1:B15) To average the values in a range of cells, use =AVERAGE(B1:B15)
Making decisions with IF
The IF function lets you choose one formula if some condition is true, and another formula if the condition is false. The function looks like this: =IF(condition,truevalue,falsevalue)
Conditions
These are just a few of the possibilities, . D6 is greater than zero B6 is greater than or equal to C6 A6 contains the text "Male" D6>0 B6>=C6 A6="Male"
True and false choices
The "truevalue" and "falsevalue" choices can be numbers, formulas or text in quotes. (Formulas don't have to start with an equals sign here.)
Referencing cells from another sheet or file
Cells from another sheet in the same worksheet can be used in a formula by beginning the cell reference with the name of the sheet, followed by the address or range: =Sheet2!B6:B17 Cells from another file work the same way, adding the filename in front of the sheet name in square brackets: =[FirstQuarter2007.xls]Sheet1!$D$2
2