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

• How to switch between Absolute and Relative reference? F4
• Another use of F4 – Repeat LAST action

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

.  A picture tells more; and sometimes clearer.

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

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 :

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

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.

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

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.

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

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

Ctrl+F4 – Close active 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.

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

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

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:

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:

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:

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.

Note:

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.