Formulas are where it’s at as far as Excel is concerned. After you know how to construct formulas and constructing them is pretty easy you can put Excel to work. You can make the numbers speak to you. You turn a bunch of unruly numbers into meaningful figures and statistics. This article explains what a formula is, how to enter a formula, and how to enter a formula quickly. You also discover how to copy formulas from cell to cell. Finally, this article explains how to make use of the hundred or so functions that Excel offers.

How Formulas Work

A formula, you may recall from the sleepy hours you spent in the back of math class, is a way to calculate numbers. For example, 2+3=5 is a formula. When you enter a formula in a cell, Excel computes the formula and displays its results in the cell. Click in cell A3 and enter =2+3, for example, and Excel displays the number 5 in cell A3.

Referring to cells in formulas

As well as numbers, Excel formulas can refer to the contents of different cells. When a formula refers to a cell, the number in the cell is used to compute the formula. For example, cell A1 contains the number 2; cell A2 contains the number 3; and cell A3 contains the formula =A1+A2. As shown in cell A3, the result of the formula is 5. If you were to change the number in cell A1 from 2 to 3, the result of the formula in cell A3 (=A1+A2) becomes 6, not 5. When a formula refers to a cell and the number in a cell changes, the result of the formula changes as well. The purpose of this worksheet is to track the budget of a school’s Parent-Teacher Association. Column C lists income from different sources; column D shows what the PTA members thought income from these sources would be; and column E shows how actual income compares to projected income from the different sources. As the figures in the Actual Income column (column C) are updated, figures in the Over/Under Budget column (column E) and the Total Income row (row 8) change instantaneously. These figures change instantaneously because the formulas refer to the numbers in cells, not to unchanging numbers (known as constants). Excel is remarkably good about updating cell references in formulas when you move cells. To see how good Excel is, consider what happens to cell addresses in formulas when you delete a row in a worksheet. If a formula refers to cell C1 but you delete row B, row C becomes row B, and the value in cell C1 changes addresses from C1 to B1. You would think that references in formulas to cell C1 would be out-of-date, but you would be wrong. Excel automatically adjusts all formulas that refer to cell C1. Those formulas now refer to cell B1 instead. To display formulas in worksheet cells instead of the results of formulas, press Ctrl+` (the accent grave, the key above the Tab key on your keyboard). Press Ctrl+` to see formula results again.

Referring to formula results in formulas

Besides referring to cells with numbers in them in a cell, you can refer to formula results in a cell. The purpose of this worksheet is to track scoring by the players on a basketball team. The Totals column shows the total points each player has scored in the three games. The Average column, using the formula results in the Totals column, determines how much each player has scored on average. The Average column does that by dividing the results in column E by 3, the number of games played.

The Basics of Entering a Formula

No matter what kind of formula you enter, no matter how complex the formula is, follow these basic steps to enter it:

1. Click the cell where you want to enter the formula.

2. Click in the Formula bar.

3. Enter an equal sign (=). You must be sure to enter the equal sign before you enter a formula. Without it, Excel thinks you’re entering text, not a formula.

4. Enter the formula. For example, enter =B1*.06. Make sure that you enter all cell addresses correctly. By the way, you can enter lowercase letters in cell references. Excel changes them to uppercase when you finish entering the formula. The next section in this article explains how to enter cell addresses quickly in formulas.

5. Press Enter or click the Enter button (the green check mark). The result of the formula appears in the cell.

Speed Techniques for Entering Formulas

Entering formulas and making sure that all cell references are correct is a tedious activity, but, fortunately for you, Excel offers a few techniques to make entering formulas easier. Read on to find out how ranges make entering cell references easier and how you can enter cell references in formulas by pointing and clicking. You’ll also find instructions here for copying formulas.

Clicking cells to enter cell references

