Working with Formulas

Referring to Cells in Formulas

Whenever you want to create a formula that depends on the data in a cell, you should refer directly to the cell by its address (such as B3). While writing a formula, you can insert a cell reference by clicking in the cell or you can type the cell's address.

You can give a cell or a range of cells a name. Let's say you have a cell with total income and another with total expenses. You want to create a formula in a new cell that will calculate profit. If you name the first cell Income and the second cell Expenses, you can create a formula =Income-Expenses. Writing formulas like this makes them very easy to understand. To name a cell, select it (or a range of cells) and click the Name box, which usually shows the cell address on the Formatting toolbar at the left. Type the name and press Enter/Return.

You can refer to cells on other worksheets in the same workbook, or even to cells in other workbooks. If you are referring to a cell in another workbook, have that workbook open. Then start building your formula. When you need to refer to a cell in another worksheet or workbook, go to that cell and click the cell. Then without switching back to your original worksheet, continue writing your formula. Press Enter when you're done.

To refer to a range of cells:

Enter the reference for the cell in the upper-left corner of the range, a colon (:), and then the reference to the cell in the lower-right corner of the range. For example, A5:B20 includes all the cells from A5 through A20 and B5 through B20.

3D references fefer to the same cell or range on multiple sheets. You can use 3D references with the following Excel functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.

To create a 3D reference, following these steps:

1. In the cell where you want to enter the function, type = (an equal sign).
2. Type the name of the function, and then type an opening parenthesis.
3. Click the tab for the first worksheet you want to reference.
4. Hold down SHIFT and click the tab for the last worksheet you want to reference.
5. Select the cell or range of cells you want to reference.
6. Complete the formula.

Creating Arithmetic Formulas

You can create your own arithmetic formulas. Here's how:

1. In the cell where you want to create the formula, type = (an equal sign).
2. Either type a number or click a cell that you want to refer to.
3. Add an arithmetic operator. The operators are shown below.
4. Enter another number or click another cell.
5. Continue to build the arithmetic expression in this way.
6. Press Enter when you're done.

+ (plus sign)	Addition
– (minus sign)	Subtraction or Negation
* (asterisk)	Multiplication
/ (forward slash)	Division
% (percent sign)	Percent

Here are some examples:

=3+1

=B2-B4

=-B3*12

=C2/4

Note: Excel follows standard order of operations:

–	Negation (as in –1)
%	Percent
^	Exponentiation (powers)
* and /	Multiplication and division
+ and –	Addition and subtraction

You can add parentheses to force operations within the parentheses to be calculated together. For example, in the formula =4+5+6/3 gives the result11 because Excel first divides 6 by 3 (=2) and then adds it to 4 + 5. If what you wanted was the average of the three numbers, you should write it as follows: =(4+5+6)/3 and then Excel first adds 4+5+6 and then divides the result by 3 so that the answer is 5.

Using Excel's Functions

Excel contains a large number of functions that you can use to simplify your calculations. The functions have a certain format that you need to follow but Excel helps you out. You need to provide each function with the information it needs, called arguments, to calculate the result of the function. Follow these steps to use a function:

1. Click the cell where you want to enter the formula.
2. Click Edit Formula (the equal sign) on the formula bar.
3. Click the down arrow next to the Functions box.
4. Choose the function you want to add to the formula. If the function does not appear in the list, click More Functions for a list of additional functions. Excel opens the formula palette where you can see the arguments and structure necessary for the formula, as well as specify the data for the arguments, shown here for the MAX function.
5. Enter the arguments. To select cells directly, click the button at the right of the text box.
6. Click OK.

Using Absolute Cell References When you copy formulas from one cell to another, Excel also copies any cell references in those formulas. But Excel adjusts the cell references in the copy of the formula so that the reference has the same relative relationship to the new formula. See the example shown here.

Usually these relative references are exactly what you want. But suppose you want to copy a formula but maintain the reference to the exact same cells it originally referred to? You can do this using absolute cell references. To create an absolute cell reference, simply place a dollar sign before the row and/or the column. For example, an absolute cell reference to B3 is $B$3. (You can create partially absolute references by placing a dollar sign before only the row or only the column. Then, when you copy the formula, the part with the dollar sign stays constant, but the part without the dollar sign is adjusted like a relative reference.)

Tip: To create an absolute reference, first create the relative reference by pointing to the cell. Then select the relative reference in the formula bar and press F4. Excel adds the dollar signs for you.