MrExcel.com - Your One Stop for Excel Tips & Solutions

Simplify the Entry of Dollar Signs in Formulas

November 29, 2021 - by

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.

The fast way to put in dollar signs is to use the F4 key after selecting a cell in the formula. Start typing =E6 times Open Parenthesis B1. The flashing insertion character is right next ot the cell reference for B1. This example continues in the next figure.
Figure 185. Type B1.

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

Press F4 once and Excel inserts two dollar signs. The formula now points to $B$1.
Figure 186. 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.

Press F4 again and the reference has only the row locked with B$1.
Figure 187. Row only..

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

Press F4 again and the dollar sign moves to lock only column B with $B1.
Figure 188. Column.

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

The fast way to put in dollar signs is to use the F4 key after selecting a cell in the formula. Start typing =E6 times Open Parenthesis B1. The flashing insertion character is right next ot the cell reference for B1. This example continues in the next figure.
Figure 189. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Alexander Mils on Unsplash