The hardest part about entering a formula is entering the cell references correctly. You have to squint to see which row and column the cell you want to refer to is in. You have to carefully type the right column letter and row number. However, instead of typing a cell reference, you can click the cell you want to refer to in a formula. As soon as you click the cell, Excel enters its address on the Formula bar. What’s more, shimmering marquee lights appear around the cell to show you which one you’re referring to in the formula.

Entering a cell range

A cell range is a line or block of cells in a worksheet. Cell ranges come in especially handy where functions are concerned (see “Working with Functions” later in this article). To create a cell range, select the cells. To identify a cell range, Excel lists the outermost cells in the range and places a colon (:) between cell addresses. You can enter cell ranges on your own without selecting cells. To do so, list the first cell in the range, enter a colon, and list the last cell. A cell range comprising cells A1, A2, A3, and A4 has this address A1:A4. A cell range comprising a block of cells from A1 to D4 has this address: A1:D4.

Naming cell ranges so that you can use them in formulas

Whether you type them yourself or drag across cells, entering cell references is a chore. Entering =C1+C2+C3+C4, for example, can cause a finger cramp. Entering =C1:C4 is no piece of cake, either. To take the tedium out of entering cell ranges in formulas, you can name cell ranges. When you want to enter a cell range in a formula, all you have to do is double-click a name in the Paste Name dialog box. Naming cell ranges has an added benefit. Cell range names must begin with a letter, backslash (\), or underscore (_). Select the cells for the range and do either of the following to name a cell range:

 -  Click in the Name Box (you’ll find it to the left of the Formula bar), enter a name for the range, and press the Enter key.

 -  Choose Insert -> Name -> Define and, in the Define Name dialog box, enter a name and click OK. To insert a range name in a formula, press F3 or choose Insert -> Name -> Paste to open the Paste Name dialog box and then doubleclick the range name. Naming cell ranges has one disadvantage, and it’s a big one: Excel doesn’t adjust the cell references when you copy a formula with a range name from one cell to another. A range name always refers to the same set of cells. Later in this article, “Copying Formulas from Cell to Cell” explains how to copy formulas.

Referring to cells in different worksheets

Excel gives you the opportunity to use data from different worksheets in a formula. If one worksheet lists sales figures from January, and the next lists sales figures from February, you can construct a “grand total” formula on either worksheet to tabulate sales in the two-month period. A reference to a cell on a different worksheet is called a 3-D reference. Construct the formula as you normally would, but when you want to refer to a cell or cell range in a different worksheet, click a worksheet tab to move to the other worksheet and select the cell or range of cells there. Without returning to the original worksheet, complete your formula in the Formula bar and press Enter. Excel returns you to the original worksheet, where you can see the results of your formula. The only things odd about constructing formulas across worksheets are the cell references. As a glance at the Formula bar tells you, cell addresses in cross-worksheet formulas list the sheet name and an exclamation point (!) as well as the cell address itself. For example, this formula in Worksheet 1 adds the number in cell A4 to the numbers in cells D5 and E5 in Worksheet 2: =A4+Sheet2!D5+Sheet2!E5 This formula in Worksheet 2 multiplies the number in cell E18 with the number in cell C15 in Worksheet 1: =E18*Sheet1!C15 This formula in Worksheet 2 finds the average of the numbers in the cell range C7:F7 in Worksheet 1: =AVERAGE(Sheet1!C7:F7)

Copying Formulas from Cell to Cell

Often in worksheets, the same formula but with different cell references is used across a row or down a column. For example, take the worksheet shown. Column F of the worksheet totals the rainfall figures in rows 5 through 9. To enter formulas for totaling the rainfall figures in column F, you could laboriously enter formulas in cells F5, F6, F7, F8, and F9. But a faster way is to enter the formula once in cell F5 and then copy the formula in F5 down the column to cells F6, F7, F8, and F9. When you copy a formula to a new cell, Excel adjusts the cell references in the formula so that the formula works in the cells to which it has been copied. Astounding! Opportunities to copy formulas abound on most worksheets. And copying formulas is the fastest and safest way to enter formulas in a worksheet. Follow these steps to copy a formula:

