Wednesday, October 21, 2015

F3 – The Magic Key Revisited

This morning I was privileged to do another Advanced Excel Class with one of the finest group of Excel scholar-practitioners that I have had the pleasure of teaching.  Our topic was the somewhat challenging one of Boolean functions.  As is the case with many complex formulas, Boolean functions involve the insertion of a great many Named Ranges.

If you are an experienced user of Excel, you undoubtedly know that Naming Ranges can save you a lot of time and make your formulas more intuitive to any user of your workbooks. Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.

Still, building multi-part formulas can be rather labor-intensive. Here is where the Magic Key of F3 can help alleviate some work for you.

Although “Magic Key” might overstating it a bit, the following simple example shows what it can do for you:

Let’s say that you have named several ranges in your workbook. When creating a formula, (in this example, we will find the average of a field named, “Sales”), do the following:

1. Type “ =Average( "
2. The hit the F3 key and
3. Using the Arrow Pad on your keyboard, choose your Named Range from the dropdown
4. Hit the Enter key and, Ala Kazam, the range is inserted into your formula!

This shortcut will save you little bits of time that will Add Up to many hours of work. (And that, Ladies and Gentlemen is a little bit of “Magic” …).

No comments: