F3 key can be used with the following combinations:

• Ctrl+Shift+F3 – Create Names from Selection
• Ctrl+F3 – Open Name Manager
• F3 alone – Paste Name
• Shift+F3 – Open Insert Function dialog box

As you see, F3 is mainly used for Name related shortcut.  If you know about how to apply Name for formula writing, you should find F3 really helpful.  If you do not know about how to apply Name for formula writing, this post may serves as a starting point for the basic of Name Manager.

Without any Name in your workbook, you won’t see what the F3 key does.  Therefore I am going to start with Ctrl+Shift+F3.

Ctrl+Shift+F3 – Create Names from Selection

As you see from the Screentip, it’s about creating names from selection.  Let’s start with an example.  The example below is set up in a way that top rows contain column labels; while leftmost column contains row labels.

Select the range, and then press Ctrl+Shift+F3.  Check the “Top row” and “Left column” and check OK.  You will see why in the next section.

Ctrl+F3 – Open Name Manager

With the names created from Top row and Left column, let’s go to Name Manager what have been created.  Press Ctrl+F3 to open the Name Manager dialog box, where you may edit/delete existing names, or create new names.

Do you see? The names are created from the labels on top rows and left columns.  The name “Apple” now refers to the range $B$2:$E$2, “Banana” refers to $B$3:$E$3 and so on and so forth.

So what’s the benefit?  With proper naming, not only does it make your spreadsheet more organized, but also it makes your formula more “readable”.  See the screenshot below to find out.

Another less-known benefit is to write a simple formula to get the value of intersection, which normally requires a VLOOKUP with MATCH.

The operator Space (” “) means the intersection of ranges.  In our example above, it means the value of Banana for HK.  (Honestly, I have never used this operator before I write this post.)

Key Notes on assigning names: 

1. Name does not take empty space.  If there is space in the labels, e.g. Apple Pie, the Name Manager will replace the empty space with underscore “_” automatically.  I.e. “Apple_Pie” without the double quotes.
2. Name does not conflict with existing cell reference.  It does not take FY2012 as a valid name because FY2007 is a valid cell reference.  In such case, Name Manger will add an underscore at the end of the name, i.e. “FY2007_“.
3. More rules can be found here.

F3 alone – Paste Name

Now with the Names created, and knowing what the names are referring to, you may apply the names in our formula with F3 key.

During formula writing, pressing F3 will show you the “Paste Name” box that shows you the existing names available to you. 

While you are not in “Edit” mode (please refer to previous post for details), pressing F3 shows you almost the same box, with an addition button “Paste List”.  Clicking the “Paste List” will give you a list of names created in your spreadsheet, with the corresponding reference next to each name.  A handy way to understand all the names created in a snapshot.

Tip: Make sure you have enough room for pasting the list.

Shift+F3 – Open Insert Function dialog box

Well, this combination has nothing to do with Name Manager.  It opens the “Insert Function” dialog box where is the starting point for using/understanding functions in Excel.

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 with us your tips in using F3.

For other Function keys:

F1  F2  F3  F4  F5  F6  F7  F8  F9  F10  F11  F12