Keyboard shortcut in Excel for Mac 2016 to paste and match destination formatting

Question

Is there a keyboard shortcut shortcut to paste and match destination formatting in Excel for Mac 2016? If there isn't, can I create one?

Solution 1

There isn't any direct shortcut for what you are asking. However, you could try one of the following methods.

1. Use Paste Special dialog

When pasting text from some other application, open Paste Special dialog with ctrl+⌘ cmd+v, choose "Unicode text" with ↓ down arrow (if not already selected) and then hit ↩︎ return. (If you have set Full Keyboard Access in System Preferences on, you have to hit ⇥ tab first in Paste Special dialog.)

When pasting formulas or values inside Excel, open Paste Special with the same shortcut ctrl+⌘ cmd+v and then hit either f to select Formulas or v to select Values depending on what you want to paste. Then hit ↩︎ return.

2. Create a macro

Record a macro in Excel with commands explained above. You can assign a custom shortcut for your macro and save it to your Personal Macro Workbook so that it's available whenever you use Excel. This is probably the easiest method.

Another possibility is to write a Visual Basic script.

3. Edit cell and paste

Start editing the cell with F2 (or fn+F2 if you haven't changed the behavior of the function keys) and then paste normally with ⌘ cmd+v.

Note that when pasting a formula, only the value will be pasted.

Also note that if there's line breaks in the pasted text, all the lines will be pasted into the same cell instead of pasting them to different rows.

In addition, this method doesn't replace the existing cell value. After hitting F2, the cursor will be placed after the existing content.

4. Create an Automator service

Create a service with Automator that receives no input in Excel and has one Run AppleScript action with the following code:

on run {input, parameters}
    tell application "Microsoft Excel"
        paste special on worksheet active sheet format "Unicode text"
    end tell

    return input
end run

Note that this only works when pasting text from some other application. It doesn't work with Excel formulas.

Also note that if the language of your user interface is not English, you have to replace "Unicode text" with what is displayed in Paste Special dialog.

If you want to paste formulas inside Excel with a script, instead of paste special... line above, use:

paste special active cell what paste formulas

And the values of formulas inside Excel can be pasted with the following code:

paste special active cell what paste values

Of course, you can create a new workflow for each of the three cases.

After creating the service, you can assign a shortcut for the service in System Preferences.

You should be able to check the contents of the clipboard in code and then decide which command to run, but I couldn't get the get clipboard formats command of Excel to work as expected so I'm not sure how to implement that.

Solution 2

I finally got a macro to work on my mac (to paste into the currently selected cell and match destination formatting)!

First go copy something into the clipboard to be able to paste... and then
Record the macro:

1. Start recording a macro with

   Tools -> Macro -> Record New Macro...

2. Give it a name, store it in Personal Macro Workbook, give it a keyboard shortcut, and description.

3. Click "Ok"

   Do the keystrokes for Paste Special, Unicode Text:

   Ctrl+Cmd+v and ↓+↓+↓+Enter

   Stop recording the macro:

   Tools -> Macro -> Stop Recording

Now edit the macro:

1. With

   Tools -> Macro -> Macros

2. Highlight the macro you just created
3. Click on "Edit" (the VBO window will open)

4. Replace this code

   ThemeEffectScheme.PasteSpecial Format:="Unicode Text", Link:=False, _
   DisplayAsIcon:=False
   

   with this instead:

   ActiveCell.PasteSpecial xlPasteValues
   

5. Close the VBO window

6. Try it out!