This file is available in our store. Contents:
Purpose
With modern devices offering touch and voice controls, the keyboard approach might seem a bit old school. Remember Star Trek 4 from 1986? After traveling back in time from the future to the 1980’s, Scotty attempts to command a computer by speaking into the mouse. When directed to the keyboard he famously quips, “ah, a keyboard – how quaint!”
This view of the keyboard is not unjust. The QWERTY layout dates from 19th century typewriters, when it was designed to avoid jamming the mechanical arms. Yet as quaint as it may be, there are still advantages to keyboard navigation over mouse or touch. In theory, the more you learn to do with the keyboard, the less you’ll be switching back and forth and the more productive you’ll be.
Further, computers have allowed programmers to build in key combinations to perform common tasks efficiently – with no risk of jamming! The more feature-rich the program, the more plentiful and useful are keyboard shortcuts. Being less visual, the keyboard takes more memorization to become proficient, but they are usually designed mnemonically or spatially to be easy to remember, and even menu paths can be deftly unfolded by key sequence.
Consider the purpose of the modifier keys – Shift, Ctrl, Alt – what are they for? Like QWERTY, Shift descends from the typewriter, still used for capitalization but now with some superpowers. Ctrl and Alt evolved from early computing uses and now are known for their superpowers. These three keys, singly and combined, modify letter and special keys in more ways than you’ll ever use or remember. That puts a lot of power at your fingertips.
There are a lot of redundant or esoteric shortcuts that are not profitable – the benefit is too small for the brain cells it cost to remember them. For example, you can print the sheet by Ctrl+P, or by Ctrl+Shift+F12. I can remember Ctrl+P for print, the other one no way, and I print so rarely anyway I just don’t care enough to memorize a shortcut.
On the other hand, I would never use mouse for undo, cut, copy or paste, when their shortcuts, Ctrl+ Z, X, C, V respectively, are all so neatly juxtaposed to be hit by the left index finger while the left pinky is on the Ctrl key. There are some shortcuts that don’t even have a menu equivalent, like copy down – Ctrl+D – and copy right – Ctrl+R.
For menu browsing in the ribbon, with the mouse each tab is a click, whereas with the keyboard, once you have a tab selected (Alt or /), arrow keys let you surf from tab to tab or item to item (left/right, tab) or from tab to items (up/down). Hold down the keys to accelerate, and Alt+Down to open drop-downs, or tab to move on.
KeyTips jump through menu paths when you forget the shortcut or there is none. For example, to toggle headings, Alt-W,V,H. To hide the window, Alt-W,H; Alt-W,U to unhide. There’s no need to try to remember a lot of paths, because the signposts are always there. If you find certain paths useful, with practice you will add them to your repertoire as hotkey sequences.
To be pragmatic you must find the right methods that are comfortable for you. The point is not to neglect free tools that can make you more productive. Immerse yourself enough to become fluent. Remember when you learned to type words? Learning to type commands is not much different. I encourage you to master all that the keyboard has to offer, and refresh yourself often. After years of power-using Excel, I still need to update my keyboard skills periodically, and I can still be pleasantly surprised to rediscover shortcuts I can use.
In my need to relearn, I bookmarked online lists and made cheat sheets. I methodically tested every combination. Eventually the opportunity matured into a project. The bonus is that the finished project will help tone up your basic Excel skills.
The concept is to simulate the keyboard on the screen, provide a means to select functions, and highlight the keyboard to visually link the key combination with the function. This will be a great use for slicers; they’re intuitive, visual and can be arranged on screen with the keyboard. There will be no need for the data itself to be visible.
This tool nicely illustrates three programming stages: Input via four Slicers; Process via hidden Table and PivotTable; Output via on-screen keyboard highlighting.
Keyboard
The keyboard is meant to look as realistic as possible. When you look at a keyboard, the rows of keys are offset by about a third of a key. To mimic this I made the column widths small and made each key three columns wide by merging cells. I give the whole sheet a dark gray fill, and each key has a gradient fill effect with two shades of gray to give it a slight shadow.
F1 to F9 have a space in the middle (e.g. F 1), so that all 12 F-keys sort correctly in the slicer. F1-F12 keys do not include their alternate (fn) functions such as volume and brightness, since these have no relevance to Excel. For the purpose of this project, the F key is the default, so the fn key is not included. Depending on the computer, this default may be set with hardware (fn lock), software (configuration utility) or the BIOS setup utility.
Font sizes are adjusted for realism. Single-character keys are size 16, multi-character keys are size 12. Combination keys are made with the first character superscript font 20, and the second normal font 16. Letter keys are padded with a space on either side, sorting the alphabet together in the slicer and retaining the center alignment; likewise the special character and punctuation keys.
Arrow keys are copied from the Symbol picker, Alt-N,U, from the Unicode set, using the same Calibri font as the other keys. Wingdings 3 font has many more arrow symbol options, but that only changes the appearance of the character in the cell to an arrow, not the character itself. Here I want the character itself to be an arrow, so that the slicer will display it as an arrow, so I use what arrows are available in Calibri. See the Startup Symbols Sheet for symbol examples.
The keyboard layout is based on an hp envy with a separate number pad. The number pad does not have special functions, but offers alternate ways to call the same functions. The trick is to be proactively aware of the num lock key status. It is important to be fluent with whatever keyboards you may use – work, home, external. The tool can be interpreted or modified for use with any keyboard.
Table
The table and its pivot will be hidden off to the right of the keyboard. Column [Key] is based on the keys in the keyboard, exactly copied. This means the content must match, not the format. Many keys have two characters, or spaces, and the keys are made of merged cells. If you copy and paste directly you will copy the format including cell merging.
If you paste values, the target cells will not be merged, but will be overwritten, so use a safe range. The safest way is to edit the merged cell to copy only the value: F2 to edit, Ctrl+A or Shift+↑ to select all, Ctrl+C to copy. Then it will safely paste into a single cell.
There are two modifier key columns, [Mod1] and [Mod2], allowing for none, one or two together. These plus the [Key] column are used to highlight the keyboard. [Key] is also a slicer, explained later.
[Area] is a function category to help the search, and supplies the first slicer. I carefully name and assign the values to end up with a handful of areas, each with a reasonable number of functions. This serves as roughly an order of magnitude zoom out on the detail function list.
[Function] is the description starting with the most likely sought-after keywords, with sort also in mind. For example, clipboard functions start with “clipboard” so they will stay together, like another grouping level. Following this is a succinct description. This feeds the second and main slicer, and can be used with or without the other slicers.
[Way] provides for multiple ways to do the same function, feeding the third slicer. For any multi-way function the ways are numbered, beginning with the one I think is the easiest or most likely to be remembered and used. Single-way functions are all assigned way 1, avoiding blank value in the slicer.
[Numlock] passes on or off status to the format condition, for those records where it matters, that is, for any of the dual-function keys in the number pad. For all other keys it is blank.
Pivot
The first step to add the slicers is to create a pivot table. But I can add slicers to the table directly, so why do I need a pivot table? A table slicer will display the selected record just as a pivot slicer will. The difference is in how filtering works, as explained below.
Thus I am using the pivot only to pass the selection from the slicer on to the subsequent process, in this case, Conditional Formatting. In the example shown, I would reference A2 to get the selected function, and use it to look up the keys to highlight.
Using a pivot here is even easier than passing on a lookup value. The pivot has all the table columns at its disposal. Some are used for the slicers and some are used for the formatting.
All seven table columns are included in the pivot, though they don’t have to be in the same order. They’re all placed in the rows section of the pivot. With totaling removed and report layout set to tabular form, the pivot looks a lot like the table itself.
Next, I create names for the top row of values so they can easily be referenced. With the pivot header and the row below it selected, and practicing using keyboard functions, I hit Ctrl+Shift+F3 to create names from the top row. Now the top data row, which will have the unique selected record, is named by the field headings. Mod1 and Mod2 names become Mod1_ and Mod2_, since otherwise they would conflict with cell addresses.
Slicers
I begin by looking at the keyboard sheet to see what kind of space I have to work with and how best to use it. The keyboard takes up about half of the vertical space and about two thirds of the horizontal space on my 15 inch screen. I want users to be able to see the whole thing, keyboard and slicers.
At work, the great majority of people I’d be sharing with probably have a standard computer configuration. I keep mine as standard as possible so that they will see what I see. Depending on custom screen size and resolution, some may have to zoom out, or minimize other features. I can’t please everyone but I will try to give tips for adjustment.
Since I need as much screen space as possible, and the columns and rows of the sheet are not relevant, I hide the headings, Alt-W,V,H. Later I’ll add some helpful tips on how to increase space.
I decide to use the space below the keyboard for Area and Function, which have longer text length needing wider buttons. Way will be small and easy to fit in. I’ll use the space to the right for Key. With the cursor in the pivot, I select Insert Slicer from the pivot menu, and select the four selection fields.
The first slicer is Area. I ended up with six areas, so I give the slicer six columns for a horizontal layout. I duplicate the format in the gallery, then modify it to fit the sheet color scheme. In Slicer Settings I change the caption to “filter by area”, and uncheck “Show items with no data last”. This will keep the areas in constant position.
The second slicer is for Function. This takes a little more careful thought. There are a lot of values, so there will be a scroll bar, and the function texts vary in length. I need a button width that allows a lot of buttons to show but minimizes truncation of the description. I line up the slicer width with the total width of the keyboard and number pad. Tweaking the number of columns, I arrive at six. Setting the height to use the space with the ribbon minimized gives me eight rows of buttons. The full list is a little more than two full pages within the slicer.
This time I leave the option on to “Show items with no data last”. With so many buttons, I want the selected group to collect at the top. As a general rule, if there’s a scroll bar, I leave this option on to avoid hiding buttons that may be filtered by another slicer. If there’s no scroll bar then nothing is hidden, and it’s easier on the eye to keep the buttons in place by turning the option off.
The third slicer is a tiny one for Way. It will have just four buttons labeled 1 to 4. I give it five columns leaving a space at the end, and making it the same height as Area.
Carefully jockeying these three slicers into position, I fit them all neatly into the space below the keyboard. Snap-to-grid alignment gives a sharp look, but sometimes it requires tweaking column width or row height to work it out just right. A trick to manage row height is to pick an empty cell and increment font size until the height is just right. This ensures it will not revert to the default height.
The final slicer is Key. Key selection is added to help memorization by position, and to learn function groupings. For example, F6 has four functions with different modifiers, all related to navigation between panes and windows. This represents a different learning approach, and so is logically separated from the other slicers, filling up the right side of the screen.
The Key slicer has the same height as the keyboard plus other slicers. At this height, all the keys fit with four columns, eliminating the scrollbar. Then I adjust the width for a comfortable button size. With no scrollbar to deal with, I turn off the “Show items with no data last” option.
Slicer order determines the selection cycle when tab navigation is used. The right-click menu has Bring to Front and Send to Back, or with multiple objects I go to the Selection Pane to arrange order, Alt+F10. Use the arrow buttons with selected objects to set the depth. Tab navigation goes from bottom to top: Area, Function, Way, Key. The Selection Pane is useful to select, order and hide some or all objects.
Trying out the slicers, the Area slicer filters the Function slicer as desired, and the pivot’s top data row always has the unique function and way selected. If there are multiple ways and none is selected, they are sorted and the top row is by default way 1.
When I so carefully arrange slicers or other objects, I want them to stay in place. To lock them from inadvertent slips, right-click > Size and Properties > Position and Layout > Disable resizing and moving.
Formatting
Now before I get to formatting, there are preconditions to define. I don’t want the highlighting to turn on until there’s a unique function selected. Otherwise, the first function would be marked even when none is selected. I need a way to tell if a specific function is selected, not just an area.
The number of rows in the pivot changes with each slicer button. A single-way function button has one row. A multi-way function can have several, probably not more than four. With no function selected, an area button will have as many function-ways as are assigned to it. Checking each area, I find the minimum is thirteen. I pick seven as a number that is between four and thirteen, then count the blanks in the top seven cells of the Function pivot column. There will be zero blanks if no function is selected, and a positive number if one is selected, whether it is single or multi-way. This provides a Boolean “is selected” condition. In addition, I check that it’s either way 1 of a multi-way function, or a single row. This eliminates- any confusion that might result if, for example, two functions are selected.
IsSelFunc = AND(COUNTBLANK(OFFSET(Function,0,0,7)), OR(AND(OFFSET(Function,1,0)=Function,Way=1), ISBLANK(OFFSET(Function,1,0))))
Now with the entire keyboard selected, active cell B2, the formatting formula to highlight the keys in yellow fill:
=AND(IsSelFunc,B2=Key)
Next, a similar condition is applied to the modifier keys. Because they each have two keys, left and right, I want to highlight both, but of course not suggest that they are both required. They get a gradient fill as a subtle hint to use one or the other. This is only applied to the three modifier pairs, Shift, Ctrl, Alt, and the two Enter keys, and works on top of the first condition. The keys are selected carefully, first the left set then the right set with opposite gradients. For the left set, active cell is B6:
=AND(IsSelFunc,OR(B6=Mod1_,B6=Mod2_))
Finally, when the Key slicer is used to pick a key that has multiple functions, I want to show that differently, using a pattern to say “has multiple functions”. When this happens, the Function slicer shows and sorts the possible functions to the top to aid retention. Once one of the functions is selected, the clear highlighting turns on with the relevant modifiers. This uses a similar check as above, with the condition that the first two pivot rows have the same key:
IsSelKey = AND(COUNTBLANK(OFFSET(Key,0,0,7)),OFFSET(Key,1,0)=Key)
format condition formula: =AND(IsSelKey,B2=Key)
The num lock key has two special conditions, formatted white for on and black with white font for off. This only applies for the dual-function number pad keys which are never way 1, so require a single record.
“on” condition =AND(IsSelFunc,Numlock="on",ISBLANK(OFFSET(Key,1,0)))
“off” condition =AND(IsSelFunc,Numlock="off",ISBLANK(OFFSET(Key,1,0)))
Now to practice! Try this: scroll the Function slicer to find “select: objects”, click it, then execute the highlighted key combination (Ctrl+Alt+5). This should select the Area slicer. With a watchful eye on the slicer features – the slicer edge, the multi-select button, the unfilter button, the button area – hit Tab repeatedly to see how the focus moves. Keep going until you’re on the Function slicer with the focus on a function button. Use arrow keys to move the focus, and space or enter to select other buttons.
If you still prefer the mouse, note that to use the scroll wheel to scroll up and down the Function slicer, first select the slicer. Clicking a button in the slicer or dragging the scroll bar does not select the slicer. Click somewhere on the header or edge of the slicer. When the whole thing is outlined with a white border it is selected, and you can scroll the buttons. When you use mouse click to select buttons, use Ctrl+click to unselect.
When using slicers or any filters, remember to unfilter all or level by level. If you select Area > Function > Way, then unfilter in reverse. Unfilter Way before selecting another Function, and unfilter Function before selecting another Area. Also, whenever you update the source table you need to refresh, which you can do either from the pivot or the slicers.
Watch out if you are using tab and arrow key navigation in the slicers, that some of the actual functions won’t work as long as the focus is on the slicer. Hit Esc to return focus to the sheet if this happens.
For a clean screen I hide the table and the pivot. These are initial setup, rarely edited, and would detract from the appearance. There are various ways to hide data or parts of a workbook. Here grouped columns make the most sense.
The table and the pivot each have seven columns and are separated by a small blank column. Practicing keyboard skills, I select the seven full-worksheet columns covering first the table then the pivot, and hit Alt+Shift+→ to create a group. Then, Data > Outline > Settings > uncheck “Summary columns to right of detail”. Finally, I collapse the groups, and hit Ctrl+8 to hide the outline symbol.
By hiding the + group symbol, I regain about two rows of space and remove clutter. The groups can easily be opened and closed multiple ways, as shown in the section on Grouping in Chapter 3 Formatting section.
To identify the group header, I usually use a single letter or symbol. Here I use P for pivot and T for table, which are revealed only when the help is turned on.
Help is provided in several grouped cells in row 1 that each contain a single word with a Data Validation input message explaining the topic. The cells are hidden until the “Show Help” check box is set. When help is turned on, each grouped cell is made to appear like a button with gradient fill. When help is turned off, the information is still there, but only the check box is visible on the sheet.
Ribbon KeyTips
Ribbon KeyTips are too numerous to include in the keyboard simulator, and are worthy of their own training tool. The KeyTips sheet does not have a graphic model, but otherwise has similar structure to the KeyBoard sheet. It starts with a table and ends with a pivot controlled by several slicers. Without graphic, the pivot itself is the output, and only the table is hidden.
In place of a graphic ribbon, the main slicer at the top of the sheet is horizontal, simulating the main tabs. It is sized to avoid tab name truncation and to avoid a scroll bar, and the setting “Show items with no data last” is cleared so the buttons remain in constant position. Each tab is prefixed for sorting, and its KeyTip is enclosed in square brackets, as in “2 [H]ome”.
The second slicer contains the tab sections. These are also prefixed for sorting, but have no KeyTip. The setting “Show items with no data last” is set so that as a tab button is selected, the sections for that tab are shown appropriately. The most sections on a tab is eleven on the Insert tab, so the slicer is set with eleven columns.
The table lists every tab, section and function in the ribbon with the KeyTip sequence. The KeyTips are then separated out into four columns of single characters, KT1 to KT4, which appear in sequenced slicers, like a KeyTip spelling primer. Finally there are two columns for alternate methods, one for a second KeyTip and one for a keyboard shortcut. For example, the ribbon sequence for outside borders is Home, Borders, Outside; the KeyTip is thus Alt-H,B,S, and the shortcut is Ctrl+Shift+7.
The four vertical slicers are narrow, with no room for the heading. To label them 1st through 4th, I use small shapes overlaying the headers but not covering the Clear Filter buttons. Then, to lock the labels in place I protect the sheet, with Use PivotTable checked, allowing the slicers to work.
I did not include every function in the File tab, or contextual tabs, but did include some specific extended KeyTip functions that also have hotkey shortcuts, to allow comparison of different methods.
By keeping the file handy you can study it a few minutes a day, and use it to look up shortcuts anytime you need. With each shortcut you commit to memory you’ll become more productive.
Comments