Building Excel Formulas

Formula Basics
Formulas must begin with the equal sign (=); that’s how Excel distinguishes them from values and labels.

You can construct formulas that subtract, multiply, divide, and exponentiate. The
– symbol means subtraction, the * means multiplication, the / means division, and the ^
means exponential operation.

Entering Formulas in Excel
Excel calculates formulas automatically. You enter them in a worksheet cell in the same way
as you do with labels and values. In the cell, however, Excel displays not the formula, but
its result. For example, if you enter a formula that says to add 4 and 2, Excel retains the formula and displays it in the formula bar when the cell is selected, but Excel displays the result, 6, in the worksheet itself.

Some Examples of Simple Excel Formulas

The points covered in the preceding paragraphs may not seem very powerful. But by applying the information, you can actually build a variety of useful Excel formulas. For example, if you enter the formula =4+2 into a cell, Excel returns the formula result 6.

Similarly, enter the formula =4-2 and Excel returns 2.

Enter the formula =4*2 and Excel returns 8.

Enter the formula =4/2 and Excel returns 2.

Finally, enter the formula =4^2 and Excel returns 16.

Building More Complicated Excel Formulas

To build more complicated formulas, you need to recognize the standard rules of operator precedence: Excel first performs exponential operations, then multiplication and division operations, and finally, addition and subtraction.

For example, in the equation =1+2*3^4, Excel first raises 3 to the fourth power to get 81. It then multiplies this value by 2 to get 162. Finally, it adds 1 to this value to get 163.

To override these rules, you must use parentheses. You can use multiple sets of parentheses
in a formula as need be. Excel first performs the function in the innermost set of parentheses.

For example, enter the formula =1+2*3^4 and Excel returns 163.

Enter the formula =(1+2)*3^4 and Excel returns 243.

And a final example: Enter the formula =((1+2)*3)^4 and Excel returns 6561.

Using Cell References in Formulas

Suppose that you were building a budgeting worksheet and wanted to calculate the total of a handful of values.

You could total the budgeted expenses by entering a formula such as =500+50+500+2000+250 into a particular cell--say cell C7.

There is, however, a practical problem with this approach: You would need to rewrite the formula each time any of the values changed.

Because this approach is unwieldy, Excel also allows you to use cell references in formulas. When
a formula includes a cell reference, Excel uses the value that cell contains.

For example, suppose that cells C1, C2, C3, C4 and C5 contained the values 500, 50, 500, 2000 and 250 and that you want to total these values and place the total value into cell C7.

You could enter the formula =500+50+500+2000+250 into cell C7.

Alternatively, you can instead enter the formula =C1+C2+C3+C4+C5 into cell C7.

Note, then, that to reference a cell on the same worksheet as the formula, you need to supply only the
column-letter-and-row-number cell reference. To reference cell C1 on the same worksheet, for example, you enter C1.

Advanced Cell Referencing Skills

Many people work only with one worksheet Excel files. But you may be interested to know that you can also reference cells on other worksheets. To reference a cell on another worksheet in the same workbook, however, you need to precede the cell reference with the name of the worksheet and an exclamation point symbol. To reference cell C1 on the worksheet named Sheet2, for example, you enter Sheet2!C1.

You can reference cells in other workbooks, too. To do this most easily, open the other workbooks, begin building your formula as described earlier in this chapter, and then click the other workbook cell you want to reference at the point you want to include the reference. Excel then writes the full cell reference for you, which includes the workbook name. An external reference to cell C1 on the worksheet named Sheet2 in the workbook named Budget might be written as =[Budget.xls]Sheet2!$C$1.