Jan 19 2015

This may be the best keyboard shortcut in Excel – F4

This may be the best keyboard shortcut in Excel F4 (Repeat last action)

There are so many useful functions and hotkeys available in Microsoft Excel that many are not known nor used. That said, most intermediate to advanced users use at least one or two of these, such as ‘Ctrl‘ ‘C‘ to copy, ‘Ctrl‘ ‘V‘ to paste, ‘Ctrl‘ ‘Z‘ to undo, ‘F2‘ to edit a formula or what I use frequently, ‘F4‘ to repeat an action. It is said that the ‘F4‘ key is the most used keyboard shortcut in Excel.

Today’s Excel hint is on the ‘F4‘ hotkey. There are various ways to use it, they save keystrokes and mouse movement.  While it is incredibly useful it is important to note that the ‘F4‘ key does not repeat every action possible, and that there are also reported problems with the hotkey.  The best way to know what it will and will not do is to try it as you work on your workbook. But before you do, read what is below, look at the links that take you to some of the best resources on the web when it comes to Microsoft Excel. Then you will have an idea of what it possible.

————————————-

Examples of how to use the ‘F4” hotkey:

 

Repeat last action:

If you want to insert a row in Excel, you have several ways to do so, some use the mouse, some use the keyboard.  The way I do it is to activate the cell where I want to insert rows.  I then hit these keys:  ‘Alt’ ‘I’ ‘R’ = Insert new row in Excel. That inserts the first row.  For each additional row I then hit ‘F4’ key and it will repeat the action. If I want to insert 6 rows, I hit it 6 times. Quick, straightforward and simple.

 

Set Cell References: 

F4‘ Cycles through all 4 types of cell references (absolute, mixed reference (2x) and relative). For example, select a cell with a formula and press ‘F4‘. Notice what happens to the formula each time you it the ‘F4‘ key.  Why show this to you?  Because if you want to take the formula from one cell and place it in other cells, and still have it display the correct result, you will need to understand this, like it or not. Cell references should be your friend.

 

————————————-

 

There are also reported problems on the use of the hotkey, both Jon Peltier and Microsoft address the issue.

While the ‘F4 shortcut to “repeat last command” seems to not work in Excel 2010/2013 like it did with Excel 2003, I still use it often when I work in Excel, for example after I hit ‘Alt‘ ‘I‘ ‘R‘ (Insert Row), I often hit the ‘F4‘ key to repeat the action, and each time I hit the ‘F4′ key, it inserts another row.  However, in Excel 2010/2013 it does not seem to work anymore for charts.  To learn more about the problems, See what Jon Peltier – Microsoft Excel MVP has to say about it.

The Microsoft website says that the ‘F4‘ keyboard shortcut does not work as expected in Microsoft Excel 2013, Excel 2010, or Excel 2007. For example, when you sort a row (row A), and then you press F4 to repeat the action on another row (row B), row B is not sorted. Or, when you copy and paste a cell, and then you press F4, the cell is pasted. However, when you press F4 again, the cell is not pasted.  This is a known issue in Excel 2013, Excel 2010, and Excel 2007.  If you cannot use the F4 keyboard shortcut to repeat a command or action, manually perform the command or action that you want to repeat. See what Microsoft has to say about this.

 

————————————-

 

Good sources of information on keyboard shortcuts and Excel hotkeys.

 

 

Easy-Excel has some great examples of Microsoft Excel functions keys, such as the F4 key, click here to learn more.

 

————————————-

 

http://theexceladdict.hubpages.com/hub/F4-One-Of-Microsoft-Excels-Hidden-Gems Has great coverage on this hotkey.  You won’t want to skip this.

 

The Excel Addict says: This may be the best keyboard shortcut in Excel, according to the Excel Addict.

This is one of those rare shortcuts in Excel that is so useful, it’s amazing that it is never mentioned in basic training. As a matter of fact, I’ve rarely heard it mentioned anywhere. That’s why few Excel users ever use it.

When you want to repeat the last action that you’ve just taken, just select the range of cells where you want to repeat it an press F4.

For example, if you have just deleted a row and you want to delete another row, simply select the range where you want to delete the next row and press F4. If you have just added cell borders to a range and you format another range the same way, F4 will repeat the previous action for you. This is often most useful when you want to repeat an action over and over.

————————————-

 

Chandoo is one of the best resources on Microsoft Excel.  He presents topics in an easy to understand style.

http://chandoo.org/wp/2011/08/08/must-have-excel-keyboard-shortcuts

Chandoo says: 5. F4 – Change cell reference style while editing formulas

Change cell referencing style - F4 - Excel

Excel runs a pretty tight mafia when it comes to cell references. You must pay it a couple of $s if you want to change the reference style from relative (A2) to absolute ($A$2). But whos got the time to carefully navigate between A and 2 and then type SHIFT+4? So I use F4 key and Excel changes the referencing style for me.

————————————-

 

Have a question, either post a comment here or post your question on our Microsoft help forum, VBAExpress.Com

 

Please post your favorite uses of the ‘F4‘ key in the comments section.

 

844-VBA-Help

 

 

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives