Tongfamily Website

One of the maddening things about the Mac Office 2004/2008 line is that it is not keystroke compatible with Windows Office. I don’t know why they don’t have a compatibility mode so that for instance Ctrl-+ works (this is insert) or Ctrl—(this is delete) or Shift-Space which is select row, but they don’t. The same with Mac PowerPoint. Ctrl-Right Arrow doesn’t promote. Well, time to go build it. Allison hates it and asked for it, so here is a feature I’ll sell to Microsoft for $20M. Here is the poor man’s way to get keystroke compatibility. (It is way easier than the last time 20 years ago when the analogous feature, 1-2-3 keystroke compatibility was such a big deal for PC Excel. First memo I ever sent up the chain and the memo that should have gotten me fired if Microsoft had been such an extraordinary company back then).

Here is how to do it:

Excel Keystroke Shortcuts

First you need a list of the keystrokes. Amazingly, Excel has 214 keystroke shortcuts available. It is just for basic editing! So that’s the list that you need. There are some that they are trying to get rid of but everyone needs them. BTW, I’m shooting for Excel 2003 and below compatibility. Excel 2007 of course changed lots of things which I don’t need to fix. Or argue about for that matter.

He has his favorites which are also mine: * Ctrl + Page Up and Ctrl + Page Down – previous/next worksheet * Shift + Space – select row * Ctrl + Space – select column * Ctrl + 0 – hide column * Shift + Ctrl + 0 – show column * Ctrl + 9 – hide row * Shift + Ctrl + 9 – show row * Ctrl + ‘ – Copy above cell and edit * Alt + Down – Open drop-down (auto-complete, filter, pick from list, or validation) * Ctrl + / – Select Array * F9 – Calculate * Shift + F9 – Calculate sheet (in the active workbook)

Then, there are a whole group that relate to selection in Excel – selecting ranges, navigating selection, etc. By way of background, Excel has a few selection “modes” which you can turn on and off. “Extend selection mode”, when turned on, simply expands selection from the active cell when you move around using the mouse or keyboard. “Add selection mode” adds to existing selection when you select cells (using either the mouse or toggling on “Extent selection mode”). These are handy for keyboard-intensive users. Here are some examples.

* F8 – Extend selection mode * Shift F8 – Add selection mode * Shift + Backspace – Collapse selection to the active cell * Ctrl+ Backspace – Show active cell (particularly handy if you have a large range selected and the active cell is off the screen) * Ctrl + Alt + Left Arrow – Move active cell to previous non-adjacent area within selection (when you have discontinuous ranges selected) * Ctrl + Alt + Right Arrow – Move active cell to next non-adjacent area within selection (again, when you have discontinuous ranges selected) * Ctrl + . – rotates the active cell through the corners of a selection * Enter – move active cell down in a selection * Shift + Enter – move active cell up in a selection

On to the more esoteric items. Here is one that is less-frequently-used since we added formula tooltips (the tooltips that show the arguments of a formula as you type), but it can still come in handy.

* Shift + Ctrl + A – Insert arguments in formula

And here are a couple of my true favourites which allow you to quickly navigate the “calculation chain” in Excel.

* Ctrl + [ – Select direct precedent cells * Shift + Ctrl + [ – Select all precedent cells * Ctrl + ] – Select directly dependent cells * Shift + Ctrl + ] – Select all dependent cells

There are other lists too like Official list and David Ritchie’s

What are most used

Changing keyboard shortcuts

So how do you change keyboard shortcuts. Well, macosx.com has some tips and also some suggestions:

Hidden Key-stroke short-cuts in MS Excel – macosx.com

Assign a keyboard shortcut

1. On the Tools menu, click Customize.
2. At the bottom of the Customize dialog box, click Keyboard.
3. In the Customize Keyboard dialog box, select a category from the Categories list to see the commands and other items associated with that category.
4. Click the command you want in the list to the right.

Note** If there is currently a keyboard shortcut associated with the command, it is displayed in the Current keys box.
5. Click inside the Press new shortcut key box.
6. Press the key combination you want to use for the command.
7. Click Add.
8. After you have finished assigning all the shortcuts you want, click OK.

Note** If you change your mind now or later, you can reset the shortcuts by clicking Reset All.”

Also, Unsanity has an app to create keyboard shortcuts that you can check out – I haven’t used it yet. I often use QuicKeys – for example, I was so sick of paste special not defualting to unformatted text (what I always seem to want to do) that I created a QuicKeys shortcut for this that I use in Word, Excel and Mail.”

Excel Keystrokes

I use these every time I’m in Excel.

* ctrl-Space – select column * shift-Space – select row * ctrl-Arrow Key – depending on which arrow key you hit, it will move the cursor along a series of cells* * ctrl-Page Up, ctrl-Page Down – cycles through worksheets * ctrl-Home – selects A1 on the current worksheet

Mac Excel shortcuts

BTW, it turns out that Mac Excel has many identical ones, but they use the Command key instead of the CTRL key. How dumb since the CTRL key isn’t used at all by Mac Excel. Here are some:

