Chapter 1

Building basic formulas

A worksheet is merely a lifeless collection of numbers and text until you define a relationship among the various entries. You do this by creating formulas that perform calculations and produce results. This chapter takes you through some formula basics, including constructing simple arithmetic and text formulas, understanding the all-important topic of operator precedence, copying and moving worksheet formulas, and making formulas easier to build and read by taking advantage of range names.

All Excel formulas have the same general structure: an equal sign (=) followed by one or more operands—which can be values, cell references, ranges, range names, or function names—separated by one or more operators—which are symbols that combine the operands in some way, such as the plus sign (+) and the greater-than sign (>).

Formula nesting levels refers to the number of expressions that are nested within other expressions using parentheses; see “Controlling the order of precedence.”

1. Select the cell in which you want to enter the formula.

2. Type an equal sign (=) to tell Excel that you’re entering a formula.

3. Type the formula’s operands and operators.

4. Select Enter to confirm the formula.

However, Excel has three different input modes that determine how it interprets certain keystrokes and mouse actions:

• When you type the equal sign to begin the formula, Excel goes into Enter mode, which is the mode you use to enter text (such as the formula’s operands and operators).

• If you select any keyboard navigation key (such as Page Up, Page Down, or any arrow key), or if you select any other cell in the worksheet, Excel enters Point mode. This is the mode you use to select a cell or range as a formula operand. When you’re in Point mode, you can use any of the standard range-selection techniques. Note that Excel returns to Enter mode as soon as you type an operator or any character.

• If you select F2, Excel enters Edit mode, which is the mode you use to make changes to the formula. For example, when you’re in Edit mode, you can use the left and right arrow keys to move the cursor to another part of the formula for deleting or inserting characters. You can also enter Edit mode by selecting anywhere within the formula. Select F2 to return to Enter mode.

After you’ve entered a formula, you might need to return to it to make changes. Excel gives you three ways to enter Edit mode and make changes to a formula in the selected cell:

• Select the F2 key.

• Double-click the cell.

• Use the formula bar (the large text box that appears just above the column headers) to position the cursor anywhere inside the formula text.

Excel divides formulas into four groups: arithmetic, comparison, text, and reference. Each group has its own set of operators, and you use each group in different ways. In the next few sections, I show you how to use each type of formula.

Most of these operators are straightforward, but the exponentiation operator might require further explanation. The formula =x^y means that the value x is raised to the power y. For example, the formula =3^2 produces the result 9 (that is, 3*3=9). Similarly, the formula =2^4 produces 16 (that is, 2*2*2*2=16).

Comparison formulas have many uses. For example, you can determine whether to pay a salesperson a bonus by using a comparison formula to compare actual sales with a predetermined quota. If the sales are greater than the quota, the rep is awarded the bonus. You also can monitor credit collection. For example, if the amount a customer owes is more than 150 days past due, you might send the invoice to a collection agency.

One way to use text formulas is to concatenate text strings. For example, if you enter the formula ="soft"&"ware" into a cell, Excel displays software. Note that the quotation marks and the ampersand aren’t shown in the result. You also can use & to combine cells that contain text. For example, if A1 contains the text Ben and A2 contains Jerry, entering the formula =A1&" and "&A2 returns Ben and Jerry.

To control this problem, Excel evaluates a formula according to a predefined order of precedence. This order of precedence enables Excel to calculate a formula unambiguously by determining which part of the formula it calculates first, which part second, and so on.

From this table, you can see that Excel performs exponentiation before addition. Therefore, the correct answer for the formula =3+5^2, given previously, is 28. Notice also that some operators in Table 1-5 have the same order of precedence (for example, multiplication and division). This means that it usually doesn’t matter in which order these operators are evaluated. For example, consider the formula =5*10/2. If you perform the multiplication first, the answer you get is 25 (5*10 equals 50, and 50/2 equals 25). If you perform the division first, you also get an answer of 25 (10/2 equals 5, and 5*5 equals 25). By convention, Excel evaluates operators with the same order of precedence from left to right, so you should assume that’s how your formulas will be evaluated.

