Powerpoint shortcuts

Here is the official list to learn. The main difference is Option is used instead of ctrl which is a little wierd since there is a CTRL key on modern mac systems:

Help and How-To for Microsoft for Mac Office Products | Mactopia

PowerPoint keyboard shortcuts

Here are the keys ones I useHelp and How-To for Microsoft for Mac Office Products | Mactopia

One character to the left

LEFT ARROW

One character to the right

RIGHT ARROW

One line up

UP ARROW

One line down

DOWN ARROW

To the beginning of a word or one word to the left

OPTION+LEFT ARROW

One word to the right

OPTION+RIGHT ARROW

To the end of a line

⌘+RIGHT ARROW or END

To the beginning of a line

⌘+LEFT ARROW or HOME

To the beginning of a paragraph or up one paragraph

OPTION+UP ARROW

Down one paragraph

OPTION+DOWN ARROW

To the end of a text box

OPTION+END

To the beginning of a text box

OPTION+HOME

MacBook Air reinstallation and hopefully Time Capsule transfer

OK, so I’ve completely wiped my MacBook Air, time to rebuild it. Here is a list of things that have to happen. Sure sounds Windows like doesn’t it 🙂

# Remote install Mac OS X. Sigh. This is going to take 4 hours over the air apparently
# Try the “Time Capsule recovery”:http://www.askdavetaylor.com/how_to_restore_imac_macbook_from_time_capsule_machine_backup.html which comes during the standard installation, it asks if you want to restore from Time Capsule
# Run Settings/Software Update to get me to 10.5.5.
# Install Microsoft Office 2004
# Install PDFPen
# Install Quickeys (or a good time to get F2 and F4 working with other stuff)
# Copy over my Music
# Make Mac Mail work with my email accounts
# Make Entourage work with our Exchange server
#

PC Office Compatibility mode for Mac Office

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:

h2. Excel Keystroke Shortcuts

First you need a list of the keystrokes. Amazingly, “Excel”:http://blogs.msdn.com/excel/archive/2006/02/23/538311.aspx 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”:http://office.microsoft.com/en-us/assistance/HP052037811033.aspx and “David Ritchie’s”:http://www.mvps.org/dmcritchie/excel/shortx2k.htm

h2. What are most used

h2. Changing keyboard shortcuts

So how do you change keyboard shortcuts. Well, “macosx.com”:http://macosx.com/forums/mac-os-x-system-mac-software/39064-hidden-key-stroke-short-cuts-ms-excel.html 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

h2. 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

h3. 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.

iCal to Exchange before Snow Leopard

So “Vyshemirsky”:http://vyshemirsky.blogspot.com/2007/10/how-to-synchronize-ical-with-exchange.html found another solution to this problem called at http://ww.snerdware.com/groupcal and they have another than does the same for contacts to the Address Book. Although “Judi”:http://judismith.wordpress.com/2007/09/13/still-no-ical-exchange-sync/ found it buggy and it deleted appointments in Exchange and iCal (so backup before trying any of this!). And “some”:http://forums.macrumors.com/showthread.php?t=471498 think they are out of business.

There are real issues like “Justin”:http://justinhartman.com/2008/03/31/apple-ical-microsoft-exchange-fix-for-leopard/ point out and “Fragens”:http://thefragens.com/blog/2008/08/ical-exchange-time-zone-fix-chapter-2/ has a fix for. Outlook 2003 and earlier uses non-standard time formats like _(GMT+02.00) Harare/Pretoria_ whereas Apple uses _Africa/Johannesburg_ and so when iCal gets a calendar request from Exchange, it simply adds two hours to the event start time becaue it can’t deal with GMT+2. He wrote a script you run on your computer that runs in Mac Mail, it intercepts the message and then fixes the dates. Clever guy!

Duplicate Calendar Events

I’ve got literally thousands of these in Exchange that somehow have grown and grown. After an hour of deleting manually, figured there has to be a better way. Here is how to delete duplicates:

Duplicates: Removing or Creating

Remove Duplicate Calendar Events Author: Jolly Roger

This script removes duplicate calendar events from the calendar in the current identity. The script determines whether an event is a duplicate of another event by examining these event properties:

To use it, you basically copy it to ~/Documents/Microsoft User Data/Entourage Script Menu and then run it from Entourage. This thing works but is extremely slow, finds one duplicate every minute or so with 5000 calendar entries. Also you have to approve each removal.

There is an iCal deduplicator that works way faster I blogged previously and on the Windows side, I tried “Mapilabs”:http://www.mapilab.com/outlook/remove_duplicates/ application that costs $15 with a free 14 day trial that works much faster.

Excel 2008 bugs make it useless for loans and private equity

Wow, I can’t believe Microsoft would ship a product like this. Beware, Excel 2008 won’t recalc XIRR correctly and in fact any sheet that has an intentional reference where loan amounts have fees that are dependent. This has worked in versions of Excel for the last 20 years. So sad, it means that Excel 2008 is useless so beware. Can’t see any information on when/if this is going to get fixed. In the mean time, if have sheets that do loan calculations of any sort, beware, you need to use Mac Excel 2004 or any version of PC Excel. Very sad statement about software quality IMHO.

Analysis Toolpak – Excel 2008

Well, in fact the problem seems to be in Excel 2008’s inability to calculate
formulae that intentionally include circular references (such as to calculate
the interest on a loan where the loan amount is determined by reference to
the total cash deficit, including the interest). These formulae return the
#VALUE! error in Excel 2008 whereas they calculate as normal in Excel 2007.
Oh dear,….

I hate Internet Explorer specific websites

Why do people create these sites? It is so easy to be compatible with generic browsers. Here are a list of offenders that are truly depressing to me:

* “Applix”:http://applix.com. It is probably Microsoft’s issue, but why is the user admin console of their hosted Exchange only possible in IE. It certainly isn’t because of the rich feature set. It is trivial to create AJAX sites that work really well on most browsers.
* “Bonzi”:http://bonzicentral.com. They have a really nice management system for soccer clubs, so why does it only run on IE? Sigh.

Reset iCal and Entourage

I’ve had terrible problems with both iCal and Entourage. With iCal, I first tried to use Google Calendar and then started deleting excessive calendars. Then, when I rebooted, I discovered that I couldn’t see any calendars and also that when I tried to add another calendar with New calendar, iCal just hung.

First thing to do when this happens is to start the Console (go to spotlight at upper right and type _console_ and in the search box, type “ical”. I saw right away there was an “Coredata uncaught exception” so something was wrong. A little research showed Coredata are the internal Apple library for managing databases, so it sure feels like a database corruption. First thing to do is to try to reset iCal

Apple – Support – Discussions – How do I completely reset iCal? …

How do I completely reset iCal? I want a really clean startup situation as if iCal has never run on this account. I tried:

rm -rf ~/Library/{Caches,Caches/Metadata,Preferences,Application\ Support}/*iCal*

but when I start iCal after that I get still a calendar mentioned (which I created a while back but is empty) besides Home and Work. I tried removing as above and logging out and in but that also did not work

Unfortunately, this is not quite correct. There is another database called ~/Library/Calendars which is your list of all calendar data. This what was corrupt which is why there was no left pane. There is a problem. Delete these many files and all is really reset.

Now Entourage somehow decided to take one recurring Birthday and make it a daily event spanning 46 years and then repeat that every year. Didn’t appear in Exchange, so this was something in the Entourage not liking what it saw in Exchange. Solution is to complete wide Entourage. Even a database rebuild (in Spotlight, search for Microsoft Database Utility) didn’t help. So you have to go to Account Settings and delete the offending and reinstall. Also delete in ~/Library/Microsoft all the Entourage databases.