XL2001: Shortcut Keys Available in Excel 2001 for Mac (Part 1)

Start a formula Cell, Formula bar = (Equal sign)
Move one character up, down, left, or right Cells, Text boxes ARROW KEYS
Move between options in the active pop-up menu or between some options in a group of options Dialog boxes ARROW KEYS
Move one cell in a given direction Sheets ARROW KEYS
Move around the page when zoomed in (in Print Preview) Print Preview ARROW KEYS
Calculate all sheets in all open workbooks Workbooks COMMAND+= (Equal sign)
Resize cell and show the formula instead of the resulting value. If the cell does not contain a formula, show the value. Options, Display Formula COMMAND+~ (Tilde)
Display the Cells command (Format menu) Workbook COMMAND+1
Select the entire worksheet Worksheet COMMAND+A
Apply or remove bold formatting Cells, Text Boxes, Chart objects, Chart items COMMAND+B
Copy the selection Cell, Range, Object COMMAND+C
Move the selected field into the Column area PivotTable Wizard – Layout dialog box COMMAND+C
Move the selected field into the Data area PivotTable Wizard – Layout dialog box COMMAND+D
Align center Cells, Text boxes COMMAND+E
Display the Find command (Edit menu) Excel COMMAND+F
Switch to the next workbook window Workbook window COMMAND+F6
Display the Replace command (Edit menu) Excel COMMAND+H
Apply or remove italic formatting Cells, Text Boxes, Chart objects, Chart items COMMAND+I
Insert a hyperlink Cell COMMAND+K
Aligns the selection to the left Cells, Text boxes COMMAND+L
Display the PivotTable Field dialog box PivotTable Wizard – Layout dialog box COMMAND+L
Move one word to the left Text box COMMAND+LEFT ARROW
Indent Cell, Range COMMAND+M
Create a new workbook Excel COMMAND+N
Open an existing file Excel COMMAND+O
Remove all borders Cell, Range COMMAND+OPTION+- (Hyphen)
Apply the outline border Cell, Range COMMAND+OPTION+0 (Zero)
Apply/remove the bottom outline border Cell, Range COMMAND+OPTION+DOWN ARROW
Apply/remove the left outline border Cell, Range COMMAND+OPTION+LEFT ARROW
Apply/remove the right outline border Cell, Range COMMAND+OPTION+RIGHT ARROW
Apply/remove the top outline border Cell, Range COMMAND+OPTION+UP ARROW
Display the Print command (File menu) Workbook, Help window COMMAND+P
Move the selected field into the Page area PivotTable Wizard – Layout dialog box COMMAND+P
Quit Excel COMMAND+Q
Aligns the selection to the right Cells, Text boxes COMMAND+R
Move the selected field into the Row area PivotTable Wizard – Layout dialog box COMMAND+R
Move one word to the right Text box COMMAND+RIGHT ARROW
Save Workbook COMMAND+S
Decrease font size Workbook COMMAND+SHIFT+,
Increase font size Cells, Text boxes COMMAND+SHIFT+.
Apply or remove strikethrough formatting Cells, Text boxes COMMAND+SHIFT+- (Hyphen)
Add or remove outline on text Cell, Range COMMAND+SHIFT+D
Switch to the previous workbook window Workbook window COMMAND+SHIFT+F6
Display the Style command (Format menu) Workbook COMMAND+SHIFT+L
Select or unselect one word to the left Cells, Text boxes COMMAND+SHIFT+LEFT ARROW
Unindent (remove indent) Cells, Text boxes COMMAND+SHIFT+M
Select all cells that contain comments Worksheet COMMAND+SHIFT+O
Select or unselect one word to the right Cells, Text boxes COMMAND+SHIFT+RIGHT ARROW
Insert the AutoSum formula Cell, Range COMMAND+SHIFT+T
Display the Paste Special dialog box after Copy command Cell, Range COMMAND+SHIFT+V
Add or remove shadow on text Cell, Range COMMAND+SHIFT+W
Select only visible cells in the current selection Range COMMAND+SHIFT+Z
Toggle cell reference relative or absolute Range COMMAND+T
Select a Help topic from the topics that are displayed by the Office Assistant Assistant Help balloon COMMAND+Topic number (where 1 is the first topic, 2 is the second, and so on)
Apply or remove underline formatting Cells, Text boxes COMMAND+U
Paste the selection Excel COMMAND+V
Close the active workbook window Windows and dialog boxes COMMAND+W
Cut the selection Cell, Range, Object COMMAND+X
Repeat the last action Workbook COMMAND+Y
Undo the last selection Excel COMMAND+Z

What is not right

In Mac Excel 2008, some keys are there and others are missing. Here’s a list of the common ones I use and their status:

Shift-Space. Select Row. It’s there
Ctrl-Space. Select Column. It’s there
Ctrl-Shift-=. Insert. Not there. Can add with the Tools/Customize Keyboard.