Figure 1-1 shows how you might implement such a formula. Cell B5 displays the Total Cost variable, and cell B6 displays the Tax Rate variable. Given these parameters, your first instinct might be to use the formula =B5/1+B6 to calculate the original cost. This formula is shown (as text) in cell E9, and the result is given in cell D9. As you can see, this answer is incorrect. What happened? Well, according to the rules of precedence, Excel performs division before addition, so the value in B5 first is divided by 1 and then is added to the value in B6. To get the correct answer, you must override the order of precedence so that the addition 1+B6 is performed first. You do this by surrounding that part of the formula with parentheses, as shown in cell E10. When this is done, you get the correct answer (cell D10).

In general, you can use parentheses to control the order that Excel uses to calculate formulas. Terms inside parentheses are always calculated first; terms outside parentheses are calculated sequentially (according to the order of precedence).

=number ^ (1 / n)

=A1 ^ (1 / 3)

To gain even more control over your formulas, you can place parentheses inside one another; this is called nesting parentheses. Excel always evaluates the innermost set of parentheses first. Here are a few sample formulas:

Notice that the order of precedence rules also hold within parentheses. For example, in the expression (5*2-5), the term 5*2 is calculated before 5 is subtracted.

Using parentheses to determine the order of calculations enables you to gain full control over your Excel formulas. This way, you can make sure that the answer given by a formula is the one you want.

• Select Formulas > Calculation Options.

• Select File > Options > Formulas.

Either way, you’re presented with three calculation options:

• Automatic: This is the default calculation mode, and it means that Excel recalculates formulas as soon as you enter them and as soon as the data for a formula changes.

• Automatic Except for Data Tables: In this calculation mode, Excel recalculates all formulas automatically, except for those associated with data tables (which I discuss in Chapter 19, “Using Excel’s business modeling tools”). This is a good choice if your worksheet includes one or more massive data tables that are slowing down the recalculation.

• Manual: Select this mode to force Excel not to recalculate any formulas until either you manually recalculate or you save the workbook. If you’re in the Excel Options dialog box, you can tell Excel not to recalculate when you save the workbook by deselecting the Recalculate Workbook Before Saving check box.

With manual calculation turned on, you see “Calculate” in the status bar whenever your worksheet data changes and your formula results need to be updated. When you want to recalculate, first display the Formulas tab. In the Calculation group, you have two choices:

• Select Calculate Now (or select F9) to recalculate every open worksheet.

• Select Calculate Sheet (or select Shift+F9) to recalculate only the active worksheet.

If you want to recalculate only part of your worksheet while manual calculation is turned on, you have two options:

• To recalculate a single formula, select the cell containing the formula, place the cursor inside the formula bar, and then confirm the cell (by selecting Enter or by selecting the Enter button).

• To recalculate a range, select the range; select Home > Find & Select > Replace (or select Ctrl+H); enter an equal sign (=) in both the Find What and Replace With boxes; finally, select Replace All. Excel “replaces” the equal sign in each formula with another equal sign. This doesn’t actually change any formula, but it forces Excel to recalculate each formula.

For example, Figure 1-2 shows a list of expense data for a company. The formula in cell C11 uses SUM(C6:C10) to total the January expenses. The idea behind this worksheet is to calculate a new expense budget number for 2019 as a percentage increase of the actual 2018 total. Cell C3 displays the INCREASE variable (in this case, the increase being used is 3%). The formula that calculates the 2019 BUDGET number (cell C13 for the month of January) multiplies the 2018 TOTAL by the INCREASE (that is, =C11 * C3).

The next step is to calculate the 2018 TOTAL expenses and the 2019 BUDGET figure for February. You could just type each new formula, but you can copy a cell much more quickly. Figure 1-3 shows the results when you copy the contents of cell C11 into cell D11. As you can see, the formula in D11 is =SUM(D6:D10), which means that Excel adjusted the range in the formula’s function so that only the February expenses are totaled. How did Excel know to do this? To answer this question, you need to know about Excel’s relative reference format, which I discuss in the next section.

Figure 1-4 shows why this format is useful. You had only to copy the formula in cell C11 to cell D11 and, thanks to relative referencing, everything came out perfectly. To get the expense total for March, you’d just have to paste the same formula into cell E11. You’ll find that this way of handling copy operations will save you incredible amounts of time when you’re building worksheet models.

However, you need to exercise some care when copying or moving formulas. Let’s see what happens if you return to the budget expense worksheet and try copying the 2019 BUDGET formula in cell C13 to cell D13. Figure 1-4 shows that the result is 0!

What happened? The formula bar shows the problem: The new formula is =D11 * D3. Cell D11 is the February 2018 TOTAL, and that’s fine, but instead of the INCREASE cell (C3), the formula refers to a blank cell (D3). Excel treats blank cells as 0, so the formula result is 0. The problem is the relative reference format. When the formula was copied, Excel assumed that the new formula should refer to cell D3. To see how you can correct this problem, you need to learn about another format, the absolute reference format, which I discuss in the next section.

To fix the budget expense worksheet, you need to anchor the INCREASE variable. To do this, you first change the January 2016 BUDGET formula in cell C13 to read =C11 * $C$3. After making this change, copying the formula to the February 2019 BUDGET column gives the new formula =D11 * $C$3, which produces the correct result.

You also should know that you can enter a cell reference using a mixed-reference format. In this format, you anchor either the cell’s row (by placing the dollar sign in front of the row address only—for example, B$6) or its column (by placing the dollar sign in front of the column address only—for example, $B6).

1. Select the cell that contains the formula you want to copy.

2. Place the cursor inside the formula bar.

3. Use the mouse or keyboard to select the entire formula.

4. Copy the selected formula.

5. Select Esc to deactivate the formula bar.

6. Select the cell in which you want the copy of the formula to appear.

7. Paste the formula.

FORMULATEXT(cell)

For example, the following formula displays the formula text from cell D9:

=FORMULATEXT(D9)

1. Select the cell containing the formula you want to convert.

2. Select F2 to activate in-cell editing. (You can also usually double-click the cell to open it for editing. If this doesn’t work for you, select File > Options > Advanced, and then select the Allow Editing Directly In Cells check box.)

3. Select F9. The formula changes to its value.

4. Select Enter or select the Enter button. Excel changes the cell to the value.

You’ll often need to use the result of a formula in several places. If a formula is in cell C5, for example, you can display its result in other cells by entering =C5 in each of the cells. This is the best method if you think the formula result might change because, if it does, Excel updates the other cells automatically. However, if you’re sure that the result won’t change, you can copy only the value of the formula into the other cells. Use the following procedure to do this:

1. Select the cell that contains the formula.

2. Copy the cell.

3. Select the cell or cells to which you want to copy the value.

4. Select Home, open the Paste list, and then select Paste Values. Excel pastes the cell’s value to each cell you selected.

Another method is to copy the cell, paste it into the destination, open the Paste Options list, and then select Values Only.

• Select Formulas > Use in Formula and then select the name in the list that appears (see Figure 1-5).

  

• Select Formulas > Use in Formula > Paste Names (or select F3) to display the Paste Name dialog box, select the range name you want to use, and then select OK.

• Type the first letter or two of the range name to display a list of names and functions that start with those letters, select the name you want, and then select Tab.

1. Select the range in which you want to apply the names or select a single cell if you want to apply the names to the entire worksheet.

2. Select Formulas > Define Name > Apply Names. Excel displays the Apply Names dialog box, shown in Figure 1-6.

   

3. In the Apply Names list, choose the name or names, you want applied from this list.

4. Select the Ignore Relative/Absolute check box to ignore relative and absolute references when applying names. (See the next section for more information on this option.)

5. Select the Use Row And Column Names check box to tell Excel whether to use the worksheet’s row and column names when applying names. If you select this check box, you also can select the Options button to see more choices. (See the section “Using row and column names when applying names,” later in this chapter, for details.)

6. Select OK to apply the names.

For example, suppose that you have a formula such as =SUM(A1:A10) and a range named Sales that refers to $A$1:$A$10. With the Ignore Relative/Absolute option deselected, Excel won’t apply the name Sales to the range in the formula; Sales refers to an absolute range, and the formula contains a relative range. Unless you think you’ll be moving your formulas around, you should leave the Ignore Relative/Absolute option selected.

