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” …).

Retro Look: Easter Eggs

You may, (as I do), find it just a bit curious that Easter Eggs has been the most frequently searched topic on this blog.  During the 7 years of the blog’s existence, Easter Eggs have been searched for 26,240 times!  This represents nearly 15% of all searches on Excel Enthusiasts.

So, what does this mean?  Well, I have a theory, but first let’s review what we’re talking about in this regard:

Virtual Easter Eggs are hidden surprises, games, or messages that have been built into various software creations by clever developers who have a sense of humor (apparently frowned upon at Microsoft these days).   In years gone by, users who were “In-the-know” could feel smug knowing how to reach this cryptic, and often entertaining, secret content. 

So, where did this unusual terms come from?  “Easter Eggs” is attributed to one of the founding fathers of computer games, Warren Robinett. While working for Atari in the late 1970s, Mr. Robinett created a hidden screen which read, “Created by Warren Robinett”.  As with many talented people working for large companies in the past (think of the Disney empire…) it was not uncommon for designers to be given little credit for their work.  That being the case, Robinett probably felt his small ploy was justified.  I’m sure we all agree.

If you have been a longtime-user of Excel, you may remember that Excel 97 had an ambitious Flight Simulator hidden within the application (quite amazing for the time!). Using a simple combination of keyboard commands brought you to this remarkable simulator game.

Although a good deal more difficult to access, Excel 2000 included a Car Racing Easter Egg which resembled the classic Spy Hunter game.  If you are interested in the old classics, you can still find several downloads for this retro favorite.

Excel 2003 included an Office Quiz featuring the Crabby Office Lady (I am betting you remember her…).  If you still have a copy of this version, you can access this egg by typing in “Tortured Soul” (really…) in the search box. 

About the only Surprise (it doesn’t even warrant being called an Easter Egg…) you’ll find in Excel these days is the DATEDIF function.  This curiously undocumented tool calculates the difference in whole days, months or years between two dates.  It’s nice to know about.

So, why do so many Excel users continue to search for Easter Eggs?  My theory is that several of us are nostalgic for the more innocent time of the past when you could find a little hidden Fun in our office work applications.  A little Fun is, after all, always a good thing…

Macro Muscle

Today our topic is the often mystifying subject of Excel Macros.  Macros are typically known as tools that are only accessible to the special skillset of VBA programmers.  On one level, however, that is not entirely true.

If, for instance, you have workbooks which you need to continually update with new data and continually repeat certain tasks, a Recorded Macro can be your ticket to lessening your repetitious work and simplifying your routine chores.

A Recorded Macro can “remember” your mouse clicks and keystrokes while you work, and then allows you to play them back in future revisions and repetitions of your workbook. You can save your recordings, and when you run the macro, it will play the commands in the same order that you recorded them. It gives you true Macro Muscle at your command!

For example, let’s say you track the performance of the Sales Reps in your company. Something like this can be a boring, repetitive task, as each week a report may be needed for management.  It can be very dull, but it needn’t be so. Here's how to Record a Macro to address these updates:

1.   First of all, in your report workbook, click the start of the cells you are going to update.
2.   Point to the Developer tab, and then click Record Macro.
3.  In the Record Macro dialog box, enter a Name that applies to your operation. For ease of operation later on, assign a custom Shortcut Key (this will separate you from other mere mortal Excel users…).
4.   Now perform the calculations, formatting, moving, etc that applies to the repetitive and monotonous update.
5.   Finish recording the macro by clicking the Stop Recording button.

To Run your Macro, simply go to the update start cell and enter (or click) your Custom Shortcut Key.

Presto! Instant update! Your newly created Macro has just done all of the work that may have taken you several minutes or an hour to complete. Give it a try, you may find you have more Muscle than you ever knew!