5. Understanding Formulas
Excel’s forté is performing calculations. When you use Excel, you typically use a combination of cells with numbers and cells with formulas. After you design a spreadsheet to calculate something, you can change the numbers used in the assumption cells and then watch Excel instantly calculate new results.
Getting the Most from This Chapter
Even if you think you know about formulas, you should review these points:
Everyone should read the “Double-Click the Fill Handle to Copy a Formula” section. Somehow, most people have learned to drag the fill handle to copy a formula. This leads to horrible frustration on long data sets, as they go flying past the end of the data. This simple but powerful trick is the one that universally amazes attendees of my seminar.
Honestly answer this question: Do you really understand the difference between cell H1 and cell $H$1? If you think the latter has anything to do with currency, you need to review the “Overriding Relative Behavior: Absolute Cell References” section thoroughly. This isn’t a trick, but one of the fundamental building blocks to creating Excel worksheets. Roughly 5% of the people in a Power Excel seminar do not understand this concept, and about 70% of the people in a community computer club presentation do not understand it. If you don’t know when and why to use the dollar signs, you are in good company with 20 million other people using Excel. It is worth taking time to learn this essential technique.
There are three ways to enter formulas, and I believe my preferred way is the best. I probably will not convince you to change, but when you understand my way, you can enter formulas far faster than by using the other two ways. To get a good understanding of the alternatives, read the “Three Methods of Entering Formulas” section later in this chapter.
Introduction to Formulas
This chapter and Chapter 6, “Controlling Formulas,” deal with formula basics. The chapters between Chapter 7, “Understanding Functions,” and Chapter 10, “Other Functions,” introduce adding functions to your formulas. Chapter 11, “Connecting Worksheets and Workbooks,” introduces formulas that calculate data found on other worksheets or in other workbooks. Chapter 12, “Array Formulas and Names in Excel,” provides interesting examples such as 3D formulas and the all-powerful array formulas.
Because of the record-oriented nature of spreadsheets, you can generally build a formula once and then copy that formula to hundreds or thousands of cells without changing anything in the formula.
Formulas Versus Values
When looking at an Excel grid, you cannot tell the difference between a cell with a formula and one that contains numbers. To see if a cell contains a number or a formula, select the cell. Look in the formula bar. If the formula bar contains a number, as shown in Figure 5.1, you know that it is a static value. If the formula bar contains a formula, you know that the number shown in the grid is the result of a formula calculation (see Figure 5.2). Keep in mind that formulas start with an equal sign.
Figure 5.1 The formula bar reveals whether a value is a static number or a calculation. In this case, cell E2 contains a static number.
Figure 5.2 In this case, cell E2 contains the result of a formula calculation. A formula starts with an equal sign.
Entering Your First Formula
Your first formula was probably a SUM function, entered with the AutoSum button. However, this discussion is talking about a pure mathematical formula that uses a value in a cell that’s added, subtracted, divided, or multiplied by a number or another cell.
Billions of variations of formulas can be used. Everyday life throws situations at you that can be solved with a formula. Keep these important points in mind as you start tinkering with your own formulas:
Every formula starts with an equal sign.
Entering formulas is just like typing an equation in a calculator with one exception (see the next point).
If one of the terms in your formula is already stored in a cell in Excel, you can point to that cell’s address instead of typing the number into that cell. Using this method enables you to change the value in one cell and then watch all the formulas recalculate. Excel 2016 adds a “slot machine” animation to show the cells in the visible window that are recalculating as the result of changing a cell.
To illustrate these points, see the steps to building a basic formula included in the following example.
Building a Formula
You want to enter a formula to calculate a target sales price, as shown in Figure 5.3. Cell D2 shows the product cost. In column E, you want to calculate the list price as two times the cost plus $3.
To enter a formula, follow these steps:
1. Select cell E2.
2. Type an equal sign. The equal sign tells Excel that you are starting a formula.
3. Type 2*D2 to indicate that you want to multiply two times the value in cell D2.
4. Type +3 to add three to the result. If your formula reads =2*D2+3, proceed to step 5. Otherwise, use the backspace key to correct the formula.
5. Press Enter. Excel calculates the formula in cell E2.
By default, Excel usually moves the cell pointer down or to the right after you finish entering a formula. You should move the cell pointer back to cell E2 to inspect the formula, as shown in Figure 5.3. Note that Excel shows a number in the grid, but the formula bar reveals the formula behind the number.
The Relative Nature of Formulas
The formula =2*D2+3 really says, “multiply two by the cell immediately to the left of me and then add three.” If you need to put this formula in cells E3 to E999, you do not need to reenter the formula 997 times. Instead, copy the formula and paste it to all the cells. When you do, Excel copies the essence of the formula: “Multiply two by the cell to the left of me and add three.” As you copy the formula to cell E3, the formula becomes =2*D3+3. Excel handles all this automatically. Figure 5.4 shows the formula after it is copied.
Figure 5.4 After you paste the formula, Excel automatically updates the cell reference to point to the current row.
Excel’s capability to change D2 to D3 in the formula is called relative referencing. This is the default behavior of a reference. Sometimes, you do not want Excel to change a reference as the formula is copied, as explained in the next section.
Overriding Relative Behavior: Absolute Cell References
Relative referencing, which is Excel’s ability to change a formula as it is copied, is what makes spreadsheets so useful. At times, however, you need part of a formula to always point at one particular cell. This happens a lot when you have a setting at the top of the worksheet, such as a growth rate or a tax rate. It would be nice to change this cell once and have all the formulas use the new rate.
The following example sets up a sample worksheet that exhibits this problem and shows how to use an arcane notation style to solve the problem. When you see a reference with two dollar signs, such as $G$1, this indicates an absolute reference to G1. An absolute reference is a cell or range address in which the row numbers and the column letters are locked and do not change during copying. Absolute references have a dollar sign before each column letter and each row number. Examples include $G$1 and $T$2:$W$99.
Suppose that you have a sales tax factor in a single cell at the top of a worksheet. After you enter the formula =C2*G1, it accurately calculates the tax in cell D2, as shown in Figure 5.5.
However, when you copy the same formula to cell D3, you get a zero as the result. As you can see in Figure 5.6, Excel correctly changed cell C2 to C3 in the copied formula. However, Excel also changed G1 to G2. Because there is nothing in G2, the formula calculates a zero.
Because the sales tax factor is only in G1, you want Excel to always point to G1. To make this happen, you need to build the original formula as =C2*$G$1. The two dollar signs tell Excel that you do not want to have the reference change as the formula is copied. The $ before the G freezes the reference to always point to column G. The $ before the 1 freezes the reference to always point to row 1. Now, when you copy this formula from cell D2 to other cells in column D, Excel changes the formula to =C3*$G$1, as shown in Figure 5.7.
Figure 5.7 The dollar signs in the formula make sure that the copied formula always points to cell G1.
To recap, a reference with two dollars signs is called an absolute reference.
If you are never going to copy the formula to the left or right, you can safely use =C2*G$1. This formula freezes only the row number. Given the shape of the current data, it is likely that using a single dollar sign will be valid.
Using Mixed References to Combine Features of Relative and Absolute References
In a number of situations, you might want to build a reference that has only one dollar sign. For example, in Figure 5.8, you want to use the monthly bonus rate in row 3, but you want to allow the column to change. In this case, the formula for cell B13 would be =B6*B$3.
Figure 5.8 By having the dollar sign before the 3 in B$3, you lock the reference to row 3 but allow the formula to point to columns D, E, and so on as you copy the formula.
When you copy this formula, it always points to the bonus amount in row 3, but the remaining elements of the formula are relative. For example, the formula in E15 is =E8*E$3, which multiplies Ken’s April sales by the April bonus rate.
There are two kinds of mixed references. One mixed reference freezes the row number and allows the column letter to change, as in A$1. The other mixed reference freezes the column letter but allows the row number to change, as in $A1. No one has thought up clever names to distinguish between these references, so they are simply called mixed references.
To illustrate the other kind of mixed reference, as shown in Figure 5.9, suppose you want a single formula to multiply the daily rate from column A by the number of days in row 4. This formula requires both kinds of mixed references.
Figure 5.9 You can create a formula by using a combination of dollar signs to allow cell C6 to be copied to all cells in the table.
In this case, you want the cell A6 reference to always point to column A, even when the formula is copied to the right. Therefore, the A6 portion of the formula should be entered as $A6. You also want the C5 portion of the formula to always point to row 5, even when the formula is copied down the rows. Therefore, the C5 portion of the formula should be entered as C$5.
Using the F4 Key to Simplify Dollar Sign Entry
In the preceding section, you entered quite a few dollar signs in formulas. The good news is that you do not have to type the dollar signs! Instead, immediately after entering a reference, press the F4 key to toggle the reference from a relative reference to an absolute reference, which automatically has the dollar signs before the row and column. If you press F4 again, the reference toggles to a mixed reference with a dollar sign before the row number. When you press F4 once again, the reference toggles to a mixed reference with a dollar sign before the column letter. Pressing F4 one more time returns the reference to a relative reference. You might find it easier to choose the right reference by looking at the various reference options offered by the F4 key.
The following sequence shows how the F4 key works while you are entering a formula. This particular example was included because it requires two types of mixed references.
The important concept is that you start pressing F4 after typing a cell reference but before you type a mathematical operator.
1. Type =A6.
2. Before typing the asterisk to indicate multiplication, press the F4 key. On the first press of F4, the reference changes to =$A$6.
3. Press the F4 key again. The reference changes to A$6 to freeze the reference to row 6. This still isn’t right because freezing the reference to row 6 will not help.
Press F4 one more time. Excel locks just the column, changing the reference to =$A6. This is the version of the reference you want. As you copy the formula across, the formula always points back to column A. As you copy the formula down, the row number in this reference is allowed to change to point to other rows.
4. To continue the formula, type an asterisk to indicate multiplication and then click cell C5 with the mouse. Press F4 twice to change C5 to a reference that locks only the row (that is, C$5).
5. Press Enter to accept the formula.
6. When you copy the formula from cell C6 to the range C6:G28, the formula automatically multiplies the rate in column A by the number of days in row 5. Figure 5.10 shows the copied formula in cell E9. The formula correctly multiplies the 10-dollar rate in cell A9 by the 24 hours figure in cell E5.
Figure 5.10 By using the correct combination of row and column mixed references, you can enter this formula once and successfully copy it to the entire rectangular range.
Using F4 After a Formula Is Entered
The F4 trick described in the preceding section works immediately after you enter a reference. If you try to change cell A6 after you type the asterisk, pressing the F4 key has no effect.
However, you can still use F4 by clicking somewhere in the formula bar adjacent to the characters A6. Pressing F4 now adds dollar signs to that reference.
Note
After you press F4 again, Excel returns the reference to the relative state A6. As you continue to press F4, Excel toggles between the four modes. It is fine to toggle between them all and then choose the correct one. If you accidentally toggle past the $A6 version, just keep pressing F4 until the correct mode comes up again.
Using F4 on a Rectangular Range
Some functions allow you to specify a rectangular range. For example, in Figure 5.11, you would like to enter a formula to calculate year-to-date sales. Although =SUM(B2:B13) works for cell C13, you cannot copy this formula to the other cells in the column. To copy this formula, you need to change the formula to =SUM(B$2:B13).
At this point in the figure, you might be tempted to press the F4 key. This does not work. If you select B2:B29 with the mouse or arrow keys, pressing F4 now converts the reference to the fully absolute range $B$2:$B$29. Continuing to press F4 toggles to B$2:B$29, then $B2:$B29, and then B2:B29. Excel does not even attempt to go through the other 12 possible combinations of dollar signs to offer B$2:B29 eventually. If you typed B2:B29, pressing F4 adjusts only the B29 reference.
In this case, you need to click the insertion point just before, just after, or in the middle of the characters B2 in the formula. If you then press F4, toggle through the various dollar sign combinations on the B2 reference. Pressing F4 twice results in the proper combination, as shown in Figure 5.12.
Figure 5.12 Using F4 is tricky when your reference is a rectangular range—you must click into the formula.
Three Methods of Entering Formulas
In the examples in the previous sections, you entered a formula by typing it. You generally need to start a formula by typing the equal sign (or the plus sign); after that point, you have three options:
Type the complete formula as described in the previous sections.
Type the operator keys, but use the mouse to touch cell references. In this book, this is referred to as the mouse method.
Type the operator keys and then use the arrow keys to specify the cell references by navigating to the cells. In this book, this method is referred to as the arrow key method.
Assume you would like to multiply the merchandise total in cell B2 by the sales tax rate in cell F1, as shown in Figure 5.13.
Enter Formulas Using the Mouse Method
If you started using computers since 1993, it is likely that you use the mouse method for entering formulas. This method is intuitive, but it requires you to move your hand between the keyboard and the mouse several times, as in this example:
1. Type = or +.
2. Click in cell B2.
3. Type *.
4. Click in cell F1.
5. Press F4 to add the dollar signs.
6. Press Enter. This usually moves the cell pointer to cell C3.
Tip
If you have a desktop keyboard, you can use the asterisk key on the numeric keypad to avoid pressing the Shift key.
This method requires only four keystrokes, but it requires you to move to the mouse twice. Moving to the mouse is the slowest part of entering formulas, but this method is easier than typing the entire formula if you are not a touch typist.
Tip
If you use the mouse method to enter formulas, customize the Quick Access Toolbar (QAT) to icons for Equal Sign, Plus Sign, Minus Sign, Multiplication Sign, Division Sign, Exponentiation Sign, and Dollar Sign. You can then enter most formulas without reaching back to the keyboard. There isn’t a QAT icon for the Enter key—use the green check mark to the left of the formula bar for Enter.
Entering Formulas Using the Arrow Key Method
The arrow key method is popular with people who started using spreadsheets in the days of Lotus 1-2-3 release 2.2. It is worthwhile to learn this method because it is incredibly fast. Almost all formula entry can be accomplished using keys on the right side of the keyboard. Here’s how it works:
1. In cell C2, type +.
2. Press the left-arrow key to move the flashing cell border to cell B2. Note that the active cell, which is the one with a green solid border, is still cell C2. The flashing border is like a second cell pointer that you can use to point to the correct cell for the formula. As shown in Figure 5.14, the temporary formula in the formula bar reads +B2.
Figure 5.14 By using the arrow keys during formula entry, you create a flashing border that can be used to navigate to a cell reference.
3. To accept cell B2 as the correct reference in the formula, press either an operator key (for example, * or +), a parenthesis, or the Enter key. In this case, type *.
Note
As you are moving the flashing cell border with the mouse, ignore the formula bar and watch just the flashing cell border.
4. Note that the dashed cell pointer disappears, and the focus is now back to the original cell, C2.
5. Press the right-arrow key three times. The flashing cell border moves to D2, E2, and then F2. With each keypress, the temporary formula in the formula bar shows an incorrect formula (+B2*D2, +B2*E2, and +B2*F2). Figure 5.15 shows what the screen looks like after you press the right-arrow key three times.
Figure 5.15 After step 4, the focus moves to the original cell. Thus, you only have to press the right-arrow key three times instead of four times to arrive at cell F2.
Tip
Even if you are mouse-centric, you should try this method for half a day. When you get the feel for navigating by using the arrow keys, you can enter formulas much faster by using this method.
6. Press the up-arrow key to move the flashing cell border to the correct location, cell F1. The temporary formula in the formula bar now shows +B2*F1.
7. Press the F4 key to add dollar signs to the F1 reference.
8. Press Ctrl+Enter to accept the formula and keep the cell pointer in cell C2.
Using this method requires 10 keystrokes, with no trips to the mouse. You can enter formulas that have no absolute references, mixed references, parentheses, or exponents by using just the arrow keys and the keys on the numeric keypad.
Note
Officially, every formula must start with an equal sign. However, to make former Lotus 1-2-3 users comfortable, Excel allows you to start a formula with a plus sign. Power Excel users have discovered that using a plus sign enables them to start a formula by typing on the numeric keypad. Because I routinely start formulas with the plus sign, I am often asked why I start with =+ instead of just =. Even though the formulas appear that way onscreen, I don’t actually enter the equal sign. When a formula starts with a plus sign, Excel adds an equal sign and does not remove the plus sign, so you end up with a formula that looks like =+B2*$F$1.
Entering the Same Formula in Many Cells
So far in this chapter, you have entered a formula in one cell and then copied and pasted to get the formula in many cells. To enter the same formula in many cells, you can use three alternatives:
Preselect the entire range where the formulas need to go. Enter the formula for the first cell and press Ctrl+Enter to enter the formula in the entire selection simultaneously.
Enter the formula in the first cell and then use the fill handle to copy the formula.
Beginning with Excel 2007, the method is to define the range as a table. When you use this method, the new formulas are copied down a column automatically.
Copying a Formula by Using Ctrl+Enter
This strategy works when you are entering formulas for one or more screens that are full of data:
1. If you have just a few cells, select them before entering the formula.
2. Click in the first cell and drag down to the last cell, as shown in Figure 5.16. Notice from the name box that the active cell is the first cell.
3. Enter the formula by using any of the three methods described earlier in this chapter. Even if you use the arrow key method, Excel keeps the entire range selected. Figure 5.16 shows a formula after you press F4 to convert the F1 reference to $F$1.
4. At this point, you would normally press Enter to complete the formula. Instead, press Ctrl+Enter to enter this formula in the entire selected range. Note that Excel does not enter =B2*$F$1 in each cell. Instead, it converts the formula as if it were copied to each cell.
Copying a Formula by Dragging the Fill Handle
If you want to enter a formula in one cell and then copy it to the other cells in a range, you can use the fill handle, which is the square dot in the lower-right corner of the cell pointer. There are two ways to use the fill handle:
Drag the fill handle.
Double-click the fill handle.
The dragging method works fine when you have less than one screen full of data:
1. Enter the formula in cell B2.
2. Press Ctrl+Enter to accept the formula and keep the cell pointer in cell B2.
3. Click the fill handle. You know that you are above the fill handle when the mouse pointer changes to a thick plus sign, as shown in Figure 5.17. Drag the mouse down to the last row of data.
4. When you release the mouse button, the original cell is copied to all the cells in the selected range.
This method is fine for copying a formula to a few cells. However, if you have thousands or hundreds of thousands of cells, it is annoying to drag to the last row. You invariably end up flying past the last row. Note that Excel 2016 automatically slows down and briefly pauses at the last row. However, it is far easier to copy a formula by double-clicking the fill handle.
Double-Click the Fill Handle to Copy a Formula
In most data sets, double-clicking the fill handle is the fastest way to copy the formula.
Instead of dragging the fill handle, double-click the fill handle. Provided one of the cells to the left, right, or below the active cell is nonblank, Excel fills to the bottom of the current region.
Before Excel 2010, using this method would fail if there were a few blank cells in the column to the left. Starting in Excel 2010, the logic was improved, and the technique almost always finds the correct number of rows based on the adjacent data.
Use the Table Tool to Copy a Formula
When you define your current data set as a table, Excel automatically copies new formulas down to the rest of the cells in the table.
Figure 5.18 shows an Excel worksheet that has headings at the top and many rows of data below the headings.
To define a range as a table, select a cell within the data set and press Ctrl+T. Excel uses its IntelliSense to guess the edges of the table. If its guess is correct, click OK in the Create Table dialog, as shown in Figure 5.19.
Ctrl+T is one of four entry points for creating a table. You can still use the Excel 2003 shortcut of Ctrl+L (because the feature was called a List in Excel 2003). You can choose Format as Table on the Home tab. You can choose the Table icon from the Insert tab.
As shown in Figure 5.20, after Excel recognizes the range as a table, several changes occur:
The table is formatted with the default formatting. Depending on your preferences, this might include banded rows or columns.
AutoFilter drop-downs are added to the headings.
Any formulas you enter use the headings to refer to cells within the table.
Figure 5.20 Defining a range as a table provides formatting and powerful features such as autofilters and natural language formulas.
Now when you enter a formula in the table, Excel automatically copies that formula down to all rows of the table.
Note
As shown in Figure 5.21, a lightning bolt drop-down appears to the right of cell D3. This drop-down offers you the opportunity to stop Excel from automatically copying the formula down.
Figure 5.21 Thanks to the Table tool in Excel, a new formula entered anywhere in column D is copied automatically to all the cells in column D.