As shown in Figure 1-7, the Total for the Rent row (cell F7) currently contains the formula =C7+D7+E7. If you applied range names to this worksheet and selected the Use Row And Column Names option, you’d think this formula would be changed to this:

Click here to view code image

=January Rent + February Rent + March Rent

If you try this, however, you’ll get a slightly different formula, as shown in Figure 1-8.

That is because when Excel is applying names, it omits the row name if the formula is in the same row. (It also omits the column name if the formula is in the same column.) In cell F7, for example, Excel omits Rent in each term because F7 is in the Rent row.

Omitting row headings isn’t a problem in a small model, but it can be confusing in a large worksheet, where you might not be able to see the names of the rows. Therefore, if you’re applying names to a large worksheet, you’ll probably prefer to include the row names when applying names.

Selecting the Options button in the Apply Names dialog box displays the expanded dialog box shown in Figure 1-9. This includes extra options that enable you to include column (and row) headings:

• Omit Column Name If Same Column: Clear this check box to include column names when applying names.

• Omit Row Name If Same Row: Clear this check box to include row names.

• Name Order: Use these options (Row Column or Column Row) to select the order of names in the reference.

1. Select Formulas > Define Name to display the New Name dialog box.

2. Enter the name you want to use for the formula in the Name text box.

3. In the Refers To box, enter the formula exactly as you would if you were entering it in a worksheet.

4. Select OK.

Now you can enter the formula name in your worksheet cells (instead of the formula itself). For example, the following is the formula for the volume of a sphere (r is the radius of the sphere):

4πr3/3

So, assuming that you have a cell named Radius somewhere in the workbook, you could create a formula named, say, SphereVolume, and make the following entry in the Refers To box of the New Name dialog box (where PI() is the Excel worksheet function that returns the value of pi):

=4 * PI() * Radius ^ 3 / 3

For example, Figure 1-10 shows two linked workbooks. The Budget Summary sheet in the 2019 Budget—Summary workbook includes data from the Details worksheet in the 2019 Budget workbook. Specifically, the formula shown for cell B2 in 2019 Budget—Summary contains an external reference to cell R7 in the Details worksheet of 2019 Budget. If the value in R7 changes, Excel immediately updates the 2019 Budget—Summary workbook.

You just need to be comfortable with the structure of an external reference. Here’s the syntax:

Click here to view code image

'path[workbookname]sheetname'!reference

For example, if you close the 2019 Budget workbook, Excel automatically changes the external reference shown in Figure 1-10 to this (depending on the actual path of the file):

Click here to view code image

='C:UsersPaulDocuments[2019 Budget.xlsx]Details'!$R$7

To be useful, however, the data in the dependent workbook should always reflect what actually is in the source workbook. You can make sure of this by updating the link, as explained here:

• If both the source and the dependent workbooks are open, Excel automatically updates the link whenever the data in the source file changes.

• If the source workbook is open when you open the dependent workbook, Excel automatically updates the links again.

• If the source workbook is closed when you open the dependent workbook, Excel displays a security warning in the information bar, which tells you that automatic updating of links has been disabled. In this case, select Enable Content.

  Tip

  If you always trust the links in your workbooks (that is, if you never deal with third-party workbooks or any other workbooks from sources you don’t completely trust), you can configure Excel to always update links automatically. To begin, select File > Options > Trust Center > Trust Center Settings. In the Trust Center dialog box, select External Content and then select the Enable Automatic Update For All Workbook Links option. Select OK and then select OK again.

• If you didn’t update a link when you opened the dependent document, you can update it any time by choosing Data > Edit Links. In the Edit Links dialog box that appears (see Figure 1-11), select the link and then select Update Values.

  

1. With the dependent workbook active, select Data > Edit Links to display the Edit Links dialog box.

2. Select the link you want to work with.

3. Select Change Source. Excel displays the Change Source dialog box.

4. Find and then select the new source document and then select OK to return to the Edit Links dialog box.

5. Select Close to return to the workbook.