Software

Office challenge: What's the quickest way to access Excel's Visible Cells Only option?

Can you answer the Office challenge: What's the quickest way to access Excel's Visible Cells Only option?

Last month, I showed you how to select only the visible cells in a filtered or subtotaled set: Quickly isolate summary values in an Excel sheet. In that entry, I mentioned using the [Alt] + ; keyboard shortcut to automate the Visible Cells Only option. There's an ever quicker way—a one-click method. What is it?

Last week we asked… Why isn't AutoComplete working in my newly installed Outlook 2010? You might think AutoComplete is broke, but it isn't. AutoComplete displays suggestions for recipients as you enter names. This feature is enabled by default. When you type the first letter, AutoComplete displays a list of possible matches, based on names you have typed before. That's the key, AutoComplete relies on previous entries and your new installation doesn't have any previous entries. Marc offered great instructions for copying the AutoComplete list from one installation to another. You can go this route if users really insist. Or, show them this trick sent by Bobby Heid, Lead Programmer/Analyst at AgFirst:

  1. Enter the first character or two—one usually isn't enough.
  2. Then, press [Alt]+K.

If there isn't a single match, Outlook will display all possible matches in the Check Names dialog box. You can choose your entry from there. If there's a single match, Outlook will update the To entry with the match. Once you use the name, it'll show up in the AutoComplete list.

Just be careful using this shortcut—don't assume that a single entry in the AutoComplete list means that's all there is. For instance, entering sh might display sharkins@gmail.com in the AutoComplete list because you've already used that name. However, you might have several other names that fit the bill, such as Sharon Moss and showtimes@gmail.com, but until you use those names as a recipient, they won't show up in the AutoComplete list.

Since we're talking about AutoComplete, there are a couple of improvements you should know about. Exchange users have access to their AutoComplete lists from any computer—Microsoft refers to this feature as Roaming AutoComplete. The lists are now stored on Exchange instead of locally. In addition, names are easy to delete from AutoComplete. Simply click the new inline Delete icon (the x to the right of the name).

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

10 comments
COFGFOR
COFGFOR

There is nothing, absolutely nothing, that can be done quicker by moving your hand from the keyboard to the long-tailed rodent. If you could somehow move the cursor and activate the mouse click event using the keyboard, you might save some time, but I doubt it. In fact, I was very unhappy with how often I was expected to use the mouse in Excel 2007 until I learned about UserForms, their KeyPress event and the KeyAscii constants associated with them, allowing me to mimic the customizable menus Excel used to offer to launch macros. Rodents are pests. I can think of only one thing they're good for: the selection of non-contiguous cells while pressing the CTRL key.

dhays
dhays

I have never had a need for accessing visible only cells, they are just there, so I don't know how in the first place. I don't quite understand what the hullabaloo is about. If they are visible, they are visible. Maybe in financial circles, but in "normal" usage, so what? What would be more interesting, it seems, would be to see a hidden cell. I use the ctl+g go to function for that.

jbenton
jbenton

(in excel 2003) There is a toolbar button available under customise toolbars near the end of the Edit category Drag this to a convenient toolbar to have available a single-click version of alt-; (altough I much prefer the latter) James

david.hanshumaker
david.hanshumaker

I have added the SELECT CURRENT REGION icon to my toolbar as I frequently find it useful. This, however, requires at least one mouseclick. Is there a keyboard shortcut to accomplish this?

RU7
RU7

"Absolutely" is a very strong word. "Nothing" is just slightly less strong. They mean the statement applies to all users, on all machines, in all circumstances, at all times. Anyone who has worked with a variety of users knows a statement like that is "never" true. Without considering the physically challenged user for whom the mouse is the preferred, if not the only interface device, there are users who simply prefer the mouse. Even for users who touch type 70 wpm, the mouse has its uses. I myself am bi-"lingual". I speak both keyboard and mouse.

jbenton
jbenton

that's what i was alluding to in the 1st reply above i'd also add that it's useful for double-clicking the fill handle, resizing columns when they're displayed and rearranging data with various shift and control combos but that's about it James

Asterisk
Asterisk

I use the visible cells option routinely. I commonly have information in sheets that most (sales)people do not need or want to see. So to prepare a slide for a presentation I hide rows and/or columns. Then I select visible cells and paste it into another place or slide. Try entering 1 in a1, 2 in b1 and 3 in c1. Then hide the B column. Now highlight a1 to c1. Copy this and paste it to d2. You receive all three cells. Now highlight a1:c1 again, but this time select visible only. Now copy and paste to e2. You get only 1 and 3 this time. Like most tips/tricks, playing with it some will usually lead to far more uses than expected.

ssharkins
ssharkins

This option allows you to select only the visible cells in a filtered set.

jbenton
jbenton

There's also ctrl-a or ctrl-sh-space which differ slightly from ctrl-* in that they select the entire sheet if repeated or if no current region; ctrl-* will also select the contiguous region before/above the current cell and go to the 1st cell of the region . In Word ctrl-* is the useful shortcut for display formatting marks; ctrl-a selects all; ctrl-sh-space is a non-breaking space