FORMULAS are the heart and soul of a spreadsheet, and Microsoft Excel 2010 offers a rich environment in which to build complex formulas. Armed with a few mathematical operators and rules for cell entry, you can turn a worksheet into a powerful calculator. In this chapter, we cover the basics and then look more closely at using functions, defining names, building structured references, working with arrays, creating linking formulas, and constructing conditional tests.
A lot of this section will seem elementary to experienced Excel users, but it is important information for anyone who is just arriving at the party. And even experienced users might find something useful here that they didn’t know about.
All formulas in Excel begin with an equal sign. This is the most fundamental fact of all. The equal sign tells Excel that the succeeding characters constitute a formula. If you omit the equal sign, Excel might interpret the entry as text. To show how formulas work, we’ll walk you through some rudimentary ones. Begin by selecting blank cell A10. Then type =10+5, and press Enter. The value 15 appears in cell A10. Now select cell A10, and the formula bar displays the formula you just typed. What appears in the cell is the displayed value; what appears in the formula bar is the underlying value, which in this case is a formula.
Operators are symbols that represent specific mathematical operations, including the plus sign (+), minus sign (–), division sign (/), and multiplication sign (*). When performing these operations in a formula, Excel follows certain rules of precedence:
Type some formulas to see how these rules apply. Select an empty cell, and type =4+12/6. Press Enter, and you see the value 6. Excel first divides 12 by 6 and then adds the result (2) to 4. Then select another empty cell, and type =(4+12)/6. Press Enter, and you see the value 2.666667. This demonstrates how you can change the order of precedence by using parentheses. The formulas in Table 12-1 contain the same values and operators, but note the different results caused by the placement of parentheses.
Table 12-1. Placement of Parentheses
Formula | Result |
|---|---|
=3*6+12/4–2 | 19 |
=(3*6)+12/(4–2) | 24 |
=3*(6+12)/4–2 | 11.5 |
=(3*6+12)/4–2 | 5.5 |
=3*(6+12/(4–2)) | 36 |
If you do not include a closing parenthesis for each opening parenthesis in a formula, Excel displays the message “Microsoft Excel found an error in this formula” and provides a suggested solution. If the suggestion matches what you had in mind, simply press Enter, and Excel completes the formula for you.
When you type a closing parenthesis, Excel briefly displays the pair of parentheses in bold. This feature is handy when you are typing a long formula and are not sure which pairs of parentheses go together.
A cell reference identifies a cell or group of cells in a workbook. When you include cell references in a formula, the formula is said to be linked to the referenced cells. The resulting value of the formula depends on the values in the referenced cells and changes automatically when the values in the referenced cells change.
To see cell referencing at work, select cell A1, and type the formula =10*2. Now select cell A2, and type the formula =A1. The value in both cells is 20. If at any time you change the value in cell A1, the value in cell A2 changes also. Now select cell A3, and type =A1+A2. Excel returns the value 40. Cell references are especially helpful when you create complex formulas.
When you start a formula by typing an equal sign into a cell, you activate Enter mode. If you then click another cell, you don’t select it; instead, the cell’s reference is inserted in the formula. You can save time and increase accuracy when you enter cell references this way. For example, to enter references to cells A9 and A10 in a formula in cell B10, do the following:
Select cell B10, and type an equal sign.
Click cell A9, and type a plus sign.
Click cell A10, and press Enter.
When you click each cell, a marquee surrounds the cell, and Excel inserts a reference to the cell in cell B10. After you finish entering a formula, be sure to press Enter. If you do not press Enter but select another cell, Excel assumes you want to include that cell reference in the formula as well.
The active cell does not have to be visible in the current window for you to enter a value in that cell. You can scroll through the worksheet without changing the active cell and click cells in remote areas of your worksheet, in other worksheets, or in other workbooks as you build a formula. The formula bar displays the contents of the active cell no matter which area of the worksheet is currently visible.
Relative references—the type we’ve used so far in the sample formulas—refer to cells by their position in relation to the cell that contains the formula, such as “the cell two rows above this cell.” A relative reference to cell A1, for example, looks like this: =A1.
Absolute references refer to cells by their fixed position in the worksheet, such as “the cell located at the intersection of column A and row 2.” An absolute reference to cell A1 looks like this: =$A$1.
Mixed references contain a relative reference and an absolute reference, such as “the cell located in column A and two rows above this cell.” A mixed reference to cell A1 looks like this: =$A1 or =A$1.
Dollar signs in a cell reference indicate its “absoluteness.” If the dollar sign precedes only the letter (A, for example), the column coordinate is absolute, and the row is relative. If the dollar sign precedes only the number (1, for example), the column coordinate is relative, and the row is absolute.
Absolute and mixed references are important when you begin copying formulas from one location to another in your worksheet. When you copy and paste, relative references adjust automatically, but absolute references do not. For information about copying cell references, see How Copying Affects Cell References on page 472.
While you are entering or editing a formula, press F4 to change reference types quickly. The following steps show how:
Select cell A1, and type =B1+B2 (but do not press Enter).
Press F4 to change the reference nearest the flashing cursor to absolute. The formula becomes =B1+$B$2.
Press F4 again to change the reference to mixed (relative column coordinate and absolute row coordinate). The formula becomes =B1+B$2.
Press F4 again to reverse the mixed reference (absolute column coordinate and relative row coordinate). The formula becomes =B1+$B2.
Press F4 again to return to the original relative reference.
When you use this technique to change reference types, click the formula bar to activate it, and then, before pressing F4, click in the cell reference you want to change or drag to select one or more cell references in the formula to change all the selected references at the same time.
You can refer to cells in other worksheets within the same workbook just as easily as you refer to cells in the same worksheet. For example, to enter a reference to cell A9 in Sheet2 into cell B10 in Sheet1, do this:
Select cell B10 in Sheet1, and type an equal sign.
Click the Sheet2 tab.
Click cell A9, and then press Enter.
After you press Enter, Sheet1 once again becomes the active sheet. Select cell B10, and you can see that it contains the formula =Sheet2!A9.
The worksheet portion of the reference is separated from the cell portion by an exclamation point. Note also that the cell reference is relative, which is the default when you select cells to create references to other worksheets.
You can refer to cells in worksheets in separate workbooks in the same way you refer to cells in other worksheets within the same workbook. These references are called external references. For example, to enter a reference to Book2 in Book1, follow these steps:
Create a new workbook—Book2—by clicking the File tab, clicking New, and double-clicking Blank Workbook.
Click the View tab, click Arrange All, select the Vertical option, and click OK.
Select cell A1 in Sheet1 of Book1, and type an equal sign.
Click anywhere in the Book2 window to make the workbook active.
Click the Sheet2 tab at the bottom of the Book2 window.
Click cell A2. Before pressing Enter to lock in the formula, your screen should look similar to Figure 12-1. Note that when you click to enter references to cells in external workbooks, the inserted references are absolute.
Press Enter to lock in the reference.
One of the handiest benefits of using references is the ability to copy and paste formulas. But you need to understand what happens to your references after you paste so that you can create formulas with references that operate the way you want them to operate.
Copying Relative References When you copy a cell containing a formula with relative cell references, Excel changes the references automatically relative to the position of the cell where you paste the formula. Referring to Figure 12-2, suppose you type the formula =AVERAGE(B4:E4) in cell F4. This formula averages the values in columns B through E.
You want to include this calculation for the remaining rows as well. Instead of typing a new formula in each cell in column F, select cell F4 and press Ctrl+C to copy it (or click the Copy button in the Clipboard group on the Home tab). Then select cells F5:F8, click the arrow next to the Paste button on the Home tab, click Paste Special, and then select the Formulas And Number Formats option (to preserve the cell and border formatting). Figure 12-3 shows the results. Because the formula in cell F4 contains a relative reference, Excel adjusts the references in each copy of the formula. As a result, each copy of the formula calculates the average of the cells in the corresponding row. For example, cell F5 contains the formula =AVERAGE(B5:E5).
Copying Absolute References If you want cell references to remain the same when you copy them, use absolute references. For example, in the worksheet on the left in Figure 12-4, cell B2 contains the hourly rate at which employees are to be paid, and cell C5 contains the relative reference formula =B2*B5. Suppose you copy the formula in C5 to the range C6:C8. The worksheet on the right in Figure 12-4 shows what happens: You get erroneous results. The formulas in cells C6:C8 should refer to cell B2, but they don’t. For example, cell C8 contains the incorrect formula =B5*B8.
Figure 12-4. The formula in cell C5 contains relative references. We copied the relative formula in cell C5 to cells C6:C8, producing incorrect results.
Because the reference to cell B2 in the original formula is relative, it changes as you copy the formula to the other cells. To correctly apply the wage rate in cell B2 to all the calculations, you must change the reference to cell B2 to an absolute reference before you copy the formula.
To change the reference style, click the formula bar, click the reference to cell B2, and then press F4. The result is the following formula: =$B$2*B5.
When you copy this modified formula to cells C6:C8, Excel adjusts the second cell reference within each formula but not the first. In Figure 12-5, cell C8 now contains the correct formula: =$B$2*B8.
Copying Mixed References You can use mixed references in your formulas to anchor a portion of a cell reference. (In a mixed reference, one portion is absolute, and the other is relative.) When you copy a mixed reference, Excel anchors the absolute portion and adjusts the relative portion to reflect the location of the cell to which you copy the formula.
To create a mixed reference, you can press the F4 key to cycle through the four combinations of absolute and relative references—for example, from B2 to $B$2 to B$2 to $B2.
The loan payment table in Figure 12-6 uses mixed references (and an absolute reference). You need to enter only one formula in cell C6 and then copy it down and across to fill the table. Cell C6 contains the formula = –PMT($B6,$C$3,C$5) to calculate annual payments on a loan. We copied this formula to all the cells in the range C6:F10 to calculate payments using three additional loan amounts and four additional interest rates.
The first cell reference, $B6, indicates that we always want to refer to the values in column B but the row reference (Rate) can change. Similarly, the mixed reference, C$5, indicates we always want to refer to the values in row 5 but the column reference (Loan Amount) can change. For example, cell E8 contains the formula = –PMT($B8,$C$3,E$5). Without mixed references, we would have to edit the formulas manually in each of the cells in the range C6:F10.
TROUBLESHOOTING
Inserted cells are not included in formulas.
If you have a SUM formula at the bottom of a row of numbers and then insert new rows between the numbers and the formula, the range reference in the SUM function doesn’t include the new cells. Unfortunately, you can’t do much about this. This is an age-old worksheet problem, but Excel attempts to correct it for you automatically. Although the range reference in the SUM formula does not change when you insert the new rows, it adjusts as you type new values in the inserted cells. The only caveat is that you must enter the new values one at a time, starting with the cell directly below the column of numbers. If you enter values in the middle of a group of newly inserted rows or columns, the range reference remains unaffected. For more information about the SUM function, see Using the SUM Function on page 537.
You edit formulas the same way you edit text entries: click in the cell or formula bar, click or drag to select characters, press Backspace or Delete or start typing. To replace a cell reference, highlight it and click the new cell you want the formula to use; Excel enters a relative reference automatically. You can also just click to place the insertion point where in the formula you want to insert a reference. To include cell B1 in the formula =A1+A3, place the insertion point between A1 and the plus sign, type another plus sign, and then click cell B1. Excel inserts the reference and the formula becomes =A1+B1+A3.
So far, we have used the default worksheet and workbook names for the examples in this book. When you save a workbook, you must give it a permanent name. If you create a formula first and then save the workbook with a new name, Excel adjusts the formula accordingly. For example, if you save Book2 as Sales.xlsx, Excel changes the remote reference formula =[Book2]Sheet2!$A$2 to =[Sales.xlsx]Sheet2!$A$2. And if you rename Sheet2 of Sales.xlsx to February, Excel changes the reference to =[Sales.xlsx]February!$A$2. If the referenced workbook is closed, Excel displays the full path to the folder where the workbook is stored in the reference, as shown in the example =’C:\Work\[Sales.xlsx]February’!$A$2.
In the preceding example, note that apostrophes surround the workbook and worksheet portion of the reference. Excel adds the apostrophes around the path when you close the workbook. If you type a new reference to a closed workbook, however, you must add the apostrophes yourself. To avoid typing errors, it is best to work with the linked workbooks open and click cells to enter references so that Excel inserts them in the correct syntax for you.
The seemingly oxymoronic term numeric text refers to an entry that is not strictly numbers but includes both numbers and a few specific text characters. You can perform mathematical operations on numeric text values as long as the numeric string uses only the following characters in very specific ways:
0 1 2 3 4 5 6 7 8 9 . + - E e
In addition, you can use the / character in fractions. You can also use the following five number-formatting characters:
$ , % ( )
You must enclose numeric text strings in quotation marks. For example, if you type the formula =$1234+$123, Excel displays an error message. (The error message also offers to correct the error for you by removing the dollar signs.) But the formula =“$1234”+“$123” produces the result 1357 (ignoring the dollar signs). When Excel performs the addition, it automatically translates numeric text entries into numeric values.
Note
For more information about number-formatting characters, see Formatting as You Type on page 322.
The term text value refers to any entry that is neither a number nor a numeric text value (see the previous section); Excel treats the entry as text only. You can refer to and manipulate text values by using formulas. For example, if cell A1 contains the text First and you type the formula =A1 in cell A10, cell A10 displays First.
Note
For more information about manipulating text with formulas, see Understanding Text Functions on page 544.
You can use the & (ampersand) operator to concatenate, or join, several text values. Extending the preceding example, if cell A2 contains the text Quarter and you type the formula =A1&A2 in cell A3, then cell A3 displays FirstQuarter. To include a space between the two strings, change the formula to =A1&” “&A2. This formula uses two concatenation operators and a literal string, or string constant (in this case, a space enclosed in quotation marks).
You can use the & operator to concatenate strings of numeric values as well. For example, if cell A3 contains the numeric value 867 and cell A4 contains the numeric value 5309, the formula =A3&A4 produces the string 8675309. This string is left-aligned in the cell because it’s considered a text value. (Remember, you can use numeric text values to perform any mathematical operation as long as the numeric string contains only the numeric characters listed in the previous section.)
Finally, you can use the & operator to concatenate a text value and a numeric value. For example, if cell A1 contains the text January and cell A3 contains the numeric value 2009, the formula =A1&A3 produces the string January2009.
INSIDE OUT Practical Concatenation
Depending on the kind of work you do, the text manipulation prowess of Excel may turn out to be the most important skill you learn in this book. If you deal with a lot of mailing lists, for example, you probably use a word-processing application such as Microsoft Word. But you might find that Excel has the tools you’ve been wishing for, and it just might become your text manipulation application of choice.
Suppose you have a database of names in which the first and last names are stored in separate columns. This example shows you how to generate lists of full names:
We created the full names listed in columns D and E using formulas like the one visible in the formula bar. For example, the formula in cell D2 is =B2&” “&A2, which concatenates and reverses the contents of the cells in columns A and B and adds a space character in between. The formula in cell E2 (=A2&”, “&B2) reverses the position of the first and last names and adds a comma before the space character. For another nifty—and related—trick, see the sidebar Practical Text Manipulation on page 548.
An error value is the result of a formula that Excel can’t resolve. Table 12-2 describes the seven error values.
Table 12-2. Error Values
Error Value | Cause |
|---|---|
#DIV/0! | You attempted to divide a number by zero. This error usually occurs when you create a formula with a divisor that refers to a blank cell. |
#NAME? | You typed a name that doesn’t exist in a formula. You might have mistyped the name or typed a deleted name. Excel also displays this error value if you do not enclose a text string in quotation marks. |
#VALUE | You entered a mathematical formula that refers to a text entry. |
#REF! | You deleted a range of cells whose references are included in a formula. |
#N/A | No information is available for the calculation you want to perform. When building a model, you can type #N/A in a cell to show you are awaiting data. Any formulas that reference cells containing the #N/A value return #N/A. |
#NUM! | You provided an invalid argument to a worksheet function. #NUM! can indicate also that the result of a formula is too large or too small to be represented in the worksheet. |
#NULL! | You included a space between two ranges in a formula to indicate an intersection, but the ranges have no common cells. |