Navigation

I have presented the case for internal structural elegance, and external aesthetic elegance. A third type of elegance to master is that of motion, or what could be called navigational fluency. If you work in an office you undoubtedly expose your screen performance skills frequently. Whether you face individuals, small teams or large groups, this is part of the package you present.

As with design principles, the best impression you can give is not to wow the crowd with tricks, but to get yourself out of the way. Hone your skills to be more productive, and let the result speak for itself. If you struggle to navigate or edit the sheet in a public setting, you draw negative attention to yourself, detracting from your message. By contrast, when a well-designed sheet is presented and interacted with natural ease, focus remains on topic. By avoiding distraction, your fluency contributes to your impression as a good communicator.

To develop skill requires study and practice. As noted earlier, there is a huge volume of training material to study. Here it is important to prioritize and focus on the basics. Even if you think you know Excel well enough and have written formulas and macros, take time to renew and improve basic skills. Study and practice.

A good place to begin is to review keyboard shortcuts. Most people use only a few clipboard shortcuts, unaware of the hidden treasure in the keys. Mastering shortcuts makes you more productive by leveraging muscle memory and reducing mouse-to-keyboard transitions. This helps you in both private and public settings. The Keyboard Trainer project explains more and provides an easy learning tool.

First, review the purpose and use of the modifier keys, Shift, Ctrl, Alt:

· Shift capitalizes letters or types the “upper” character of a key

· Ctrl with most keys invokes whatever hot-key function is assigned

· Alt with a few keys invokes a hot-key function (e.g. =,↓, some F-keys)

· Alt begins a key sequence to invoke a ribbon function

Navigation Shortcuts

Walk before you run by getting fully comfortable moving around in the sheet and selecting data. Of course you know how the four arrow keys move cell to cell, and how PgUp, PgDn and Home keys work. The End key is more subtle. It toggles End Mode, as seen in the bottom left status bar, and must precede each key press.

End-navigation is easier with the Ctrl key. For example, say I’m in the top left corner of a filled rectangular range, and I want to go to the other three corners and back. I can go End →, End ↓, End ←, End ↑, or instead, I can hold down Ctrl as I hit →, ↓, ←, ↑.

Now, experiment. Add and hold down each modifier key – Shift, Ctrl, Alt – first one by one, then in pairs, as you move around with the four arrows. Practice this with different kinds of sheets – contiguous data region, non-contiguous regions, table, filter. Next, repeat the exercise with PgUp and PgDn keys, with multiple sheets, and finally, with Home and End. You will notice the following:

· Shift+ any navigation key extends the selection

· Ctrl+ arrows goes to the end of the contiguous range

· Ctrl+Home goes to cell A1

· Ctrl+End goes to the bottom right cell (as of the last save)

· Ctrl+PgDn goes to next sheet, Ctrl+PgUp goes to previous sheet

· Ctrl+Shift+ arrows, Home or End extends selection to the jump-to cell

· Alt+↓ opens a pick list, or creates a dynamic one in a text column

· Shift+Alt+→ groups, Shift+Alt+← ungroups

· Shift+Alt+↓ within a table column opens the filter

Not every combination is assigned a function, but this is how you discover what’s available. Some functions depend on the context, so study is still the way to understand the intended use. Practicing basic navigation skills is like the Karate Kid’s wax-on, wax-off exercise. You will see the value later, as you apply them in edit mode, in the ribbon and in dialogs as well as the sheet. In some contexts you’ll add the Tab key, and Space or Enter to select an option. In any case, you can always try any combination. See what happens!

Another key to add to your navigation vocabulary is F6. It cycles between panes, including the sheet, panes within the sheet if split, the sheet tabs, the status and zoom bar, and the ribbon. In each pane, try your moves and watch where the focus goes. With modifiers F6 cycles open workbooks.

The greatest agility is gained when you’re able to efficiently combine all input methods. Practice modifier keys with mouse actions to learn techniques such as:

· Shift+ left click extends the selection

· Ctrl+ left click adds to the selection

· Alt+ left click opens the research pane (looks up first word in a text cell)

· Ctrl+ scroll zooms in and out (scroll on the zoom bar does a finer zoom)

· Shift+ scroll opens and closes groups

· Ctrl+Shift+ scroll scrolls horizontally

