Recently, I was reminded how infrequently the F4 function in Excel gets used to its fullest… everytime I show someone this function, they love it – it’s easy, fast, and versatile! If you’re not familiar with it, try it out and show a colleague – it is guaranteed to increase your Excel efficiency.

Looking for more ways to simplify your life with Excel?  Download our Excel shortcut listing. 

Excel F4 Key – How to use it

The F4 function is used for one of two tasks: 1) Cycle between absolute and relative references (in a formula) and 2) Repeat the last action. The one people seem to like the most is the repeat functionality, and really this is where the time savings will come into play once you get used to using F4.

Using F4 to repeat tasks:

When you’re in Excel (or Word for that matter) and you’ve just completed a task, maybe formatting a cell, deleting/inserting a row, or anything that’s repetitive simply push F4 the next time you need to complete that task. A few quick pointers:

  • It will only remember 1 action…it repeats your last action.
  • It doesn’t work with typing…that’s actually a good thing, because if you are formatting cells a certain color, this action will not be lost if you need to type in between using the formatting.
  • An easy way to think about it is that it’s just like the format painter, but better because it’s faster.

Using F4 to Cycle Between Absolute and Relative:

Excel F4 KeyIf you’re always writing formulas that require certain cell references to be absolute and others to be relative, then you’ll want to be using this shortcut key. Just to be sure we’re on the same page.  A relative reference is one that automatically updates when it is moved (or copied) within a spreadsheet. For example, if you enter =A1 in cell C2 and copy that formula to cell D3, the formula will automatically change to =B2. Relative references are the default references. Now, lets say you want the formula to remain =A1; to do this, it needs to be =$A$1 – the $ means that the reference will not automatically update. A $ in front of the A (i.e., column) will keep the column the same and a $ in front of the 1 (i.e., row) will keep the row the same. So, back to our example, if we entered =$A1 in cell C2 and then copied that to D3, we would get =$A2.

Now that we have that out of the way, you can use the F4 key to add the $ for you. Once you enter a cell reference, just push F4 (before you hit Enter) and the reference is made absolute. Each time you push F4, for a certain reference, the following will be available:

First time it’s pushed =$A$1
Second =A$1
Third =$A1
Fourth =A1

Simply stop on the one that works for your formula. Also, if you pass by the one you needed, just keep pushing F4, it will cycle back around.

Note, ‘Ctrl y’ can also be used to repeat the last action; however, it doesn’t work for cycling through absolute and relative.
Back to Top
On October 29, 2015   /   How To, Useful Resources