Obscure Shortcut Keys And Mouse Clicks

Category: General Weirdness | [Item URL]

Excel is certainly no slouch when it comes to user interface elements. You can right-click on just about anything, and you'll get a shortcut menu that often contains the command you're looking for. And, of course, it supports dozens of shortcut key combinations.

Most of the shortcut keys are documented, but the typical user probably knows 5-6 of them. Following are some of the more obscure user-interface elements in Excel.

• Shift+F10 displays the shortcut menu for a cell or range selection (equivalent to right-clicking the cell or range).
• Select a range of data, and press F11 (or Alt+F1) for an instant chart.
• To toggle between normal view and formula view, press Ctrl+` (that's the key that displays the ~ symbol).
• Right-click the Excel icon in the menu bar (to the left of the File menu), and you'll get the same shortcut menu that appears when you right-click a workbook's title bar (which is not visible if the workbook is maximized). Included on this menu is the very-handy View Code command -- which takes you to the ThisWorkbook code module. (Contributed by Bob Umlas)
• You're probably familiar with the "VCR" navigational controls to the left of the row of sheet tabs. But have you ever right-clicked on those controls? Try it. You'll get a handy menu of all sheets in the workbook. Click and sheet name, and you're there!

• If you press Shift and click either of the inner VCR navigational controls, the sheet tabs will scroll a screen's worth of tabs at a time. (Contributed by Bob Umlas)
• Most people know about the F5 key (equivalent to Edit - Go To), which brings up the Go To dialog box. This is used to go to a named range. You can also type a cell reference (such as AZ902) into the Reference box and click OK to go directly to that cell.
• If you're a VBA programmer, you might be interested in the fact that you can also type a VBA procedure name in the Go To dialog's Reference box. Click OK, and the VB Editor will be activated, and the cursor will be at the first statement in the procedure. (Contributed by Bob Umlas)
• In the Define Name dialog box (Insert - Name Define), the Refers to box can be very frustrating. Say you want to edit the reference... When you press an arrow key, the cell reference changes! To get into normal "edit" mode in the Refers to box, press F2 first. Then you can edit the range reference using standard techniques.
• If you use the "move selection after enter" setting (Tools - Options, Edit tab), you can override this by using Ctrl+Enter. The cell cursor won't change when you have a single cell selected. Also, you can use Shift+Enter to make the cursor move in the opposite direction. (Contributed by Bob Umlas)
• You've probably noticed that the status bar displays the sum of the selected cells. But many people haven't discovered that right-clicking the status bar lets you change the function that's applied to the selected range. (Contributed by Andy Brown)
  
  
• While we're on the topic of right-clicking... In Excel 2000 and later, right-clicking a worksheet's scrollbar displays another shortcut menu that contains substitute commands for scrolling a sheet. As far as I can tell, these shortcuts are worthless. By the time you right-click and make your choice from the shortcut menu, you could have done your scrolling directly. Or maybe I'm missing something.

Moving Data Validation Messages

Category: General Weirdness | [Item URL]

If you use Excel's data validation feature, you probably know that you can specify an input message that will appear when the cell is selected. Normally, this message appears near the cell.

When the prompt message is displayed, you can click it drag it to a new location. In the figure below, the prompt message for cell A1 has been dragged away. After you've dragged a prompt message, all new Data validation prompts are displayed in the same screen position!

There doesn't seem to be a way to "reset" this behavior back to normal -- except by closing and re-opening the workbook.

(contributed by Bob Umlas)

A Range Name Is Really A Named Formula

Category: General Weirdness | [Item URL]

Excel users often refer to named ranges and named cells. This terminology is not quite accurate. When you create a name, you are actually creating a named formula -- a formula that doesn't exist in a cell. Rather these named formulas exist in Excel's memory.

When you work with the Define Name dialog box, the Refers to field contains the formula, and the Name In workbook field contains the formula's name. You'll find that the contents of the Refers to field always begin with an equal sign -- a sure indication that it's a formula.

As you can see in the figure below, the workbook contains a name (InterestRate) for the cell B3 on Sheet3. Whenever you use the name InterestRate in a formula, Excel actually evaluate the formula with that name and returns the result.

If you understand this concept, you'll realize that you can create more complex (and useful) names. For example, you can create a name called ThisMonth (see below) which doesn't refer to any cells.

After this name is defined, entering the following formula will display the name of the current month:

=ThisMonth

Furthermore, named formulas are treated as if they were array-entered. Consider, for example, the following array formula, which returns TRUE if A1:A11 is in sequence, and FALSE if not.

=AND(A2:A11>A1:A10)

This is an array formula, so it must be array-entered with Ctrl+Shift+Enter.

Now, created a named formula, say InSeq, which is defined as follows:

  =AND(Sheet1!$A$2:$A$11>Sheet1!$A$1:$A$10)

After defining this name, you can use a formula like this, without array-entering it:

=InSeq

A Sheet Named History

Category: General Weirdness | [Item URL]

Spreadsheets that keep track of historical information are quite common. But did you ever try to give such a worksheet the (obvious) name History?

Try it, and Excel complains that it's a reserved name. This one had me puzzled, until someone pointed out that the History sheet name is reserved for keeping track of changes when the Track Changes feature is used (a feature I avoid using).

Page 3 of 3 pages
[Previous page]