F4 -It is my buddy!

Two years ago, I wrote two posts about using F4.

Honestly, these were the only two ways I know about F4 by that time.  Now I know it is more and it’s become the most hit function keys for me… so what F4 does?

  • F4 alone (contents enriched vs. the posts before)
  1. Switch absolute/relative cell reference while editing formula
  2. Repeat last action in general
  • Ctrl+F4 – Close active workbook
  • Alt+F4 or Alt+Shift+F4 – Close active program (Excel)
  • Shift+F4 – Find next (What?)
  • Ctrl+Shift+F4 – Find previous (What??)

F4 alone – Switch absolute/relative cell reference while editing formula

Screen Shot 2015-09-12 at 1.38.21 pm.  A picture tells more; and sometimes clearer.

This is what the F4 key does when the active cursor is sitting on a cell reference:

Excel Tips - F4 (switch reference)1

If you want to switch the reference of a range instead, you need to select the whole range, i.e. “A1:B1” in our example; or simply select the colon :

Excel Tips - F4 (switch reference)2

In case you need to switch all the reference in a formula, select the whole formula before pressing F4:

Excel Tips - F4 (switch reference)3

Nevertheless,  I haven’t encountered a case when I wanted to switch all the references altogether.  If you do, please share in comment.

F4 alone – Repeat Last Action

Literally, it repeats your last action performed in Excel.  I used it mostly in three situations:

1. Repeat formatting to different cells.

Excel Tips - F4 (repeat last action)0

Note: This is a simple illustration.  If you want more formats, e.g. color fill, font size and color, underline, etc., do not do it by clicking on formatting buttons on the Ribbon as each click is an action.  You need to do it in “Format Cell” in order to make it a single action that can be repeated as a whole.  Read my post Another use of F4 – Repeat LAST action.

2. Repeat the actions of Inserting / Deleting / Hiding rows or columns

Excel Tips - F4 (repeat last action)1

This is quite handy indeed.

3.  Grouping data more quickly

It takes you at least two clicks (Data–>Group) excluding the clicking of selecting rows/columns to be grouped.Excel Tips - F4 (repeat last action)2

It could be time consuming if we are repeating this action many many times.  With F4, it is less clumsy.

Excel Tips - F4 (repeat last action)3

Tip: the shortcut key for Grouping Data: Alt+Shift+→(Right Arrow )

Ctrl+F4 – Close active workbook

Excel Tips - F4 (close workbook)

I prefer the Ctrl+W that performs the same task.

Alt+F4 or Alt+Shift+F4 – Close active program (Excel)

This key combination is not for Excel but Windows.  It closes the active program.

Excel Tips - F4 (Close Excel)

There is an quicker way to achieve this task with the help of mouse.

Excel Tips - F4 (Close Excel)1

If you wish to close all workbooks but Excel, here is an alternative:

Go to Excel Options –> Quick Access Toolbar –> Choose Commands Not in the Ribbon –> Close All, Add it to the QAT

Excel Tips - F4 (Close workbooks) Excel Tips - F4 (close workbook)1

Use with Cautions:  Many times when we opened many workbooks, we don’t remember what changes have been made to which workbooks.  By closing all workbooks simultaneously, there is a high chance of clicking the “Don’t Save” accidentally…. Crying won’t help🙂

Shift+F4 – Find next (What?)

This is the hidden gem of F4.  When you press Shift+F4 on a worksheet, you will probably see the following:

Excel Tips - F4 (Find next)1

Is this message box familiar to you?  Maybe if you use Find and Replace in Excel

Basically Shift+F4 means “Find Next” in the Find dialog box:

Excel Tips - F4 (Find next)0

By pressing Shift+F4 without opening the Find and Replace dialog box, we instruct Excel to find a blank cell.  Depends on the current selection, it may lead you to an empty cell or the message box displayed before.

Practical use?

I find it useful if I need to navigate to all blank cells in a range:

Excel Tips - F4 (Find next)2

Tip: If you intend to input a value to the blanks, use Tab instead of Enter.  As by default Tab moves to the right; Enter moves downwards

Note: If you activated Find and input “something” in the “Find what:” box before, and without clearing “something” after you performed the Find, Shift+F4 would look for next “something” instead of next blank cell.

Ctrl+Shift+F4 – Find previous (What??)

Now it should be clear.  It does the same thing as Shift+F4 in the opposite direction.

wow… Can’t believe it is such a long post…  I hope you like it.


The shortcut combinations stated above have been tested with Excel 2010.  If you find it doesn’t work in other version of Excel, please let us know by leaving your comment.

Also feel free to share your tips in using F4 key.

This entry was posted in Shortcut and tagged . Bookmark the permalink.

2 Responses to F4 -It is my buddy!

  1. Igor says:

    I just watched MrExcel’s “The ModelOff Championships” and found it relevant to this topic especially to “F4 alone – Repeat Last Action”


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s