Excel: Simplify the Entry of Dollar Signs in Formulas

Problem: It is a pain to type the dollar signs in complex formulas such as the formula in the previous topic. How can I make this job easier?

Press the F4 key as you are entering a formula to toggle a reference through the four possible reference types. Here's an example of how to use it:

1. Start to type the formula =E6*(B1.

1. Type B1.
   2. Immediately after you type B1, press the F4 key. Excel will insert both dollar signs in the B1 reference.

   

2. Press F4
   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.

   

3. Row only..
   4. Press the F4 key again. Excel changes the reference to a mixed reference, with the column portion of the reference locked.

   

4. Column.
   5. Press the F4 key once more. Excel changes the reference back to a relative reference with no dollar signs.

   

5. 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.