3. Press the F4 key again. Excel changes the reference from an absolute reference to a mixed reference, with the row portion of the reference locked.
Figure 197 Row only..
4. Press the F4 key again. Excel changes the reference to a mixed reference, with the column portion of the reference locked.
Figure 198 Column.
5. Press the F4 key once more. Excel changes the reference back to a relative reference with no dollar signs.
Figure 199 Relative.
Here are the steps for entering the complex formula =E6*($B$1+$C6)*E$1:
1. Type =E6*(B1.
2. Press the F4 key once.
3. Type +C6.
4. Press the F4 key three times.
5. Type )*E1.
6. Press the F4 key twice to change E1 to a reference with the row locked.
7. Press Ctrl+Enter to accept the formula without moving the cell pointer to the next cell.
8. Use the mouse to grab the fill handle (the square dot in the lower-right corner of the cell) and drag it to the right by two cells. Excel will copy the formula from January to the other two months.
9. Double-click the fill handle. Excel will copy the three cells down to all the rows that contain data.
Additional Details: You might find mixed references confusing. As you work on building the first formula, you might know that you need to point to C7. Enter C7 in the formula and then use F4 to toggle between the various reference types. Say to yourself, “Okay, there is a dollar sign before the C that will lock the column and let the row change. Is that what I need?" As long as you say this to yourself without your lips moving, your office mates won’t think any less of you.
Additional Details: If you did not add the dollar signs as you typed the formula, you can still use the F4 trick later. Here’s how:
1. Use the mouse to highlight the proper reference in the formula bar.
2. Press the F4 key to toggle the highlighted reference through the four reference styles.
You can use the F4 key to easily add dollar signs to a reference in order to toggle it from relative to absolute to mixed to the other mixed.