Compete with yourself to find the best methods for you.

Function Shortcuts

The next level of shortcuts perform functions. Almost every key on the board has one or more hotkey functions assigned to it, and the F1 to F12 keys have stand-alone functions as well. It would be difficult and unnecessary to memorize them all, but they should all be assessed so that you can select and memorize those that give you the most benefit.

Of course, to know what hotkeys to use you must be familiar with the functions they perform. As an example, I was once in a meeting where the presenter had learned to copy and paste by Ctrl+C, Ctrl+V, but had not learned how to paste values only. When the target cell format was changed, the person reformatted the cell, causing an awkward distraction. Had they studied, they would know first that there is an option to paste values only, and second that while there is no direct hotkey, there are at least three easy keyboard methods:

· Ctrl+Alt+V, V, Enter

· Alt-H, V, V

· Ctrl+V, Ctrl, V

Most hotkeys are assigned mnemonically or spatially to be easy to remember. Appreciation of that will be to your advantage as you recall or even guess your way to productive use. They are also spelled out on the related ribbon icon tooltip, as you hover the mouse over it.

The Ribbon and Toolbar

The ribbon menu is rich with features, each with nice graphical representation. It is worth some quality time to get familiar with each tab, section and function, to know what’s available and where to look when you need it. Hover the mouse over each function to see a description, and whether a hotkey exists.

The entire ribbon is accessible via key sequence. Press Alt or F10 to start ribbon mode navigation. This reveals small black boxes with one or two characters called KeyTips, which are the shortcuts to each tab and function. You must always start with the KeyTip for the tab, then add the KeyTip for the function. For example, if you remember that the Name Manager is in the Formulas tab, you press Alt to see the letters on each tab, then M for the Formulas tab, then find the Name Manager icon and hit N to open it. Alt-M,N. Now, you may already know that Ctrl+F3 also opens Name Manager, so now you have options and can choose which way you like.

Generally, the most frequently used functions are those that have predefined hotkey shortcuts. If you get good enough at using the hotkeys, then use the KeyTips method for anything else. Or use hotkeys for navigation, and KeyTips for ribbon tools. It’s all about your personal productivity. KeyTips are less important to memorize because it is easy to open and follow the signs on the fly, easier if you can at least remember which tab to start with.

Another way to use the ribbon with keys is simple navigation. Start with Alt or F10 as with KeyTip navigation, then use → and ← to move between tabs. ↓ goes down into the tab, ↑ goes back up to tab navigation. Within a tab, Ctrl+→ or Ctrl+← to move between sections, → or ← or Tab to move between functions. To open a drop-down menu, Alt+↓, then ↓ or ↑ within the menu, Space or Enter to select an item, or Esc to close the menu.

Once you have a mental list of your personal favorite functions, keep them within easy reach in the Quick Access Toolbar (QAT). These are your choicest functions. They can be functions that don’t have a hotkey, or a redundant method for those that do. They can be tools you use everyday, or ones you rarely use but don’t want to hunt for again, and can recognize by icon. They can be standard tools, contextual tools or even personal macros that you keep in your startup file.

Customize the QAT by the drop-down at its right end. I like my QAT below the ribbon because it is quicker to access, and the buttons are in color, like they are in the ribbon. The downside of putting it below the ribbon is that it takes up about two rows of screen space.

To add a function from the ribbon to the Quick Access Toolbar, right-click it and select “Add to Quick Access Toolbar”. Not everything is in the ribbon, and not everything in the ribbon can be added this way. Sometimes you can only add the gallery when what you want is one item within the gallery. You really owe it to yourself to spend some quality time in the QAT customizer, Alt-F,T,Q, or via the QAT menu, “More Commands...” Here you can add specific items from any menu or gallery, explore available features, reorder items and add separators. Give special attention to the first nine items, as these will have KeyTip shortcuts Alt-1 through Alt-9.

In the QAT customizer “Choose commands from:” drop-down menu you can select from commands not in the ribbon, from macros, or from any standard or contextual tab. You can also customize the ribbon itself, Alt-F,T,C, by adding a tab, or group within a tab, then adding items to it. I have a tab I call “Mine” where I dump anything that looks interesting, allowing me to experiment without messing up my QAT. You can also remove groups you don’t need, and don’t worry, you can reset the tab or the whole ribbon to standard.