Am a lover of keyboard shortcuts. I want everything i do in keyboard shortcuts so that i don't take my hands off the keyboard. This week , i was trying to set a keyboard shortcut to just copy / paste the formatting (format painter) in Excel 2011 on my Mac. And my journey began there..
It is easy in Word 2011. The customized keyboard shortcut provides the commands copyFormat/pasteFormat to assign a shortcut. If you are not familiar , you can assign shortcuts to most of the commands you use in Word/Excel through the Tools->Customize Keyboard. I tried to search the same copyFormat/pasteFormat commands in Excel but could not find it. But digging a little further showed that this command is there in Excel 2011 Mac in a different name "Formatting". So , here is what i did to set the shortcut.
Go to Tools-> Customize Keyboard. Select "Edit" from categories. On the right hand side select "Formatting". Go to "Press new shortcut key" and type in a new shortcut. I used Command + Shift + v. And , click on "Add" . Close the window. All set. Now i can just use the normal copy ( Command + c) the cells from which i want to copy the
formatting. Then go the cell where i want to paste the format and hit
the keyboard shortcut (Command + Shift + v). BAM ! the format is copied.
End of Solution !
You can also add this command in your toolbar/menu. Just right click on the free space in worksheet toolbar ( space beside the Help ? symbol on top of any workbook or space before the quick search). select the "Customize toolbars and menus". Go to "Commands" tab and select the formatting from "Edit" category. Click and drag the "Formatting" to the Worksheet toolbar or the Standard toolbar i.e your menu .
In between , i also tried some other alternatives from this post. It provided a simple Macro script to paste only the format. It was simple and did the job.
ok, now i have to get this macro for all my excel sheets. So , i added this macro in an new excel workbook and saved it as Excel Add-in (.xlam). Close the workbook and open a new one. Then go to Tools -> Add-ins and use select to find the .xlam file you stored the macro. Check it and close it. Thats it. The macro is available all across the excel sheets.
It is a good learning.