1. Select the cell with the formula you want to copy.

2. Drag the AutoFill handle across the cells to which you’ll copy the formula. This is the same AutoFill handle you drag to enter serial data. The AutoFill handle is the small black square in the lower-right corner of the cell. When you move the mouse pointer over it, it changes to a black cross.

3. Release the mouse button. Click in the cells to which you copied the formula and glance at the Formula bar to make sure that the formula was copied correctly. You can also copy formulas with the Copy and Paste commands. Just make sure that cell references refer correctly to the surrounding cells.

Working with Functions

A function is a canned formula that comes with Excel. Excel offers hundreds of functions, some of which are very obscure and fit only for use by rocket scientists or securities analysts. Other functions are very practical. For example, you can use the SUM function to quickly total the numbers in a range of cells. (“Entering a cell range,” earlier in this article, describes cell ranges.) Instead of entering =C2+C3+C4+C5 on the Formula bar, you can enter =SUM(C2:C5), which tells Excel to total the numbers in cell C2, C3, C4, and C5. To obtain the product of the number in cell G4 and .06, you can use the PRODUCT function and enter =PRODUCT(G4,.06) on the Formula bar. A function takes one or more arguments the cell references or numbers, enclosed in parentheses, that the function acts upon. For example, AVERAGE(B1:B4) returns the average of the numbers in the cell range B1 through B4; PRODUCT(6.5,C4) returns the product of multiplying the number 6.5 by the number in cell C4. When a function requires more than one argument, enter a comma between the arguments. To get an idea of the numerous functions that Excel offers, click the Insert Function button or choose Insert -> Function. You see the Insert Function dialog box. Choose a function category in the dialog box, select a function name, and read the description. You can click the Help on This Function link to open the Help program and get a thorough description of the function and how it’s used.

Manually entering a function in a formula

If you know a function well, you can enter it yourself in the Formula bar along with the rest of the formula. Be sure to enclose the argument or arguments in parentheses. Don’t enter a space between the function’s name and the first parenthesis. And please, please, please be sure to start your formula by entering an equal sign (=). Without it, Excel thinks you’re entering text. You can enter function names in lowercase. Excel converts function names to uppercase after you click the Enter button or press Enter to complete the formula. Entering function names in lowercase is recommended because doing so gives you a chance to find out whether you entered a function name correctly. If Excel doesn’t convert your function name to uppercase, you made a typing error. To quickly total the numbers in cells, click the cell where you want the total to appear and then click the AutoSum button on the Standard toolbar. Marquee lights appear around the cells that Excel wants to add up. If these are the cells you want to add up, press Enter immediately. Otherwise, select the cells you want to add up and then press Enter. You can also enter the AVERAGE, COUNT, MAX, or MIN function and their arguments this way. Click the arrow next to the AutoSum button and choose AVERAGE, COUNT, and so on from the drop-down list.

Getting Excel’s help to enter a function

Besides entering a function the conventional way by typing it, you can do it by way of the Function Arguments dialog box. The beauty of using this dialog box is that it warns you if you enter arguments incorrectly, and it spares you the trouble of typing the function name without making an error. What’s more, the Function Arguments dialog box shows you the results of the formula so that you get an idea whether you’re entering the formula correctly. Click the cell where you want to enter a formula, enter an equal sign (=) in the Formula bar, and do one of the following to bring up the Function Arguments dialog box:

 -  Open the Functions drop-down list (it appears on the left side of the Formula bar after you enter the equal sign) and choose a function.

 -  Click the Insert Function button or choose Insert -> Function and, in the Insert Function dialog box, find and double-click the name of the function you need for your formula. Enter arguments in the spaces provided by the Function Arguments dialog box. To enter cell references or ranges, you can click or select cells in the worksheet. If necessary, click the Go to Worksheet button (you’ll find it to the right of an argument text box) to shrink the Function Arguments dialog box and get a better look at your worksheet. Click OK when you have finished fooling with the function.