Excel 2020: Use F4 for Absolute Reference or Repeating Commands - KING OF EXCEL

Friday, August 14, 2020

Excel 2020: Use F4 for Absolute Reference or Repeating Commands

Excel 2020: Use F4 for Absolute Reference or Repeating Commands

The mighty F4 key should be in your Excel arsenal for two completely different reasons:
  • Use F4 to add dollar signs in formula references to make them absolute, mixed, or relative.
  • When you are not editing a formula, use F4 to repeat the last command.
A cartoon shows an F4 Key with arms and legs. In the first panel, F4 is throwing dollar signs into cell references. In the second panel, it shows that F4 is the Repeat Last Action key when you are not in Edit mode.
Illustration: Cartoon Bob D‘Amico

Make a Reference Absolute

In the following figure, the tax in C2 is B2 times F1.
Sales amounts are in B2:B11. A single tax rate is in F1. A new formula of =B2*F1 is being entered.
But when you copy this formula down, none of the sales tax calculations are working. As you copy the formula down the column, the B2 reference automatically changes to B3, B4, and so on. That is what you want. But unfortunately, the reference to the sales tax in F1 is changing as well. That is not what you want.
The =B2*F1 formula works in cell C2, but it fails when copied down. The formula in C5 is shown and is =B5*F4. You need the reference to F1 to keep pointing to F1, even if the formula is copied down.
The solution? Edit the original formula and press F4. Two dollar signs are added to the final element of the formula. The $F$1 says that no matter where you copy this formula, that part of the formula always needs to point to F1. This is called an absolute reference. Pressing F4 while the insertion point is touching the F1 reference is a fast way to add both dollar signs.
As you are entering the first formula, type =B2*F1 and then press the F4 key. Excel will change the formula to =B2*$F$1.
There are other times when you need only part of the reference to be locked. In the following example, you need to multiply H2 by A3 by C1. The H1 will always point to H1, so you need both dollar signs in $H$1. The A3 will always point back to column A, so you need $A3. The C1 will always point to row 1, so you need C$1.
A formula shows three types of references =$H$2*$A3*C$1. To create $H$2, touch H2 and then press F4 once. To lock down only the column in $A3, touch A3 and then press F4 three times. To lock down only the row in C$1, touch C1 and press F4 twice.
To enter the above formula, you would press F4 once after clicking on H1, three times after clicking on A3, and twice after clicking on C1. What if you screw up and press F4 too many times? Keep pressing F4: It will toggle back to relative then absolute, then row absolute, then column absolute.
The result? A single formula that can be copied to C3:F12.
The formula =$H$2*$A3*C$1 works in C3:F12

Repeat the Last Command

Keyboard shortcuts are great. For example, Alt+EDC Enter deletes a column. But even if you are really fast at doing Alt+EDC Enter, it can be a pain to do this many times in a row.
This data set has the annoying feature of a tiny 1-width column between each column of data. Select the first blank column (B1) and Alt+E D C to delete the column.
After deleting column B, press the Right Arrow key to move to the next column that needs to be deleted. Instead of doing Alt+EDC Enter again, simply press F4. This beautiful command repeats the last command that you invoked.
Press the right arrow to move to the next blank column. Press F4 to repeat the Delete Column command.
To delete the remaining columns, keep pressing Right Arrow and then F4.
Keep deleting columns with Right-Arrow, F4, Right-Arrow, F4.
Next, you need to delete a row, so use Alt+EDR Enter to delete the row.
There are also blank rows between each row of data. Alt+E D R from the first blank row deletes it.
To keep deleting rows, press the Down Arrow key followed by F4 until all the blank rows are gone.
From there, press Down Arrow, F4, Down Arrow, F4, and so on.
The F4 trick works for a surprising number of commands. Perhaps you just built a custom format to display numbers in thousands: #,##0,K. If you see a few more cells that need the same format, select the cells and press F4.
Annoyingly, a few commands do not work with F4. For example, going into Field Settings in a pivot table and changing the number format and calculation is one that would be nice to repeat. But it does not work.
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

Popular Posts